Operators

Numerical Operators

+ (plus)
- (minus)
* (multiply)
/ (divide)
% (modulo)

`+`

Addition

> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3                   |
+---------------------+

`-`

Subtraction

> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1                   |
+---------------------+

`*`

Multiplication

> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6                   |
+---------------------+

`/`

Division (integer division truncates toward zero)

> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2                   |
+---------------------+

`%`

Modulo (remainder)

> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1                   |
+---------------------+

Comparison Operators

= (equal)
!= (not equal)
< (less than)
<= (less than or equal to)
> (greater than)
>= (greater than or equal to)
<=> (three-way comparison, alias for IS NOT DISTINCT FROM)
IS DISTINCT FROM
IS NOT DISTINCT FROM
~ (regex match)
~* (regex case-insensitive match)
!~ (not regex match)
!~* (not regex case-insensitive match)

`=`

Equal

> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true                |
+---------------------+

`!=`

Not Equal

> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true                 |
+----------------------+

`<`

Less Than

> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true                |
+---------------------+

`<=`

Less Than or Equal To

> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true                 |
+----------------------+

`>`

Greater Than

> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true                |
+---------------------+

`>=`

Greater Than or Equal To

> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true                 |
+----------------------+

`<=>`

Three-way comparison operator. A NULL-safe operator that returns true if both operands are equal or both are NULL, false otherwise.

> SELECT NULL <=> NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT 1 <=> NULL;
+------------------------------------+
| Int64(1) IS NOT DISTINCT FROM NULL |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 1 <=> 2;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(2) |
+----------------------------------------+
| false                                  |
+----------------------------------------+

> SELECT 1 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

`IS DISTINCT FROM`

Guarantees the result of a comparison is true or false and not an empty set

> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

`IS NOT DISTINCT FROM`

The negation of IS DISTINCT FROM

> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

`~`

Regex Match

> SELECT 'foo' ~ '^foo(-cli)*';
+-----------------------------------+
| Utf8("foo") ~ Utf8("^foo(-cli)*") |
+-----------------------------------+
| true                              |
+-----------------------------------+

`~*`

Regex Case-Insensitive Match

> SELECT 'foo' ~* '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") ~* Utf8("^foo(-cli)*") |
+------------------------------------+
| true                               |
+------------------------------------+

`!~`

Not Regex Match

> SELECT 'foo' !~ '^foo(-cli)*';
+------------------------------------+
| Utf8("foo") !~ Utf8("^foo(-cli)*") |
+------------------------------------+
| false                              |
+------------------------------------+

`!~*`

Not Regex Case-Insensitive Match

> SELECT 'foo' !~* '^FOO(-cli)+';
+-------------------------------------+
| Utf8("foo") !~* Utf8("^FOO(-cli)+") |
+-------------------------------------+
| true                                |
+-------------------------------------+

`~~`

Like Match

SELECT 'foobar' ~~ 'f_o%r';
+---------------------------------+
| Utf8("foobar") ~~ Utf8("f_o%r") |
+---------------------------------+
| true                            |
+---------------------------------+

`~~*`

Case-Insensitive Like Match

SELECT 'foobar' ~~* 'F_o%r';
+----------------------------------+
| Utf8("foobar") ~~* Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

`!~~`

Not Like Match

SELECT 'foobar' !~~ 'F_o%r';
+----------------------------------+
| Utf8("foobar") !~~ Utf8("F_o%r") |
+----------------------------------+
| true                             |
+----------------------------------+

`!~~*`

Not Case-Insensitive Like Match

SELECT 'foobar' !~~* 'F_o%Br';
+------------------------------------+
| Utf8("foobar") !~~* Utf8("F_o%Br") |
+------------------------------------+
| true                               |
+------------------------------------+

Logical Operators

Spice rejects a SQL statement whose expression tree is nested more than 512 levels deep, before it plans the query. A flat chain of AND or OR terms nests one level per term, so a WHERE clause with several hundred OR conditions can exceed the limit even though it contains no parentheses. An IN list is wide rather than deep and is not limited by this check, so rewrite a long chain of equality tests as IN (...), or join against a table of the values. The limit is fixed and cannot be configured. A rejected query fails with HTTP 400 on /v1/sql and the gRPC status InvalidArgument over Arrow Flight SQL, and the error contains:

Query rejected: an expression is nested more than 512 levels deep. Simplify the query (for example, replace a long chain of OR/AND conditions with an IN list or a join).

`AND`

Logical And

> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

`OR`

Logical Or

> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

Bitwise Operators

& (bitwise and)
| (bitwise or)
# (bitwise xor)
>> (bitwise shift right)
<< (bitwise shift left)

`&`

Bitwise And

> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1                   |
+---------------------+

`|`

Bitwise Or

> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7                   |
+---------------------+

`#`

Bitwise Xor (interchangeable with ^)

> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6                   |
+---------------------+

`>>`

Bitwise Shift Right

> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0                    |
+----------------------+

`<<`

Bitwise Shift Left

> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40                   |
+----------------------+

Type Casting Operators

CAST(expr AS type): Explicit type conversion
:: (PostgreSQL-style cast): Shorthand type conversion

`CAST(expr AS type)`

Converts an expression to the specified data type.

> SELECT CAST('123' AS INT);
+------------------------+
| CAST(Utf8("123") AS Int32) |
+------------------------+
| 123                    |
+------------------------+

> SELECT CAST(3.14159 AS INT);
+--------------------------+
| CAST(Float64(3.14159) AS Int32) |
+--------------------------+
| 3                        |
+--------------------------+

> SELECT CAST('2024-01-15' AS DATE);
+----------------------------+
| CAST(Utf8("2024-01-15") AS Date32) |
+----------------------------+
| 2024-01-15                 |
+----------------------------+

`::` (PostgreSQL-style cast)

Shorthand syntax for type conversion, equivalent to CAST.

> SELECT '123'::INT;
+-----------------------+
| Utf8("123") AS Int32  |
+-----------------------+
| 123                   |
+-----------------------+

> SELECT '2024-01-15'::DATE;
+---------------------------+
| Utf8("2024-01-15") AS Date32 |
+---------------------------+
| 2024-01-15                |
+---------------------------+

> SELECT 100::TEXT;
+-------------------+
| Int64(100) AS Utf8 |
+-------------------+
| 100               |
+-------------------+

Supported Types:

Type	Description
`INT` / `INTEGER` / `INT4`	32-bit signed integer
`BIGINT` / `INT8`	64-bit signed integer
`SMALLINT` / `INT2`	16-bit signed integer
`FLOAT` / `REAL` / `FLOAT4`	32-bit floating point
`DOUBLE` / `FLOAT8`	64-bit floating point
`TEXT` / `VARCHAR` / `STRING`	Variable-length string
`BOOLEAN` / `BOOL`	True/false value
`DATE`	Calendar date
`TIMESTAMP`	Date and time
`INTERVAL`	Time duration

Other Operators

|| (string concatenation)
`@>` (array contains)
<@ (array is contained by)

`||`

String or array concatenation. When both operands are strings, concatenates them. When both are arrays (lists), invokes array_concat; when one side is a list and the other is a scalar of the element type, invokes array_append or array_prepend.

> SELECT 'Hello, ' || 'Spice!';
+-----------------------------------+
| Utf8("Hello, ") || Utf8("Spice!") |
+-----------------------------------+
| Hello, Spice!                     |
+-----------------------------------+

> SELECT [1, 2] || [3, 4];
+-------------------------+
| List([1,2]) || List([3,4]) |
+-------------------------+
| [1, 2, 3, 4]            |
+-------------------------+

> SELECT [1, 2] || 3;
+----------------------+
| List([1,2]) || Int64(3) |
+----------------------+
| [1, 2, 3]            |
+----------------------+

`@>`

Array contains. Returns true if every element of the right array is present in the left. Equivalent to array_has_all. Only supported with list/array arguments.

> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

`<@`

Array is contained by. Returns true if every element of the left array is present in the right. Equivalent to array_has_all(right, left). Only supported with list/array arguments.

> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

Literals

Use single quotes for literal string values.

SELECT 'foo';

Escaping

SQL literals do not support C-style escape sequences such as \n for newline by default. All characters in a ' string are treated literally.

To escape ' in SQL literals, use '':

> SELECT 'it''s escaped';
+----------------------+
| Utf8("it's escaped") |
+----------------------+
| it's escaped         |
+----------------------+

Strings such as 'foo\nbar' contain a literal backslash followed by n, not a newline:

> SELECT 'foo\nbar';
+------------------+
| Utf8("foo\nbar") |
+------------------+
| foo\nbar         |
+------------------+

E-String Escape Sequences

To include escaped characters such as newline or tab, use E-prefixed strings:

> SELECT E'foo\nbar';
+-----------------+
| Utf8("foo
bar") |
+-----------------+
| foo
bar         |
+-----------------+

Supported escape sequences:

Escape	Character
`\n`	Newline
`\t`	Tab
`\r`	Carriage return
`\\`	Backslash
`\'`	Single quote