Expressions
SQL expressions are the components of a query that compare a value or values against other values. They can also perform calculations. An expression found inside a SQL statement is usually in the form of a conditional statement.
Some functions also use Lambda functions.
Operator precedence
The following table shows operator precedence in decreasing (high to low) order.
When an expression includes more than one operator, 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; --- unsafe
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
Expressions are limited by the available stack. 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" might be thrown. Adding physical memory and/or increasing the value of ulimit -s can increase the available stack and prevent the error.
Analytic expressions have a maximum recursion depth of 2000. If this limit is exceeded then the error "The query contains an expression that is too complex to analyze" might be thrown. This limit cannot be increased.