info
Spice is built on Apache DataFusion and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.
Operators
Numerical Operators​
+​
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
> 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 |
+----------------------+
Other Operators​
||​
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 |
+-------------------------------------------------------------------------+
