Language elements

• 1: Keywords
• 2: Identifiers
• 3: Literals
• 3.1: Number-type literals
• 3.2: String literals
• 3.2.1: Character string literals
• 3.2.2: Dollar-quoted string literals
• 3.2.3: Unicode string literals
• 3.2.4: VARBINARY string literals
• 3.2.5: Extended string literals
• 3.3: Date/time literals
• 3.3.1: Time zone values
• 3.3.2: Day of the week names
• 3.3.3: Month names
• 3.3.4: Interval literal
• 3.3.4.1: Interval subtype units
• 3.3.4.2: Interval qualifier
• 4: Operators
• 4.1: Bitwise operators
• 4.2: Boolean operators
• 4.3: Comparison operators
• 4.4: Data type coercion operators (CAST)
• 4.4.1: Cast failures
• 4.5: Date/time operators
• 4.6: Mathematical operators
• 4.7: NULL operators
• 4.8: String concatenation operators
• 5: Expressions
• 5.1: Aggregate expressions
• 5.2: CASE expressions
• 5.3: Column references
• 5.4: Comments
• 5.5: Date/time expressions
• 5.6: NULL value
• 6: Predicates
• 6.1: BETWEEN predicate
• 6.2: Boolean predicate
• 6.3: Column value predicate
• 6.4: IN predicate
• 6.5: INTERPOLATE
• 6.6: Join predicate
• 6.7: LIKE predicate
• 6.8: NULL predicate
• 7: Hints
• 7.1: :c
• 7.2: :v
• 7.3: ALLNODES
• 7.4: DEPOT_FETCH
• 7.5: DISTRIB
• 7.6: EARLY_MATERIALIZATION
• 7.7: ECSMODE
• 7.8: ENABLE_WITH_CLAUSE_MATERIALIZATION
• 7.9: GBYTYPE
• 7.10: JFMT
• 7.11: JTYPE
• 7.12: LABEL
• 7.13: PROJS
• 7.14: SKIP_PROJS
• 7.15: SKIP_STATISTICS
• 7.16: SYNTACTIC_JOIN
• 7.17: UTYPE
• 7.18: VERBATIM

1 - Keywords

Keywords are words that have a specific meaning in the SQL language. Every SQL statement contains one or more keywords. Although SQL is not case-sensitive with respect to keywords, they are generally shown in uppercase letters throughout this documentation for readability purposes.

Reserved words and keywords

Many keywords are also reserved words.

Vertica recommends that you not use reserved words as names for objects, or as identifiers. Including reserved words can make your SQL statements confusing. Reserved words that are used as names for objects or identifiers must be enclosed in double-quotes.

Non-reserved keywords

Non-reserved keywords have a special meaning in some contexts, but can be used as identifiers in others. You can use non-reserved keywords as aliases—for example, SOURCE:

=> SELECT my_node AS SOURCE FROM nodes;

Viewing the list of reserved and non-reserved keywords

To view the current list of Vertica reserved and non-reserved words, query system table KEYWORDS. Vertica lists keywords alphabetically and identifies them as reserved (R) or non-reserved (N).

For example, the following query gets all reserved keywords that begin with B:

=> SELECT * FROM keywords WHERE reserved = 'R' AND keyword ilike 'B%';
 keyword | reserved
---------+----------
 BETWEEN | R
 BIGINT  | R
 BINARY  | R
 BIT     | R
 BOOLEAN | R
 BOTH    | R
(6 rows)

2 - Identifiers

Identifiers (names) of objects such as schema, table, projection, column names, and so on, can be up to 128 bytes in length.

Unquoted identifiers

Unquoted SQL identifiers must begin with one of the following:

• Non-Unicode letters: A–Z or a-z

• Underscore (_)

Subsequent characters in an identifier can be any combination of the following:

• Non-Unicode letters: A–Z or a-z

• Underscore (_)

• Digits(0–9)

• Unicode letters (letters with diacriticals or not in the Latin alphabet), unsupported for model names

• Dollar sign ($), unsupported for model names

  Caution

  The SQL standard does not support dollar sign in identifiers, so usage can cause application portability problems.

Quoted identifiers

Identifiers enclosed in double quote (") characters can contain any character. If you want to include a double quote, you need a pair of them; for example """". You can use names that would otherwise be invalid—for example, names that include only numeric characters ("123") or contain space characters, punctuation marks, and SQL or Vertica-reserved keywords. For example:

CREATE SEQUENCE "my sequence!";

Double quotes are required for non-alphanumerics and SQL keywords such as "1time", "Next week" and "Select".

Case sensitivity

Identifiers are not case-sensitive. Thus, identifiers "ABC", "ABc", and "aBc" are synonymous, as are ABC, ABc, and aBc.

Non-ASCII characters

Vertica accepts non-ASCII UTF-8 Unicode characters for table names, column names, and other identifiers, extending the cases where upper/lower case distinctions are ignored (case-folded) to all alphabets, including Latin, Cyrillic, and Greek.

For example, the following CREATE TABLE statement uses the ß (German eszett) in the table name:

=> CREATE TABLE straße(x int, y int);
   CREATE TABLE

Identifiers are stored as created

SQL identifiers, such as table and column names, are not converted to lowercase. They are stored as created, and references to them are resolved using case-insensitive compares. For example, the following statement creates table ALLCAPS.

=> CREATE TABLE ALLCAPS(c1 varchar(30));
=> INSERT INTO ALLCAPS values('upper case');

The following statements are variations of the same query:

=> SELECT * FROM ALLCAPS;
=> SELECT * FROM allcaps;
=> SELECT * FROM "allcaps";

The three queries all return the same result:

     c1
------------
 upper case
(1 row)

Note that Vertica returns an error if you try to create table AllCaps:

=> CREATE TABLE AllCaps(c1 varchar(30));
   ROLLBACK:  table "AllCaps" already exists

See QUOTE_IDENT for additional information.

3 - Literals

Literals are numbers or strings used in SQL as constants. Literals are included in the select-list, along with expressions and built-in functions and can also be constants.

Vertica provides support for number-type literals (integers and numerics), string literals, VARBINARY string literals, and date/time literals. The various string literal formats are discussed in this section.

3.1 - Number-type literals

Vertica supports three types of numbers: integers, numerics, and floats.

• Integers are whole numbers less than 2^63 and must be digits.

• Numerics are whole numbers larger than 2^63 or that include a decimal point with a precision and a scale. Numerics can contain exponents. Numbers that begin with 0x are hexadecimal numerics.

Numeric-type values can also be generated using casts from character strings. This is a more general syntax. See the Examples section below, as well as Data type coercion operators (CAST).

Syntax

digits
digits.[digits] | [digits].digits
digits e[+-]digits | [digits].digits e[+-]digits | digits.[digits] e[+-]digits

Parameters

digits

One or more numeric characters, 0 through 9

e

Exponent marker

Notes

• At least one digit must follow the exponent marker (e), if e is present.

• There cannot be any spaces or other characters embedded in the constant.

• Leading plus (+) or minus (–) signs are not considered part of the constant; they are unary operators applied to the constant.

• In most cases a numeric-type constant is automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it as described in Data type coercion operators (CAST).

• Floating point literals are not supported. If you specifically need to specify a float, you can cast as described in Data type coercion operators (CAST).

• Vertica follows the IEEE specification for floating point, including NaN (not a number) and Infinity (Inf).

• A NaN is not greater than and at the same time not less than anything, even itself. In other words, comparisons always return false whenever a NaN is involved.

• Dividing INTEGERS (x / y) yields a NUMERIC result. You can use the // operator to truncate the result to a whole number.

Examples

The following are examples of number-type literals:

42
3.5
4.
.001
5e2
1.925e-3

Scientific notation:

=> SELECT NUMERIC '1e10';
 ?column?
-------------
 10000000000
(1 row)

BINARY scaling:

=> SELECT NUMERIC '1p10';
 ?column?
----------
     1024
(1 row)
=> SELECT FLOAT 'Infinity';
  ?column?
----------
 Infinity
(1 row)

The following examples illustrated using the / and // operators to divide integers:

=> SELECT 40/25;
 ?column?
----------------------
 1.600000000000000000
(1 row)
=> SELECT 40//25;
 ?column?
----------
        1
(1 row)

See also

3.2 - String literals

String literals are string values surrounded by single or double quotes. Double-quoted strings are subject to the backslash, but single-quoted strings do not require a backslash, except for \' and \\.

You can embed single quotes and backslashes into single-quoted strings.

To include other backslash (escape) sequences, such as \t (tab), you must use the double-quoted form.

Precede single-quoted strings with a space between the string and its preceding word, since single quotes are allowed in identifiers.

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Internationalization parameters
• Implement locales for international data sets

3.2.1 - Character string literals

Character string literals are a sequence of characters from a predefined character set, enclosed by single quotes.

Syntax

'character-seq'

Parameters

character-seq

Arbitrary sequence of characters

Embedded single quotes

If a character string literal includes a single quote, it must be doubled. For example:

=> SELECT 'Chester''s gorilla';
  ?column?
-------------------
Chester's gorilla
(1 row)

Standard-conforming strings and escape characters

Vertica uses standard-conforming strings as specified in the SQL standard, so backslashes are treated as string literals and not escape characters.

Examples

=> SELECT 'This is a string';
  ?column?
------------------
 This is a string
(1 row)
=> SELECT 'This \is a string';
   WARNING:  nonstandard use of escape in a string literal at character 8
   HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
     ?column?
------------------
 This is a string
(1 row)
vmartdb=> SELECT E'This \is a string';
     ?column?
------------------
 This is a string
=> SELECT E'This is a \n new line';
       ?column?
----------------------
 This is a
 new line
(1 row)
=> SELECT 'String''s characters';
      ?column?
--------------------
 String's characters
(1 row)

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Implement locales for international data sets

3.2.2 - Dollar-quoted string literals

Dollar-quoted string literals are rarely used, but are provided here for your convenience.

The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax.

Syntax

$$characters$$

Parameters

characters

Arbitrary sequence of characters bounded by paired dollar signs ($$)

Dollar-quoted string content is treated as a literal. Single quote, backslash, and dollar sign characters have no special meaning within a dollar-quoted string.

Notes

A dollar-quoted string that follows a keyword or identifier must be separated from the preceding word by whitespace; otherwise, the dollar-quoting delimiter is taken as part of the preceding identifier.

Examples

=> SELECT $$Fred's\n car$$;
     ?column?
-------------------
 Fred's\n car
(1 row)

=> SELECT 'SELECT 'fact';';
ERROR:  syntax error at or near "';'" at character 21
LINE 1: SELECT 'SELECT 'fact';';

=> SELECT 'SELECT $$fact';$$;
   ?column?
---------------
 SELECT $$fact
(1 row)

=> SELECT 'SELECT ''fact'';';
    ?column?
----------------
 SELECT 'fact';
(1 row)

3.2.3 - Unicode string literals

Syntax

U&'characters' [ UESCAPE '<Unicode escape character>' ]

Parameters

characters

Arbitrary sequence of UTF-8 characters bounded by single quotes (')

Unicode escape character

A single character from the source language character set other than a hexit, plus sign (+), quote ('), double quote (''), or white space

Using standard conforming strings

With StandardConformingStrings enabled, Vertica supports SQL standard Unicode character string literals (the character set is UTF-8 only).

Before you enter a Unicode character string literal, enable standard conforming strings in one of the following ways.

• To enable for all sessions, update the StandardConformingStrings configuration parameter. See Configuration parameters in the Administrator's Guide.

• To treat backslashes as escape characters for the current session, use the SET STANDARD_CONFORMING_STRINGS statement.

See also Extended String Literals.

Examples

To enter a Unicode character in hexadecimal, such as the Russian phrase for "thank you, use the following syntax:

=> SET STANDARD_CONFORMING_STRINGS TO ON;
=> SELECT U&'\0441\043F\0430\0441\0438\0431\043E' as 'thank you';
 thank you
-----------
 спасибо
(1 row)

To enter the German word mude (where u is really u-umlaut) in hexadecimal:

=> SELECT U&'m\00fcde';
?column?
----------
müde
(1 row)
=> SELECT 'ü';
?column?
----------
ü
(1 row)

To enter the LINEAR B IDEOGRAM B240 WHEELED CHARIOT in hexadecimal:

=> SELECT E'\xF0\x90\x83\x8C';
?column?
----------
(wheeled chariot character)
(1 row)

See also

• SET STANDARD_CONFORMING_STRINGS
• SET ESCAPE_STRING_WARNING
• Internationalization parameters
• Implement locales for international data sets

3.2.4 - VARBINARY string literals

VARBINARY string literals allow you to specify hexadecimal or binary digits in a string literal.

Syntax

X''
B''

Parameters

X or x

Specifies hexadecimal digits. The <hexadecimal digits> string must be enclosed in single quotes (').

B or b

Specifies binary digits. The <binary digits> string must be enclosed in single quotes (').

Examples

=> SELECT X'abcd';
 ?column?
----------
 \253\315
(1 row)

=> SELECT B'101100';
 ?column?
----------
 ,
(1 row)

3.2.5 - Extended string literals

Syntax

E'characters'

Parameters

characters

Arbitrary sequence of characters bounded by single quotes (') You can use C-style backslash sequence in extended string literals, which are an extension to the SQL standard. You specify an extended string literal by writing the letter E as a prefix (before the opening single quote); for example:

E'extended character string\n'

Within an extended string, the backslash character (\) starts a C-style backslash sequence, in which the combination of backslash and following character or numbers represent a special byte value, as shown in the following list. Any other character following a backslash is taken literally; for example, to include a backslash character, write two backslashes (\\).

• \\ is a backslash

• \b is a backspace

• \f is a form feed

• \n is a newline

• \r is a carriage return

• \t is a tab

• \x##,where ## is a 1 or 2-digit hexadecimal number; for example \x07 is a tab

• \###, where ### is a 1, 2, or 3-digit octal number representing a byte with the corresponding code.

When an extended string literal is concatenated across lines, write only E before the first opening quote:

=> SELECT E'first part o'
    'f a long line';
         ?column?
---------------------------
 first part of a long line
(1 row)

Two adjacent single quotes are used as one single quote:

=> SELECT 'Aren''t string literals fun?';
          ?column?
-----------------------------
 Aren't string literals fun?
(1 row)

Standard conforming strings and escape characters

When interpreting commands, such as those entered in vsql or in queries passed via JDBC or ODBC, Vertica uses standard conforming strings as specified in the SQL standard. In standard conforming strings, backslashes are treated as string literals (ordinary characters), not escape characters.

• To treat back slashes as escape characters, set configuration parameter StandardConformingStrings to 0.

• To enable standard conforming strings permanently, set the StandardConformingStrings parameter to '1', as described below.

• To enable standard conforming strings per session, use SET STANDARD_CONFORMING_STRING TO ON, which treats backslashes as escape characters for the current session only.

Identifying strings that are not standard conforming

The following procedure can be used to identify nonstandard conforming strings in your application so that you can convert them into standard conforming strings:

1. Be sure the StandardConformingStrings parameter is off, as described in Internationalization parameters.

   => ALTER DATABASE DEFAULT SET StandardConformingStrings = 0;
   

   Note

   Vertica recommends that you migrate your application to use standard conforming strings .

2. If necessary, turn on the EscapeStringWarning parameter.

   => ALTER DATABASE DEFAULT SET EscapeStringWarning = 1;
   

   Vertica now returns a warning each time it encounters an escape string within a string literal. For example, Vertica interprets the \n in the following example as a new line:

   => SELECT 'a\nb';
      WARNING:  nonstandard use of escape in a string literal at character 8
      HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
    ?column?
   ----------
    a
   b
   (1 row)
   

   When StandardConformingStrings is ON, the string is interpreted as four characters: a \ n b.

   Modify each string that Vertica flags by extending it as in the following example:

   E'a\nb'
   

   Or if the string has quoted single quotes, double them; for example, 'one'' double'.

3. Turn on the StandardConformingStrings parameter for all sessions:

   => ALTER DATABASE DEFAULT SET StandardConformingStrings = 1;
   

Doubled single quotes

This section discusses vsql inputs that are not passed on to the server. Vertica recognizes two consecutive single quotes within a string literal as one single quote character. For example, the following inputs, 'You''re here!' ignored the second consecutive quote and returns the following:

=> SELECT 'You''re here!';
   ?column?
--------------
 You're here!at
(1 row)

This is the SQL standard representation and is preferred over the form, 'You\'re here!', because backslashes are not parsed as before. You need to escape the backslash:

=> SELECT (E'You\'re here!');
   ?column?
--------------
 You're here!
(1 row)

This behavior change introduces a potential incompatibility in the use of the vsql meta-command \set, which automatically concatenates its arguments. For example:

\set file  '\''  'pwd'  '/file.txt'  '\''\echo :file

vsql takes the four arguments and outputs the following:

'/home/vertica/file.txt'

Vertica parses the adjacent single quotes as follows:

\set file  '\'''pwd''/file.txt''\''\echo :file
'/home/vertica/file.txt''

Note the extra single quote at the end. This is due to the pair of adjacent single quotes together with the backslash-quoted single quote.

The extra quote can be resolved either as in the first example above, or by combining the literals as follows:

\set file '\''`pwd`'/file.txt'''\echo :file
'/home/vertica/file.txt'

In either case the backslash-quoted single quotes should be changed to doubled single quotes as follows:

\set file '''' `pwd` '/file.txt'''

Additional examples

=> SELECT 'This \is a string';
     ?column?
------------------
 This \is a string
(1 row)

=> SELECT E'This \is a string';
     ?column?
------------------
 This is a string

=> SELECT E'This is a \n new line';
       ?column?
----------------------
 This is a
 new line
(1 row)

=> SELECT 'String''s characters';
      ?column?
--------------------
 String's characters
(1 row)

3.3 - Date/time literals

Date or time literal input must be enclosed in single quotes. Input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others.

Vertica handles date/time input more flexibly than the SQL standard requires. The exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones are described in Date/time expressions.

3.3.1 - Time zone values

Vertica attempts to be compatible with the SQL standard definitions for time zones. However, the SQL standard has an odd mix of date and time types and capabilities. Obvious problems are:

• Although the DATE type does not have an associated time zone, the TIME type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.

• Vertica assumes your local time zone for any data type containing only date or time.

• The default time zone is specified as a constant numeric offset from UTC. It is therefore not possible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

To address these difficulties, OpenText recommends using Date/Time types that contain both date and time when you use time zones. OpenText recommends that you do not use the type TIME WITH TIME ZONE, even though it is supported it for legacy applications and for compliance with the SQL standard.

Time zones and time-zone conventions are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules.

Vertica currently supports daylight-savings rules over the time period 1902 through 2038, corresponding to the full range of conventional UNIX system time. Times outside that range are taken to be in "standard time" for the selected time zone, no matter what part of the year in which they occur.

3.3.2 - Day of the week names

The following tokens are recognized as names of days of the week:

3.3.3 - Month names

The following tokens are recognized as names of months:

3.3.4 - Interval literal

A literal that represents a time span.

Syntax

[ @ ] [-] { quantity subtype-unit }[...] [ AGO ]

Parameters

@

Ignored

- (minus)

Specifies a negative interval value.

quantity

Integer numeric constant

subtype-unit

See Interval subtype units for valid values. Subtype units must be specified for year-month intervals; they are optional for day-time intervals.

AGO

Specifies a negative interval value. AGO and - (minus) are synonymous.

Notes

• The amounts of different units are implicitly added up with appropriate sign accounting.

• The boundaries of an interval constant are:

  • 9223372036854775807 usec to -9223372036854775807 usec

  • 296533 years 3 mons 21 days 04:00:54.775807 to -296533 years -3 mons -21 days -04:00:54.775807

• The range of an interval constant is +/– 263 – 1 microseconds.

• In Vertica, interval fields are additive and accept large floating-point numbers.

Examples

See Specifying interval input.

3.3.4.1 - Interval subtype units

The following tables lists subtype units that you can specify in an interval literal, divided into major categories:

• Year-month subtype units

• Day-time subtype units

Year-month subtype units

Day-time subtype units

Processing m input

Vertica uses context to interpret the input unit m as months or minutes. For example, the following command creates a one-column table with an interval value:

=> CREATE TABLE int_test(i INTERVAL YEAR TO MONTH);

Given the following INSERT statement, Vertica interprets the interval literal 1y 6m as 1 year 6 months:

=> INSERT INTO int_test VALUES('1y 6m');
 OUTPUT
--------
      1
(1 row)
=> COMMIT;
COMMIT
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT * FROM int_test;
        i
-----------------
 1 year 6 months
(1 row)

The following ALTER TABLE statement adds a DAY TO MINUTE interval column to table int_test:

=>  ALTER TABLE int_test ADD COLUMN x INTERVAL DAY TO MINUTE;
ALTER TABLE

The next INSERT statement sets the first and second columns to 3y 20m and 1y 6m, respectively. In this case, Vertica interprets the m input literals in two ways:

• For column i, Vertica interprets the m input as months, and displays 4 years 8 months.

• For column x, Vertica interprets the m input as minutes. Because the interval is defined as DAY TO MINUTE, it converts the inserted input value 1y 6m to 365 days 6 minutes:

=> INSERT INTO int_test VALUES ('3y 20m', '1y 6m');
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM int_test;
        i         |        x
------------------+-----------------
 1 year 6 months  |
 4 years 8 months | 365 days 6 mins
(2 rows)

3.3.4.2 - Interval qualifier

Specifies how to interpret and format an interval literal for output, and optionally sets precision. Interval qualifiers are composed of one or two units:

unit [ TO unit ] [ (p) ]

where:

• unit specifies a day-time or year-month subtype.

• p specifies precision, an integer between 0 and 6.

  Note

  Precision only applies to SECOND units, specifying the number of decimal digits to show after the seconds value decimal point. The default precision for SECOND is 6.

  When SECOND is the second unit of a qualifier—for example, DAY TO SECOND or MINUTE TO SECOND—it has a precision of 2 places before the decimal point.

  For example:

  => SELECT INTERVAL '6 122.538987' MINUTE TO SECOND (5);
    ?column?
  -------------
   08:02.53899
  (1 row)
  

  For details, see Specifying interval precision.

If an interval omits an interval qualifier, the default is DAY TO SECOND(6).

Interval qualifiers are divided into two categories:

• day-time

• year-month

Day-time interval qualifiers

Year-month interval qualifiers

YEAR

Unconstrained

MONTH

Months within year

YEAR TO MONTH

Span of years and months

=> SELECT INTERVALYM '1 2';
 ?column?
----------
 1-2
(1 row)
=> SELECT INTERVAL '1 2' YEAR TO MONTH;
?column?
1-2
(1 row)

Examples

See Controlling interval format.

4 - Operators

Operators are logical, mathematical, and equality symbols used in SQL to evaluate, compare, or calculate values.

4.1 - Bitwise operators

Bitwise operators perform bit manipulations on INTEGER and BINARY/VARBINARY data types:

^† Invalid for BINARY/VARBINARY data types

String argument handling

String arguments must be explicitly cast as BINARY or VARBINARY data types for all bitwise operators. For example:

=> SELECT 'xyz'::VARBINARY & 'zyx'::VARBINARY AS AND;
 AND
-----
 xyx
(1 row)

=> SELECT 'xyz'::VARBINARY | 'zyx'::VARBINARY AS OR;
 OR
-----
 zyz
(1 row)

Bitwise operators treats all string arguments as equal in length. If the arguments have different lengths, the operator function right-pads the smaller string with one or more zero bytes to equal the length of the larger string.

For example, the following statement ANDs unequal strings xyz and zy. Vertica right-pads string zy with one zero byte. The last character in the result is represented accordingly, as \000:

=> SELECT 'xyz'::VARBINARY & 'zy'::VARBINARY AS AND;
  AND
--------
 xy\000
(1 row)

4.2 - Boolean operators

Vertica supports the following Boolean operators:

• AND

• OR

• NOT

Operators AND and OR are commutative, that is, you can switch left and right operands without affecting the result. However, the order of evaluation of sub-expressions is not defined. To force evaluation order, use a CASE construct.

Logic

SQL uses a three-valued Boolean logic where the NULL represents "unknown."

4.3 - Comparison operators

Comparison operators are available for all data types where comparison makes sense. All comparison operators are binary operators that return values of true, false, or NULL.

NULL handling

Comparison operators return NULL if either or both operands are null. One exception applies: <=> returns true if both operands are NULL, and false if one operand is NULL.

Collections

When comparing collections, null collections are ordered last. Otherwise, collections are compared element by element until there is a mismatch, and then they are ordered based on the non-matching elements. If all elements are equal up to the length of the shorter one, then the shorter one is ordered first.

4.4 - Data type coercion operators (CAST)

Data type coercion (casting) passes an expression value to an input conversion routine for a specified data type, resulting in a constant of the indicated type. In Vertica, data type coercion can be invoked by an explicit cast request that uses one of the following constructs:

Syntax

SELECT CAST ( expression AS data-type )
SELECT expression::data-type
SELECT data-type 'string'

Parameters

Truncation

If a binary value is cast (implicitly or explicitly) to a binary type with a smaller length, the value is silently truncated. For example:

=> SELECT 'abcd'::BINARY(2);
 ?column?
----------
 ab
(1 row)

Similarly, if a character value is cast (implicitly or explicitly) to a character value with a smaller length, the value is silently truncated. For example:

=> SELECT 'abcd'::CHAR(3);
 ?column?
----------
 abc
(1 row)

Binary casting and resizing

Vertica supports only casts and resize operations as follows:

• BINARY to and from VARBINARY

• VARBINARY to and from LONG VARBINARY

• BINARY to and from LONG VARBINARY

On binary data that contains a value with fewer bytes than the target column, values are right-extended with the zero byte '\0' to the full width of the column. Trailing zeros on variable-length binary values are not right-extended:

=> SELECT 'ab'::BINARY(4), 'ab'::VARBINARY(4), 'ab'::LONG VARBINARY(4);
  ?column?  | ?column? | ?column?
------------+----------+----------
 ab\000\000 | ab       | ab
(1 row)

Automatic coercion

The explicit type cast can be omitted if there is no ambiguity as to the type the constant must be. For example, when a constant is assigned directly to a column, it is automatically coerced to the column's data type.

Examples

=> SELECT CAST((2 + 2) AS VARCHAR);
 ?column?
----------
 4
(1 row)

=> SELECT (2 + 2)::VARCHAR;
 ?column?
----------
 4
(1 row)

=> SELECT INTEGER '123';
 ?column?
----------
      123
(1 row)

=> SELECT (2 + 2)::LONG VARCHAR
 ?column?
----------
 4
(1 row)

=> SELECT '2.2' + 2;
   ERROR:  invalid input syntax for integer: "2.2"

=> SELECT FLOAT '2.2' + 2;
 ?column?
----------
      4.2
(1 row)

See also

• Data Type Conversions

• Data Type Coercion Chart

• CAST Failures

4.4.1 - Cast failures

When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you try to cast conflicting conversions, such as coercing a VARCHAR expression that contains letters to an integer.

When a cast fails, the result returned depends on the data type.

Enabling strict time casts

You can enable all cast failures to result in an error, including those for date/time data types. Doing so lets you see the reason why some or all of the cast failed. To return an error instead of NULL, set the configuration parameter EnableStrictTimeCasts to 1:

ALTER SESSION SET EnableStrictTimeCasts=1;

By default, EnableStrictTimeCasts is set to 0. Thus, the following attempt to cast a VARCHAR to a TIME data type returns NULL:

==> SELECT current_value from configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
 current_value
---------------
 0
(1 row)

=> CREATE TABLE mytable (a VARCHAR);
CREATE TABLE
=> INSERT INTO mytable VALUES('one');
OUTPUT
--------
1
(1 row)

=> INSERT INTO mytable VALUES('1');
OUTPUT
--------
1
(1 row)

=> COMMIT;
COMMIT
=> SELECT a::time FROM mytable;
 a
---


(2 rows)

If EnableStrictTimeCasts is enabled, the cast failure returns an error:

=> ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
=> SELECT a::time FROM mytable;
ERROR 3679:  Invalid input syntax for time: "1"

Returning all cast failures as NULL

To explicitly cast an expression to a requested data type, use the following construct:

SELECT expression::data-type

Using this command to cast any values to a conflicting data type returns the following error:

=> SELECT 'one'::time;
ERROR 3679:  Invalid input syntax for time: "one"

Vertica also supports the use of the coercion operator ::!, which is useful when you want to return:

• NULL instead of an error for any non-date/time data types

• NULL instead of an error after setting EnableStrictTimeCasts

Returning all cast failures as NULL allows those expressions that succeed during the cast to appear in the result. Those expressions that fail during the cast, however, have a NULL value.

The following example queries mytable using the coercion operator ::!. The query returns NULL where column a contains the string one, and returns 1 where the column contains 1. Query results are identical no matter how EnableStrictTimeCasts is set:

=> SELECT current_value FROM configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
 current_value
---------------
 0
(1 row)

=> SELECT a::!int FROM mytable;
a
---

1
(2 rows)

ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
=> SELECT a::!int FROM mytable;
 a
---

 1
(2 rows)

You can use ::! for casts of arrays and sets. The cast resolves each element individually, producing NULL for elements that cannot be cast.

4.5 - Date/time operators

Syntax

[ + | – | * | / ]

Parameters

+  Addition
–  Subtraction
*  Multiplication
/  Division

Notes

• The operators described below that take TIME or TIMESTAMP inputs actually come in two variants: one that takes TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, and one that takes TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE. For brevity, these variants are not shown separately.

• The + and * operators come in commutative pairs (for example both DATE + INTEGER and INTEGER + DATE); only one of each such pair is shown.

DATE '2001-09-28' + TIME 
'03:00'

INTERVAL '1 DAY' + INTERVAL 
'1 HOUR'

TIMESTAMP '2001-09-28 01:00' 
+ INTERVAL '23 HOURS'

TIME '01:00' + INTERVAL 
'3 HOURS'

DATE '2001-10-01' – DATE 
'2001-09-28'

DATE '2001-09-28' – INTERVAL 
'1 HOUR'

TIME '05:00'  INTERVAL 
'2 HOURS'

TIMESTAMP '2001-09-28 23:00' 
– INTERVAL '23 HOURS'

INTERVAL '1 DAY' – INTERVAL 
'1 HOUR'

TIMESTAMP '2001-09-29 03:00' 
– TIMESTAMP '2001-09-27 12:00'

DOUBLE PRECISION '3.5' 
* INTERVAL '1 HOUR' 

INTERVAL '1 HOUR' / 
DOUBLE PRECISION '1.5'

4.6 - Mathematical operators

Mathematical operators are provided for many data types.

Factorial operator support

Vertica supports use of factorial operators on positive and negative floating point (DOUBLE PRECISION) numbers and integers. For example:

=> SELECT 4.98!;
   ?column?
------------------
 115.978600750905
(1 row)

Factorial is defined in terms of the gamma function, where (-1) = Infinity and the other negative integers are undefined. For example:

(–4)! = NaN
–(4!) = –24

Factorial is defined as follows for all complex numbers z:

z! = gamma(z+1)

For details, see Abramowitz and Stegun: Handbook of Mathematical Functions.

4.7 - NULL operators

To check whether a value is or is not NULL, use the constructs:

[expression IS NULL | expression IS NOT NULL]

Alternatively, use equivalent, but nonstandard, constructs:

[expression ISNULL | expression NOTNULL]

Do not write expression = NULL because NULL represents an unknown value, and two unknown values are not necessarily equal. This behavior conforms to the SQL standard.

4.8 - String concatenation operators

To concatenate two strings on a single line, use the concatenation operator (two consecutive vertical bars).

Syntax

string || string

Parameters

Notes

• || is used to concatenate expressions and constants. The expressions are cast to VARCHAR if possible, otherwise to VARBINARY, and must both be one or the other.

• Two consecutive strings within a single SQL statement on separate lines are automatically concatenated

Examples

The following example is a single string written on two lines:

=> SELECT E'xx'-> '\\';
 ?column?
----------
 xx\
(1 row)

The following examples show two strings concatenated:

=> SELECT E'xx' ||-> '\\';
 ?column?
----------
 xx\\
(1 row)

=> SELECT 'auto' || 'mobile';
 ?column?
----------
 automobile
(1 row)

=> SELECT 'auto'-> 'mobile';
 ?column?
----------
 automobile
(1 row)

=> SELECT 1 || 2;
 ?column?
----------
 12
(1 row)


=> SELECT '1' || '2';
 ?column?
----------
 12
(1 row)
=> SELECT '1'-> '2';
 ?column?
----------
 12
(1 row)

5 - Expressions

SQL expressions are the components of a query that compare a value or values against other values. They can also perform calculations. Expressions found inside any SQL command are usually in the form of a conditional statement.

Operator precedence

The following table shows operator precedence in decreasing (high to low) order.

Expression evaluation rules

The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order. To force evaluation in a specific order, use a CASE construct. For example, this is an untrustworthy way of trying to avoid division by zero in a WHERE clause:

=> SELECT x, y WHERE x <> 0 AND y/x > 1.5;

But this is safe:

=> SELECT x, y
   WHERE
     CASE
       WHEN x <> 0 THEN y/x > 1.5
       ELSE false
     END;

A CASE construct used in this fashion defeats optimization attempts, so use it only when necessary. (In this particular example, it would be best to avoid the issue by writing y > 1.5*x instead.)

Limits to SQL expressions

There are some limits on the number of modifiers and recursions that you can make in an expression. There are two limits that you should be aware of:

• The first limit is based on the stack available to the expression. Vertica requires at least 100kb of free stack. If this limit is exceeded then the error "The query contains an expression that is too complex to analyze" may be thrown. Adding additional physical memory and/or increasing the value of ulimit -s max increase the available stack and prevent the error.

• The second limit is the number of recursions possible in an analytic expression. The limit is 2000. If this limit is exceeded then the error "The query contains an expression that is too complex to analyze" may be thrown. This limit cannot be increased.

5.1 - Aggregate expressions

An aggregate expression applies an aggregate function across the rows or groups of rows selected by a query.

An aggregate expression only can appear in the select list or HAVING clause of a SELECT statement. It is invalid in other clauses such as WHERE, because those clauses are evaluated before the results of aggregates are formed.

Syntax

An aggregate expression has the following format:

<span class="code-variable"><span class="code-variable"><a href="/en/sql-reference/functions/aggregate-functions/#">aggregate-function</a></span></span> ( [ * ] [ ALL | DISTINCT ] expression )

Parameters

Examples

The AVG aggregate function returns the average income from the customer_dimension table:

=> SELECT AVG(annual_income) FROM customer_dimension;
 AVG
--------------
 2104270.6485
(1 row)

The following example shows how to use the COUNT aggregate function with the DISTINCT keyword to return all distinct values of evaluating the expression x+y for all inventory_fact records.

=> SELECT COUNT (DISTINCT date_key + product_key) FROM inventory_fact;
COUNT
-------
21560
(1 row)

5.2 - CASE expressions

The CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to CASE and IF/THEN/ELSE statements in other languages.

Syntax (form 1)

CASE
  WHEN condition THEN result
  [ WHEN condition THEN result ]
  ...
  [ ELSE result ]
END

Parameters

Syntax (form 2)

CASE expression
 WHEN value THEN result
 [ WHEN value THEN result ]
 ...
 [ ELSE result ]
END

Parameters

Notes

The data types of all result expressions must be convertible to a single output type.

Examples

The following examples show two uses of the CASE statement.

=> SELECT * FROM test;
 a
---
 1
 2
 3
=> SELECT a,
     CASE WHEN a=1 THEN 'one'
          WHEN a=2 THEN 'two'
          ELSE 'other'
     END
   FROM test;
 a | case
---+-------
 1 | one
 2 | two
 3 | other
=> SELECT a,
     CASE a WHEN 1 THEN 'one'
            WHEN 2 THEN 'two'
            ELSE 'other'
     END
   FROM test;
 a | case
---+-------
 1 | one
 2 | two
 3 | other

Special example

A CASE expression does not evaluate subexpressions that are not needed to determine the result. You can use this behavior to avoid division-by-zero errors:

=> SELECT x FROM T1 WHERE
      CASE WHEN x <> 0 THEN y/x > 1.5
      ELSE false
    END;

5.3 - Column references

Syntax

[[[database.]schema.]table-name.]column-name

Parameters

Restrictions

A column reference cannot contain any spaces.

5.4 - Comments

A comment is an arbitrary sequence of characters beginning with two consecutive hyphen characters and extending to the end of the line. For example:

   -- This is a standard SQL comment

A comment is removed from the input stream before further syntax analysis and is effectively replaced by white space.

Alternatively, C-style block comments can be used where the comment begins with /* and extends to the matching occurrence of */.

   /* multiline comment
    * with nesting: /* nested block comment */
    */

These block comments nest, as specified in the SQL standard. Unlike C, you can comment out larger blocks of code that might contain existing block comments.

5.5 - Date/time expressions

Vertica uses an internal heuristic parser for all date/time input support. Dates and times are input as strings, and are broken up into distinct fields with a preliminary determination of what kind of information might be in the field. Each field is interpreted and either assigned a numeric value, ignored, or rejected. The parser contains internal lookup tables for all textual fields, including months, days of the week, and time zones.

Vertica parses date/time type inputs as follows:

1. Break the input string into tokens and categorize each token as a string, time, time zone, or number.

2. Numeric token contains:

   • colon (:) — Parse as a time string, include all subsequent digits and colons.

   • dash (-), slash (/), or two or more dots (.) — Parse as a date string which might have a text month.

   • Numeric only — Parse as a single field or an ISO 8601 concatenated date (19990113 for January 13, 1999) or time (141516 for 14:15:16).

3. Token starts with a plus (+) or minus (–): Parse as a time zone or a special field.

4. Token is a text string: match up with possible strings.

   • Perform a binary-search table lookup for the token as either a special string (for example, today), day (for example, Thursday), month (for example, January), or noise word (for example, at, on).

   • Set field values and bit mask for fields. For example, set year, month, day for today, and additionally hour, minute, second for now.

   • If not found, do a similar binary-search table lookup to match the token with a time zone.

   • If still not found, throw an error.

5. Token is a number or number field:

   • If eight or six digits, and if no other date fields were previously read, interpret as a "concatenated date" (19990118 or 990118). The interpretation is YYYYMMDD or YYMMDD.

   • If token is three digits and a year was already read, interpret as day of year.

   • If four or six digits and a year was already read, interpret as a time (HHMM or HHMMSS).

   • If three or more digits and no date fields were found yet, interpret as a year (this forces yy-mm-dd ordering of the remaining date fields).

   • Otherwise the date field ordering is assumed to follow the DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a month or day field is found to be out of range.

6. If BC is specified: negate the year and add one for internal storage. (In the Vertica implementation, 1 BC = year zero.)

7. If BC is not specified, and year field is two digits in length: adjust the year to four digits. If field is less than 70, add 2000, otherwise add 1900.

Month day year ordering

For some formats, ordering of month, day, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields.

Special date/time values

Vertica supports several special date/time values for convenience, as shown below. All of these values need to be written in single quotes when used as constants in SQL statements.

The values INFINITY and -INFINITY are specially represented inside the system and are displayed the same way. The others are simply notational shorthands that are converted to ordinary date/time values when read. (In particular, NOW and related strings are converted to a specific time value as soon as they are read.)

The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type:

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP

• LOCALTIME

• LOCALTIMESTAMP

The latter four accept an optional precision specification. (See Date/time functions.) However, these functions are SQL functions and are not recognized as data input strings.

5.6 - NULL value

NULL is a reserved keyword used to indicate that a data value is unknown. It is the ASCII abbreviation for NULL characters (\0).

Usage in expressions

Vertica does not treat an empty string as a NULL value. An expression must specify NULL to indicate that a column value is unknown.

The following considerations apply to using NULL in expressions:

• NULL is not greater than, less than, equal to, or not equal to any other expression. Use the Boolean predicate to determine whether an expression value is NULL.

• You can write queries with expressions that contain the <=> operator for NULL=NULL joins. See Equi-joins and non equi-joins.

• Vertica accepts NULL characters ('\0') in constant strings and does not remove null characters from VARCHAR fields on input or output.

Projection ordering of NULL data

Vertica sorts NULL values in projection columns as follows:

See also

6 - Predicates

Predicates are truth-tests. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results.

Predicates consist of a set of parameters and arguments. For example, in the following example WHERE clause:

WHERE name = 'Smith';

• name = 'Smith' is the predicate

• 'Smith' is an expression

6.1 - BETWEEN predicate

The special BETWEEN predicate is available as a convenience.

Syntax

WHERE a BETWEEN x AND y

Examples

WHERE a BETWEEN x AND y

is equivalent to:

WHERE a >= x AND a <= y

Similarly:

WHERE a NOT BETWEEN x AND y

is equivalent to:

WHERE a < x OR a > y

You can use the BETWEEN predicate for date ranges:

=> CREATE TABLE t1 (c1 INT, c2 INT, c3 DATE);
=> COPY t1 FROM stdin DELIMITER '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 | 2 | 2014-07-26
>> 2 | 3 | 2014-07-27
>> 3 | 4 | 2014-07-28
>> 4 | 5 | 2014-07-29
>> 5 | 6 | 2014-07-30
>> 6 | 7 | 2014-07-31
>> 7 | 8 | 2014-08-01
>> 8 | 9 | 2014-08-02
>> \.

=> SELECT* FROM t1 WHERE c3 BETWEEN DATE('2014-07-26') AND DATE('2014-07-30');
 c1 | c2 |     c3
----+----+------------
  1 |  2 | 2014-07-26
  2 |  3 | 2014-07-27
  3 |  4 | 2014-07-28
  4 |  5 | 2014-07-29
  5 |  6 | 2014-07-30
(5 rows)

You can also use the NOW and INTERVAL keywords to select from a date range:

=> SELECT * FROM t1 WHERE c3 BETWEEN NOW()-INTERVAL '1 week' AND NOW();
 c1 | c2 |     c3
----+----+------------
  7 |  8 | 2014-08-01
  1 |  2 | 2014-07-26
  2 |  3 | 2014-07-27
  3 |  4 | 2014-07-28
  4 |  5 | 2014-07-29
  5 |  6 | 2014-07-30
  6 |  7 | 2014-07-31
(7 rows)

6.2 - Boolean predicate

Retrieves rows where the value of an expression is true, false, or unknown (null).

Syntax

expression IS [NOT] TRUE
expression IS [NOT] FALSE
expression IS [NOT] UNKNOWN

Notes

• A null input is treated as the value UNKNOWN.

• IS UNKNOWN and IS NOT UNKNOWN are effectively the same as the NULL predicate, except that the input expression does not have to be a single column value. To check a single column value for NULL, use the NULL-predicate.

• Do not confuse the boolean-predicate with Boolean operators or the Boolean data type, which can have only two values: true and false.

6.3 - Column value predicate

Syntax

column-name comparison-op constant-expression

Parameters

Notes

To check a column value for NULL, use the NULL predicate.

Examples

table.column1 = 2
table.column2 = 'Seafood'
table.column3 IS NULL

6.4 - IN predicate

Syntax

(column-list) [ NOT ] IN ( values-list )

Parameters

Examples

The following SELECT statement queries all data in table t11.

=> SELECT * FROM t11 ORDER BY pk;
 pk | col1 | col2 | SKIP_ME_FLAG
----+------+------+--------------
  1 |    2 |    3 | t
  2 |    3 |    4 | t
  3 |    4 |    5 | f
  4 |    5 |    6 | f
  5 |    6 |    7 | t
  6 |      |    8 | f
  7 |    8 |      | t
(7 rows)

The following query specifies an IN predicate, to find all rows in t11 where columns col1 and col2 contain values of (2,3) or (6,7):

=> SELECT * FROM t11 WHERE (col1, col2) IN ((2,3), (6,7)) ORDER BY pk;
 pk | col1 | col2 | SKIP_ME_FLAG
----+------+------+--------------
  1 |    2 |    3 | t
  5 |    6 |    7 | t
(2 rows)

6.5 - INTERPOLATE

Used to join two event series together using some ordered attribute, event series joins let you compare values from two series directly, rather than having to normalize the series to the same measurement interval.

Syntax

expression1 INTERPOLATE PREVIOUS VALUE expression2

Parameters

Description

• An event series join is an extension of a regular outer join. Instead of padding the non-preserved side with null values when there is no match, the event series join pads the non-preserved side with the previous values from the table.

• The difference between expressing a regular outer join and an event series join is the INTERPOLATE predicate, which is used in the ON clause. See the Examples section below Notes and Restrictions. See also Event series joins.

• Data is logically partitioned on the table in which it resides, based on other ON clause equality predicates.

• Interpolated values come from the table that contains the null, not from the other table.

• Vertica does not guarantee that there will be no null values in the output. If there is no previous value for a mismatched row, that row will be padded with nulls.

• Event series join requires that both tables be sorted on columns in equality predicates, in any order, followed by the INTERPOLATED column. If data is already sorted in this order, then an explicit sort is avoided, which can improve query performance. For example, given the following tables:

  ask: exchange, stock, ts, pricebid: exchange,
  stock, ts, price
  

  In the query that follows

  • ask is sorted on exchange, stock (or the reverse), ts

  • bid is sorted on exchange, stock (or the reverse), ts

  SELECT ask.price - bid.price, ask.ts, ask.stock, ask.exchange
  FROM ask FULL OUTER JOIN bid
     ON ask.stock = bid.stock AND ask.exchange =
     bid.exchange AND ask.ts INTERPOLATE PREVIOUS
     VALUE bid.ts;
  

Restrictions

• Only one INTERPOLATE expression is allowed per join.

• INTERPOLATE expressions are used only with ANSI SQL-99 syntax (the ON clause), which is already true for full outer joins.

• INTERPOLATE can be used with equality predicates only.

• The AND operator is supported but not the OR and NOT operators.

• Expressions and implicit or explicit casts are not supported, but subqueries are allowed.

Examples

The examples that follow use this simple schema.

CREATE TABLE t(x TIME);
CREATE TABLE t1(y TIME);
INSERT INTO t VALUES('12:40:23');
INSERT INTO t VALUES('14:40:25');
INSERT INTO t VALUES('14:45:00');
INSERT INTO t VALUES('14:49:55');
INSERT INTO t1 VALUES('12:40:23');
INSERT INTO t1 VALUES('14:00:00');
COMMIT;

Normal full outer join

=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x = t1.y;

Notice the null rows from the non-preserved table:

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 |
 14:45:00 |
 14:49:55 |
          | 14:00:00
(5 rows)

Full outer join with interpolation

=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x INTERPOLATE
PREVIOUS VALUE t1.y;

In this case, the rows with no entry point are padded with values from the previous row.

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 12:40:23 | 14:00:00
 14:40:25 | 14:00:00
 14:45:00 | 14:00:00
 14:49:55 | 14:00:00
(5 rows)

Normal left outer join

=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x = t1.y;

Again, there are nulls in the non-preserved table

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 |
 14:45:00 |
 14:49:55 |
(4 rows)

Left outer join with interpolation

=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x INTERPOLATE
   PREVIOUS VALUE t1.y;

Nulls padded with interpolated values.

    x     |    y
----------+----------
 12:40:23 | 12:40:23
 14:40:25 | 14:00:00
 14:45:00 | 14:00:00
 14:49:55 | 14:00:00
(4 rows)

Inner joins

For inner joins, there is no difference between a regular inner join and an event series inner join. Since null values are eliminated from the result set, there is nothing to interpolate.

A regular inner join returns only the single matching row at 12:40:23:

=> SELECT * FROM t INNER JOIN t1 ON t.x = t1.y;
    x     |    y
----------+----------
 12:40:23 | 12:40:23
(1 row)

An event series inner join finds the same single-matching row at 12:40:23:

=> SELECT * FROM t INNER JOIN t1 ON t.x INTERPOLATE
PREVIOUS VALUE t1.y;
    x     |    y
----------+----------
 12:40:23 | 12:40:23
(1 row)

Semantics

When you write an event series join in place of normal join, values are evaluated as follows (using the schema in the above examples):

• t is the outer, preserved table

• t1 is the inner, non-preserved table

• For each row in outer table t, the ON clause predicates are evaluated for each combination of each row in the inner table t1.

• If the ON clause predicates evaluate to true for any combination of rows, those combination rows are produced at the output.

• If the ON clause is false for all combinations, a single output row is produced with the values of the row from t along with the columns of t1 chosen from the row in t1 with the greatest t1.y value such that t1.y < t.x; If no such row is found, pad with nulls.

In the case of a full outer join, all values from both tables are preserved.

See also

• Event series joins

6.6 - Join predicate

Specifies the columns on which records from two or more tables are joined. You can connect multiple join predicates with logical operators AND, OR, and NOT.

Syntax

ON column-ref = column-ref [ {AND | OR | NOT } column-ref = column-ref ]...

Parameters

6.7 - LIKE predicate

Retrieves rows where the string value of a column matches a specified pattern. The pattern can contain one or more wildcard characters.

Syntax

string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } 'pattern' [ESCAPE 'escape-character' ]

Parameters

Substitute symbols

You can substitute the following symbols for LIKE and its variants:

Pattern matching

LIKE requires that the entire string expression match the pattern. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.

LIKE does not ignore trailing white space characters. If the data values to match end with an indeterminate amount of white space, append the wildcard character % to pattern.

LIKE variants compared

The LIKE predicate is compliant with the SQL standard. Vertica also supports several non-standard variants, notably ILIKE , which is equivalent to LIKE except it performs case-insensitive searches. The following differences pertain to LIKE and its variants:

• LIKE operates on UTF-8 character strings. Exact behavior depends on collation parameters such as strength. In particular, ILIKE works by setting S=2 (ignore case) in the current session locale.

• LIKE and ILIKE are stable for character strings, but immutable for binary strings, while LIKEB and ILIKEB are immutable for both cases.

• LIKEB and ILIKEB predicates do byte-at-a-time ASCII comparisons.

Locale dependencies

In the default locale, LIKE and ILIKE handle UTF-8 character-at-a-time, locale-insensitive comparisons. ILIKE handles language-independent case-folding.

In non-default locales, LIKE and ILIKE perform locale-sensitive string comparisons, including some automatic normalization, using the same algorithm as the "=" operator on VARCHAR types.

ESCAPE expressions evaluate to exactly one octet—or one UTF-8 character for non-default locales.

Examples

The following example illustrates pattern matching in locales.

\locale default=> CREATE TABLE src(c1 VARCHAR(100));
=> INSERT INTO src VALUES (U&'\00DF'); --The sharp s (ß)
=> INSERT INTO src VALUES ('ss');
=> COMMIT;

Querying the src table in the default locale returns both ss and sharp s.

=> SELECT * FROM src;
 c1
----
 ß
 ss
(2 rows)

The following query combines pattern-matching predicates to return the results from column c1:

=> SELECT c1, c1 = 'ss' AS equality, c1 LIKE 'ss'
   AS LIKE, c1 ILIKE 'ss' AS ILIKE FROM src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | f        | f    | f
 ss | t        | t    | t
(2 rows)

The next query specifies unicode format for c1:

=> SELECT c1, c1 = U&'\00DF' AS equality,
   c1 LIKE U&'\00DF' AS LIKE,
   c1 ILIKE U&'\00DF' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | f        | f    | f
(2 rows)

Now change the locale to German with a strength of 1 (ignore case and accents):

\locale LDE_S1
=> SELECT c1, c1 = 'ss' AS equality,
c1 LIKE 'ss' as LIKE, c1 ILIKE 'ss' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | t        | t    | t
(2 rows)

This example illustrates binary data types with pattern-matching predicates:

=> CREATE TABLE t (c BINARY(1));
=> INSERT INTO t values(HEX_TO_BINARY('0x00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFF'));
=> SELECT TO_HEX(c) from t;
 TO_HEX
--------
 00
 ff
(2 rows)
select * from t;
  c
------
 \000
 \377
(2 rows)
=> SELECT c, c = '\000', c LIKE '\000', c ILIKE '\000' from t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | t        | t        | t
 \377 | f        | f        | f
(2 rows)
=> SELECT c, c = '\377', c LIKE '\377', c ILIKE '\377' from t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | f        | f        | f
 \377 | t        | t        | t
(2 rows)

6.8 - NULL predicate

Tests for null values.

Syntax

value_expression IS [ NOT ] NULL

Parameters

Examples

Column name:

=> SELECT date_key FROM date_dimension WHERE date_key IS NOT NULL;
 date_key
----------
        1
      366
     1462
     1097
        2
        3
        6
        7
        8
...

Function:

=> SELECT MAX(household_id) IS NULL FROM customer_dimension;
 ?column?
----------
 f
(1 row)

Literal:

=> SELECT 'a' IS NOT NULL;
 ?column?
----------
 t
(1 row)

7 - Hints

Hints are directives that you embed within a query or directed query. They conform to the following syntax:

/*+hint-name[, hint-name]...*/

Hints are bracketed by comment characters /*+ and */, which can enclose multiple comma-delimited hints. For example:

SELECT /*+syntactic_join,verbatim*/

Restrictions

When embedding hints in a query, be aware of the following restrictions:

• Do not embed spaces in the comment characters /* and */.

• In general, spaces are allowed before and after the plus (+) character and hint-name; however, some third-party tools do not support spaces embedded inside /*+.

Supported hints

Vertica supports the following hints:

General hints

Eon Mode hints

Join hints

Projection hints

Directed query hints

The following hints are only supported by directed queries:

7.1 - :c

In a directed query, marks a query constant that must be included in an input query; otherwise, that input query is disqualified from using the directed query.

Syntax

/*+:c*/

Usage

By default, optimizer-generated directed queries set ignore constant (:v) hints on predicate constants. You can override this behavior by setting the :c hint on input query constants that must not be ignored. For example, the following statement creates a directed query that can be used only for input queries where the join predicate constant is the same as in the original input query—8:

=> CREATE DIRECTED QUERY OPTIMIZER simpleJoin_KeepPredicateConstant SELECT * FROM S JOIN T ON S.a = T.b WHERE S.a = 8 /*+:c*/;
CREATE DIRECTED QUERY
=> ACTIVATE DIRECTED QUERY simpleJoin_KeepPredicateConstant;

See also

Conserving Predicate Constants in Directed Queries

7.2 - :v

In a directed query, marks an input query constant that the optimizer ignores when it considers whether to use the directed query for a given query. Use this hint to create a directed query that can be used for multiple variants of an input query.

Vertica also supports IGNORECONST as an alias of :v . Optimizer-generated directed queries automatically mark predicate constants in input and annotated queries with :v hints.

For details, see Ignoring constants in directed queries.

Syntax

/*+:v(arg)*/
/*+IGNORECONST(arg)*/

arg

Integer argument that is used in the directed query to pair each input query :v hint with one or more annotated query :v hints.

Examples

See Ignoring constants in directed queries.

7.3 - ALLNODES

Qualifies an EXPLAIN statement to request a query plan that assumes all nodes are active. If you omit this hint, the EXPLAIN statement produces a query plan that takes into account any nodes that are currently down.

Syntax

EXPLAIN /*+ALLNODES*/

Examples

In the following example, the ALLNODES hint requests a query plan that assumes all nodes are active.

QUERY PLAN DESCRIPTION:
 ------------------------------

 Opt Vertica Options
 --------------------
 PLAN_ALL_NODES_ACTIVE


 EXPLAIN /*+ALLNODES*/ select * from Emp_Dimension;

 Access Path:
 +-STORAGE ACCESS for Emp_Dimension [Cost: 125, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.Emp_Dimension_b0
 |  Materialize: Emp_Dimension.Employee_key, Emp_Dimension.Employee_gender, Emp_Dimension.Courtesy_title, Emp_Dimension.Employee_first_name, Emp_Dimension.Employee_middle_initial, Emp_Dimension.Employee_last_name, Emp_Dimension.Employee_age, Emp_Dimension.Employee_birthdate, Emp_Dimension.Employee_street, Emp_Dimension.Employee_city, Emp_Dimension.Employee_state, Emp_Dimension.Employee_region, Emp_Dimension.Employee_position
 |  Execute on: All Nodes

7.4 - DEPOT_FETCH

Eon Mode only

Specifies whether a query fetches data to the depot from communal storage when the depot lacks data for this query. This hint overrides configuration parameter DepotOperationsForQuery.

Syntax

SELECT /*+DEPOT_FETCH (option)*/

Arguments

*option*

Specifies behavior when the depot does not contain queried file data, one of the following:

• ALL (default): Fetch file data from communal storage, if necessary displace existing files by evicting them from the depot.

• FETCHES: Fetch file data from communal storage only if space is available; otherwise, read the queried data directly from communal storage.

• NONE: Do not fetch file data to the depot, read the queried data directly from communal storage.

Examples

SELECT /*+DEPOT_FETCH(All)*/ count(*) FROM bar;
SELECT /*+DEPOT_FETCH(FETCHES)*/ count(*) FROM bar;
SELECT /*+DEPOT_FETCH(NONE)*/ count(*) FROM bar;

7.5 - DISTRIB

Specifies to the optimizer how to distribute join key data to implement a join.

Syntax

JOIN /*+DISTRIB(outer-join, inner-join)*/

Arguments

outer-join inner-join

Specifies how to distribute data on the outer and inner joins:

• L (local): Inner and outer join keys are identically segmented on each node, join locally.

• R (resegment): Inner and outer join keys are not identically segmented. Resegment join-key data before implementing the join.

• B (broadcast): Inner and outer join keys are not identically segmented. Broadcast data of this join key to other nodes before implementing the join.

• F (filter): Join table is unsegmented. Filter data as needed by the other join key before implementing the join.

• A (any): Let the optimizer choose the distribution method that it considers to be most cost-effective.

Description

The DISTRIB hint specifies to the optimizer how to distribute join key data in order to implement a join. If a specified distribution is not feasible, the optimizer ignores the hint and throws a warning.

The following requirements apply:

• Queries that include the DISTRIB hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the DISTRIB hint and throws a warning.

• Join syntax must conform with ANSI SQL-92 join conventions.

Examples

In the following query, the join is qualified with a DISTRIB hint of /*+DISTRIB(L,R)*/. This hint tells the optimizer to resegment data of join key stores.store_key before joining it to the sales.store_key data:

SELECT /*+SYNTACTIC_JOIN*/ sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date
FROM (store.storeSales AS sales JOIN /*+DISTRIB(L,R),JTYPE(H)*/ store.store_dimension AS stores ON (sales.store_key = stores.store_key))
WHERE (sales.sale_date = '2014-12-01'::date) ORDER BY sales.store_key, sales.sale_date;

7.6 - EARLY_MATERIALIZATION

Specifies early materialization of a table for the current query. A query can include this hint for any number of tables. Typically, the query optimizer delays materialization until late in the query execution process. This hint overrides any choices that the optimizer otherwise would make.

This hint can be useful in cases where late materialization of join inputs precludes other optimizations—for example, pushing aggregation down the joins, or using live aggregate projections. In these cases, qualifying a join input with EARLY_MATERIALIZATION can enable the optimizations.

Syntax

table-name [ [AS] alias ] /*+EARLY_MATERIALIZATION*/

7.7 - ECSMODE

Eon Mode only

Sets the ECS strategy that the optimizer uses when it divides responsibility for processing shard data among subscriber nodes. This hint is applied only if the subcluster uses elastic crunch scaling (ECS).

Syntax

SELECT /*+ECSMODE(option)*/

Arguments

*option*

Specifies the strategy to use when dividing shard data among its subscribing nodes, one of the following:

• AUTO: The optimizer chooses the strategy to use, useful only if ECS mode is set at the session level (see Setting the ECS Strategy for the Session or Database).

• IO_OPTIMIZED: Use I/O-optimized strategy.

• COMPUTE_OPTIMIZED: Use compute-optimized strategy.

• NONE: Disable use of ECS for this query. Only participating nodes are involved in query execution; collaborating nodes are not.

Example

The following example shows the query plan for a simple single-table query that is forced to use the compute-optimized strategy:

=> EXPLAIN SELECT /*+ECSMode(COMPUTE_OPTIMIZED)*/ employee_last_name,
             employee_first_name,employee_age
             FROM employee_dimension
             ORDER BY employee_age DESC;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 The execution of this query involves non-participating nodes.
 Crunch scaling strategy preserves data segmentation
 ------------------------------
. . .

7.8 - ENABLE_WITH_CLAUSE_MATERIALIZATION

Syntax

WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/

7.9 - GBYTYPE

Specifies which algorithm—GROUPBY HASH or GROUPBY PIPELINED —the Vertica query optimizer should use to implement a GROUP BY clause. If both algorithms are valid for this query, the query optimizer chooses the specified algorithm over the algorithm that the query optimizer might otherwise choose in its query plan.

Syntax

GROUP BY /*+GBYTYPE( HASH | PIPE )*/

Arguments

HASH | PIPE

Specifies the GROUP BY algorithm to use:

• HASH: GROUPBY HASH algorithm

• PIPE: GROUPBY PIPELINED algorithm

  Note

  Vertica uses the GROUPBY PIPELINED algorithm only if the query and one of its projections comply with GROUPBY PIPELINED requirements. Otherwise, Vertica issues a warning and uses GROUPBY HASH.

For more information about both algorithms, see GROUP BY implementation options.

Examples

See Controlling GROUPBY Algorithm Choice.

7.10 - JFMT

Specifies how to size VARCHAR column data when joining tables on those columns, and buffer that data accordingly. The JFMT hint overrides the default behavior that is set by configuration parameter JoinDefaultTupleFormat, which can be set at database and session levels.

For more information, see Joining variable length string data.

Syntax

JOIN /*+JFMT(format-type)*/

Arguments

format-type

Specifies how to format VARCHAR column data when joining tables on those columns, and buffers the data accordingly. Set to one of the following:

• f (fixed): Use join column metadata to size column data to a fixed length, and buffer accordingly.

• v (variable): Use the actual length of join column data, so buffer size varies for each join.

For example:

SELECT /*+SYNTACTIC_JOIN*/ s.store_region, SUM(e.vacation_days) TotalVacationDays
   FROM public.employee_dimension e
   JOIN /*+JFMT(f)*/ store.store_dimension s ON s.store_region=e.employee_region
   GROUP BY s.store_region ORDER BY TotalVacationDays;

Requirements

• Queries that include the JFMT hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the JFMT hint and throws a warning.

• Join syntax must conform with ANSI SQL-92 join conventions.

7.11 - JTYPE

Specifies the join algorithm as hash or merge.

Use the JTYPE hint to specify the algorithm the optimizer uses to join table data. If specified algorithm is not feasible, the optimizer ignores the hint and throws a warning.

Syntax

JOIN /*+JTYPE(join-type)*/

Arguments

join-type

One of the following arguments:

• H: Hash join

• M: Merge join, valid only if both join inputs are already sorted on the join columns, otherwise Vertica ignores it and throws a warning.

  Note

  The optimizer relies upon the query or DDL to verify whether input data is sorted, rather than the actual runtime order of the data.

• FM: Forced merge join. Before performing the merge, the optimizer re-sorts the join inputs. Two restrictions apply:

  • This option is valid only for simple join conditions. For example:
    

    SELECT /*+SYNTACTIC_JOIN*/ * FROM x JOIN /*+JTYPE(FM)*/ y ON x.c1 = y.c1;
    

  • Join columns must be of the same type and precision or scale. One exception applies: string columns can have different lengths

Requirements

• Queries that include the JTYPE hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the JTYPE hint and throws a warning.

• Join syntax must conform with ANSI SQL-92 join conventions.

7.12 - LABEL

Assigns a label to a statement so it can easily be identified to evaluate performance and debug problems.

LABEL hints are valid in the following statements:

• COPY
• DELETE

• EXPORT statements:

  • EXPORT TO DELIMITED
  • EXPORT TO ORC
  • EXPORT TO PARQUET
  • EXPORT TO VERTICA
• INSERT
• MERGE
• SELECT
• UPDATE

• UNION: Valid in the UNION's first SELECT statement. Vertica ignores labels in subsequent SELECT statements.

Syntax

statement-name /*+LABEL (label-string)*/

Arguments

label-string

A string that is up to 128 octets long. If enclosed with single quotes, label-string can contain embedded spaces.

Examples

See Labeling statements.

7.13 - PROJS

Specifies one or more projections to use for a queried table.

Syntax

FROM `*`table-name`*` /*+PROJS( [[`*`database`*`.]`*`schema.`*`]`*`projection`*`[,...] )*/

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

projection

The projection to use. You can specify a list of comma-delimited projections.

Description

The PROJS hint can specify multiple projections; the optimizer determines which ones are valid and uses the one that is most cost-effective for the queried table. If no hinted projection is valid, the query returns a warning and ignores projection hints.

Examples

The employee_dimension table has two projections: segmented superprojection public.employee_dimension, which includes all table columns; and the unsegmented projection public.employee_dimension_rep, which includes a subset of the columns:

=> SELECT export_objects('','employee_dimension');
                                                      export_objects
--------------------------------------------------------------------------------------------------------------------------

CREATE TABLE public.employee_dimension
(
    employee_key int NOT NULL,
    employee_gender varchar(8),
    courtesy_title varchar(8),
    employee_first_name varchar(64),
    employee_middle_initial varchar(8),
    employee_last_name varchar(64),
    employee_age int,
    hire_date date,
    employee_street_address varchar(256),
    employee_city varchar(64),
    employee_state char(2),
    employee_region char(32),
    job_title varchar(64),
    reports_to int,
    salaried_flag int,
    annual_salary int,
    hourly_rate float,
    vacation_days int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (employee_key) DISABLED
);

CREATE PROJECTION public.employee_dimension
...
AS
 SELECT employee_dimension.employee_key,
        employee_dimension.employee_gender,
        employee_dimension.courtesy_title,
        employee_dimension.employee_first_name,
        employee_dimension.employee_middle_initial,
        employee_dimension.employee_last_name,
        employee_dimension.employee_age,
        employee_dimension.hire_date,
        employee_dimension.employee_street_address,
        employee_dimension.employee_city,
        employee_dimension.employee_state,
        employee_dimension.employee_region,
        employee_dimension.job_title,
        employee_dimension.reports_to,
        employee_dimension.salaried_flag,
        employee_dimension.annual_salary,
        employee_dimension.hourly_rate,
        employee_dimension.vacation_days
 FROM public.employee_dimension
 ORDER BY employee_dimension.employee_key
SEGMENTED BY hash(employee_dimension.employee_key) ALL NODES KSAFE 1;

CREATE PROJECTION public.employee_dimension_rep
...
AS
 SELECT employee_dimension.employee_key,
        employee_dimension.employee_gender,
        employee_dimension.employee_first_name,
        employee_dimension.employee_middle_initial,
        employee_dimension.employee_last_name,
        employee_dimension.employee_age,
        employee_dimension.employee_street_address,
        employee_dimension.employee_city,
        employee_dimension.employee_state,
        employee_dimension.employee_region
 FROM public.employee_dimension
 ORDER BY employee_dimension.employee_key
UNSEGMENTED ALL NODES;

SELECT MARK_DESIGN_KSAFE(1);

(1 row)

The following query selects all table columns from employee_dimension and includes the PROJS hint, which specifies both projections. public.employee_dimension_rep does not include all columns in the queried table, so the optimizer cannot use it. The segmented projection includes all table columns so the optimizer uses it, as verified by the following query plan:

=> EXPLAIN SELECT * FROM employee_dimension /*+PROJS('public.employee_dimension_rep', 'public.employee_dimension')*/;

QUERY PLAN DESCRIPTION:
------------------------------
EXPLAIN SELECT * FROM employee_dimension /*+PROJS('public.employee_dimension_rep', 'public.employee_dimension')*/;

 Access Path:
 +-STORAGE ACCESS for employee_dimension [Cost: 177, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.employee_dimension_b0

7.14 - SKIP_PROJS

Specifies which projections to avoid using for a queried table. If SKIP_PROJS excludes all available projections that are valid for the query, the optimizer issues a warning and ignores the projection hints.

Syntax

FROM table-name /*+SKIP_PROJS( [[database.]schema.]projection[,...] )*/

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

*projection*

A projection to skip. You can specify a list of comma-delimited projections.

Examples

In this example, the EXPLAIN output shows that the optimizer uses the projection public.employee_dimension_b0 for a given query:

QUERY PLAN DESCRIPTION:
------------------------------

 EXPLAIN SELECT employee_last_name, employee_first_name, employee_city, job_title FROM employee_dimension;

 Access Path:
 +-STORAGE ACCESS for employee_dimension [Cost: 59, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.employee_dimension_b0
 |  Materialize: employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.employee_city, employee_dimension.job_title
 |  Execute on: All Nodes

You can use the SKIP_PROJS hint to avoid using this projection. If another projection is available that is valid for this query, the optimizer uses it instead:

QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT employee_last_name, employee_first_name, employee_city, job_title FROM employee_dimension /*+SKIP_PROJS('public.employee_dimension')*/;

 Access Path:
 +-STORAGE ACCESS for employee_dimension [Cost: 156, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Projection: public.employee_dimension_super
 |  Materialize: employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.emplo
yee_city, employee_dimension.job_title
 |  Execute on: Query Initiator

7.15 - SKIP_STATISTICS

Directs the optimizer to produce a query plan that incorporates only the minimal statistics that are collected by ANALYZE_ROW_COUNT. The optimizer ignores other statistics that would otherwise be used, that are generated by ANALYZE_STATISTICS and ANALYZE_STATISTICS_PARTITION. This hint is especially useful when used in queries on small tables, where the amount of time required to collect full statistics is often greater than actual execution time.

Syntax

SELECT /*+SKIP_STAT[ISTIC]S*/

EXPLAIN output

EXPLAIN returns the following output for a query that includes SKIP_STATISTICS (using its shortened form SKIP_STATS):

=> EXPLAIN SELECT /*+ SKIP_STATS*/ customer_key, customer_name, customer_gender, customer_city||', '||customer_state, customer_age
    FROM customer_dimension WHERE customer_region = 'East' AND customer_age > 60;

 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT /*+ SKIP_STATS*/ customer_key, customer_name, customer_gender, customer_city||', '||customer_state,
 customer_age FROM customer_dimension WHERE customer_region = 'East' AND customer_age > 60;

 Access Path:
 +-STORAGE ACCESS for customer_dimension [Cost: 2K, Rows: 10K (STATISTICS SKIPPED)] (PATH ID: 1)
 |  Projection: public.customer_dimension_b0
 |  Materialize: public.customer_dimension.customer_age, public.customer_dimension.customer_key, public.customer_dimensi
on.customer_name, public.customer_dimension.customer_gender, public.customer_dimension.customer_city, public.customer_di
mension.customer_state
 |  Filter: (public.customer_dimension.customer_region = 'East')
 |  Filter: (public.customer_dimension.customer_age > 60)
 |  Execute on: All Nodes
...

7.16 - SYNTACTIC_JOIN

Enforces join order and enables other join hints.

Syntax

SELECT /*+SYN[TACTIC]_JOIN*/

Description

In order to achieve optimal performance, the optimizer often overrides a query's specified join order. By including the SYNTACTIC_JOIN hint, you can ensure that the optimizer enforces the query's join order exactly as specified. One requirement applies: the join syntax must conform with ANSI SQL-92 conventions.

The SYNTACTIC_JOIN hint must immediately follow SELECT. If the annotated query includes another hint that must also follow SELECT, such as VERBATIM, combine the two hints together. For example:

SELECT /*+ syntactic_join,verbatim*/

Examples

In the following examples, the optimizer produces different plans for two queries that differ only by including or excluding the SYNTACTIC_JOIN hint.

Excludes SYNTACTIC_JOIN

EXPLAIN SELECT sales.store_key, stores.store_name, products.product_description, sales.sales_quantity, sales.sale_date
FROM (store.store_sales sales JOIN products ON sales.product_key=products.product_key)
JOIN store.store_dimension stores ON sales.store_key=stores.store_key
WHERE sales.sale_date='2014-12-01' order by sales.store_key, sales.sale_date;

 Access Path:
 +-SORT [Cost: 14K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
 |  Order: sales.store_key ASC, sales.sale_date ASC
 |  Execute on: All Nodes
 | +---> JOIN HASH [Cost: 11K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT)
 | |      Join Cond: (sales.product_key = products.product_key)
 | |      Materialize at Input: sales.store_key, sales.product_key, sales.sale_date, sales.sales_quantity
 | |      Execute on: All Nodes
 | | +-- Outer -> JOIN HASH [Cost: 1K, Rows: 100K (NO STATISTICS)] (PATH ID: 3)
 | | |      Join Cond: (sales.store_key = stores.store_key)
 | | |      Execute on: All Nodes
 | | | +-- Outer -> STORAGE ACCESS for sales [Cost: 1K, Rows: 100K (NO STATISTICS)] (PATH ID: 4)
 | | | |      Projection: store.store_sales_b0
 | | | |      Materialize: sales.store_key
 | | | |      Filter: (sales.sale_date = '2014-12-01'::date)
 | | | |      Execute on: All Nodes
 | | | |      Runtime Filter: (SIP1(HashJoin): sales.store_key)
 | | | +-- Inner -> STORAGE ACCESS for stores [Cost: 34, Rows: 250] (PATH ID: 5)
 | | | |      Projection: store.store_dimension_DBD_10_rep_VMartDesign_node0001
 | | | |      Materialize: stores.store_key, stores.store_name
 | | | |      Execute on: All Nodes
 | | +-- Inner -> STORAGE ACCESS for products [Cost: 3K, Rows: 60K (NO STATISTICS)] (PATH ID: 6)
 | | |      Projection: public.products_b0
 | | |      Materialize: products.product_key, products.product_description
 | | |      Execute on: All Nodes

Includes SYNTACTIC_JOIN

EXPLAIN SELECT /*+SYNTACTIC_JOIN*/ sales.store_key, stores.store_name, products.product_description, sales.sales_quantity, sales.sale_date
FROM (store.store_sales sales JOIN products ON sales.product_key=products.product_key)
JOIN store.store_dimension stores ON sales.store_key=stores.store_key
WHERE sales.sale_date='2014-12-01' order by sales.store_key, sales.sale_date;

 Access Path:
 +-SORT [Cost: 11K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
 |  Order: sales.store_key ASC, sales.sale_date ASC
 |  Execute on: All Nodes
 | +---> JOIN HASH [Cost: 8K, Rows: 100K (NO STATISTICS)] (PATH ID: 2)
 | |      Join Cond: (sales.store_key = stores.store_key)
 | |      Execute on: All Nodes
 | | +-- Outer -> JOIN HASH [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 3) Outer (BROADCAST)(LOCAL ROUND ROBIN)
 | | |      Join Cond: (sales.product_key = products.product_key)
 | | |      Execute on: All Nodes
 | | |      Runtime Filter: (SIP1(HashJoin): sales.store_key)
 | | | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 4)
 | | | |      Projection: store.store_sales_b0
 | | | |      Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
 | | | |      Filter: (sales.sale_date = '2014-12-01'::date)
 | | | |      Execute on: All Nodes
 | | | +-- Inner -> STORAGE ACCESS for products [Cost: 3K, Rows: 60K (NO STATISTICS)] (PATH ID: 5)
 | | | |      Projection: public.products_b0
 | | | |      Materialize: products.product_key, products.product_description
 | | | |      Execute on: All Nodes
 | | +-- Inner -> STORAGE ACCESS for stores [Cost: 34, Rows: 250] (PATH ID: 6)
 | | |      Projection: store.store_dimension_DBD_10_rep_VMartDesign_node0001
 | | |      Materialize: stores.store_key, stores.store_name
 | | |      Execute on: All Nodes

7.17 - UTYPE

Specifies how to combine UNION ALL input.

Syntax

UNION ALL /*+UTYPE(union-type)*/

Arguments

union-type

One of the following arguments:

• U: Concatenates UNION ALL input (default).

• M: Merges UNION ALL input in the same sort order as the source query results. This option requires all input from the source queries to use the same sort order; otherwise, Vertica throws a warning and concatenates the UNION ALL input.

  Note

  The optimizer relies upon the query or DDL to verify whether input data is sorted, rather than the actual runtime order of the data.

Requirements

Queries that include the UTYPE hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the UTYPE hint and throws a warning.

7.18 - VERBATIM

Enforces execution of an annotated query exactly as written.

VERBATIM directs the optimizer to create a query plan that incorporates all hints in a annotated query. Furthermore, it directs the optimizer not to apply its own plan development processing on query plan components that pertain to those hints.

Usage of this hint varies between optimizer-generated and custom directed queries, as described below.

Syntax

SELECT /*+ VERBATIM*/

Requirements

The VERBATIM hint must immediately follow SELECT. If the annotated query includes another hint that must also follow SELECT, such as SYNTACTIC_JOIN, combine the two hints together. For example:

SELECT /*+ syntactic_join,verbatim*/

Optimizer-generated directed queries

The optimizer always includes the VERBATIM hint in the annotated queries that it generates for directed queries. For example, given the following CREATE DIRECTED QUERY OPTIMIZER statement:

=> CREATE DIRECTED QUERY OPTIMIZER getStoreSales SELECT sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date FROM store.storesales sales JOIN store.store_dimension stores ON sales.store_key=stores.store_key WHERE sales.sale_date='2014-12-01' /*+IGNORECONST(1)*/ AND stores.store_name='Store1' /*+IGNORECONST(2)*/ ORDER BY sales.store_key, sales.sale_date;
CREATE DIRECTED QUERY

The optimizer generates an annotated query that includes the VERBATIM hint:

=> SELECT query_name, annotated_query FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name = 'getStoreSales';
-[ RECORD 1 ]---+------
query_name      | getStoreSales
annotated_query | SELECT /*+ syntactic_join,verbatim*/ sales.store_key AS store_key, stores.store_name AS store_name, sales.product_description AS product_description, sales.sales_quantity AS sales_quantity, sales.sale_date AS sale_date
FROM (store.storeSales AS sales/*+projs('store.storeSales')*/ JOIN /*+Distrib(L,L),JType(H)*/ store.store_dimension AS stores/*+projs('store.store_dimension_DBD_10_rep_VMartDesign')*/  ON (sales.store_key = stores.store_key))
WHERE (sales.sale_date = '2014-12-01'::date /*+IgnoreConst(1)*/) AND (stores.store_name = 'Store1'::varchar(6) /*+IgnoreConst(2)*/)
ORDER BY 1 ASC, 5 ASC

When the optimizer uses this directed query, it produces a query plan that is equivalent to the query plan that it used when it created the directed query:

=> ACTIVATE DIRECTED QUERY getStoreSales;
ACTIVATE DIRECTED QUERY

=> EXPLAIN SELECT sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date FROM store.storesales sales JOIN store.store_dimension stores ON sales.store_key=stores.store_key WHERE sales.sale_date='2014-12-04' AND stores.store_name='Store14' ORDER BY sales.store_key, sales.sale_date;

 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date FROM store.storesales sales JOIN store.store_dimension stores ON sales.store_key=stores.store_key WHERE sales.sale_date='2014-12-04' AND stores.store_name='Store14' ORDER BY sales.store_key, sales.sale_date;

 The following active directed query(query name: getStoreSales) is being executed:
 SELECT /*+syntactic_join,verbatim*/ sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date
FROM (store.storeSales sales/*+projs('store.storeSales')*/ JOIN /*+Distrib('L', 'L'), JType('H')*/store.store_dimension stores
/*+projs('store.store_dimension_DBD_10_rep_VMartDesign')*/ ON ((sales.store_key = stores.store_key))) WHERE ((sales.sale_date = '2014-12-04'::date)
AND (stores.store_name = 'Store14'::varchar(7))) ORDER BY sales.store_key, sales.sale_date

 Access Path:
 +-JOIN HASH [Cost: 463, Rows: 622 (NO STATISTICS)] (PATH ID: 2)
 |  Join Cond: (sales.store_key = stores.store_key)
 |  Materialize at Output: sales.sale_date, sales.sales_quantity, sales.product_description
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for sales [Cost: 150, Rows: 155K (NO STATISTICS)] (PATH ID: 3)
 | |      Projection: store.storeSales_b0
 | |      Materialize: sales.store_key
 | |      Filter: (sales.sale_date = '2014-12-04'::date)
 | |      Execute on: All Nodes
 | |      Runtime Filter: (SIP1(HashJoin): sales.store_key)
 | +-- Inner -> STORAGE ACCESS for stores [Cost: 35, Rows: 2] (PATH ID: 4)
 | |      Projection: store.store_dimension_DBD_10_rep_VMartDesign_node0001
 | |      Materialize: stores.store_name, stores.store_key
 | |      Filter: (stores.store_name = 'Store14')
 | |      Execute on: All Nodes

Custom directed queries

The VERBATIM hint is included in a custom directed query only if you explicitly include it in the annotated query that you write for that directed query. When the optimizer uses that directed query, it respects the VERBATIM hint and creates a query plan accordingly.

If you omit the VERBATIM hint when you create a custom directed query, the hint is not stored with the annotated query. When the optimizer uses that directed query, it applies its own plan development processing on the annotated query before it generates a query plan. This query plan might not be equivalent to the query plan that the optimizer would have generated for the Vertica version in which the directed query was created.