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.

When an expression includes more than one operator, Vertica recommends that you specify the order of operation using parentheses, rather than relying on operator precedence.

Operator/Element Associativity Description
.

left

table/column name separator

::

left

typecast

[ ]

left

array element selection

-

right

unary minus

^

left

exponentiation

* / %

left

multiplication, division, modulo

+ -

left

addition, subtraction

IS

IS TRUE, IS FALSE, IS UNKNOWN, IS NULL

IN

set membership

BETWEEN

range containment

OVERLAPS

time interval overlap

LIKE

string pattern matching

< >

less than, greater than

=

right

equality, assignment

NOT

right

logical negation

AND

left

logical conjunction

OR

left

logical disjunction

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.

In This Section