Expressions

SQL expressions are the components of a query that compare a value or values against other values.

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.

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