Operators

info

Spice is built on Apache DataFusion and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.

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

`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 Concatenation

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

`@>`

Array Contains

> 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

> 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

Numerical Operators​

+​

-​

*​

/​

%​

Comparison Operators​

=​

!=​

<​

<=​

>​

>=​

<=>​

IS DISTINCT FROM​

IS NOT DISTINCT FROM​

~​

~*​

!~​

!~*​

~~

~~*​

!~~​

!~~*​

Logical Operators​

AND​

OR​

Bitwise Operators​

&​

|​

#​

>>​

<<​

Type Casting Operators​

CAST(expr AS type)​

:: (PostgreSQL-style cast)​

Other Operators​

||​

@>​

<@​

Literals​

Escaping​

E-String Escape Sequences​