Skip to main content
Version: Latest (v2.4)

Operators

info

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

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

> 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                           |
+--------------------------------+

IS DISTINCT FROM and IS NOT DISTINCT FROM bind more tightly than AND and OR, as in PostgreSQL: a IS DISTINCT FROM b OR c is (a IS DISTINCT FROM b) OR c. Spice v2.4.1 and earlier read the right operand as everything up to the end of the expression, so a IS DISTINCT FROM b OR c was a IS DISTINCT FROM (b OR c). On those versions, SELECT true IS DISTINCT FROM true OR true returns false, a WHERE filter of that form can drop rows, and an integer operand fails with Cannot infer common argument type for logical boolean operation Int64 OR Boolean. Wrap the comparison in parentheses to get the same result on every version.

~​

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                   |
+----------------------+

Type Casting Operators​

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:

TypeDescription
INT / INTEGER / INT432-bit signed integer
BIGINT / INT864-bit signed integer
SMALLINT / INT216-bit signed integer
FLOAT / REAL / FLOAT432-bit floating point
DOUBLE / FLOAT864-bit floating point
TEXT / VARCHAR / STRINGVariable-length string
BOOLEAN / BOOLTrue/false value
DATECalendar date
TIMESTAMPDate and time
INTERVALTime duration

Other Operators​

||​

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:

EscapeCharacter
\nNewline
\tTab
\rCarriage return
\\Backslash
\'Single quote