# Operators

> **Note**
>
> Spice is built on [Apache DataFusion](https://datafusion.apache.org/) and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.

## Numerical Operators

- [+ (plus)](https://spice.ai/docs/sql/operators#op_plus)
- [- (minus)](https://spice.ai/docs/sql/operators#op_minus)
- [\* (multiply)](https://spice.ai/docs/sql/operators#op_multiply)
- [/ (divide)](https://spice.ai/docs/sql/operators#op_divide)
- [% (modulo)](https://spice.ai/docs/sql/operators#op_modulo)

### `+`

Addition

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

### `-`

Subtraction

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

### `*`

Multiplication

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

### `/`

Division (integer division truncates toward zero)

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

### `%`

Modulo (remainder)

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

## Comparison Operators

- [= (equal)](https://spice.ai/docs/sql/operators#op_eq)
- [!= (not equal)](https://spice.ai/docs/sql/operators#op_neq)
- [< (less than)](https://spice.ai/docs/sql/operators#op_lt)
- [<= (less than or equal to)](https://spice.ai/docs/sql/operators#op_le)
- [> (greater than)](https://spice.ai/docs/sql/operators#op_gt)
- [>= (greater than or equal to)](https://spice.ai/docs/sql/operators#op_ge)
- [<=> (three-way comparison, alias for IS NOT DISTINCT FROM)](https://spice.ai/docs/sql/operators#op_spaceship)
- [IS DISTINCT FROM](https://spice.ai/docs/sql/operators#is-distinct-from)
- [IS NOT DISTINCT FROM](https://spice.ai/docs/sql/operators#is-not-distinct-from)
- [\~ (regex match)](https://spice.ai/docs/sql/operators#op_re_match)
- [\~\* (regex case-insensitive match)](https://spice.ai/docs/sql/operators#op_re_match_i)
- [!\~ (not regex match)](https://spice.ai/docs/sql/operators#op_re_not_match)
- [!\~\* (not regex case-insensitive match)](https://spice.ai/docs/sql/operators#op_re_not_match_i)

### `=`

Equal

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

### `!=`

Not Equal

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

### `<`

Less Than

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

### `<=`

Less Than or Equal To

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

### `>`

Greater Than

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

### `>=`

Greater Than or Equal To

```sql
> 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.

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

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

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

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

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

### `IS NOT DISTINCT FROM`

The negation of `IS DISTINCT FROM`

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

### `~`

Regex Match

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

### `~*`

Regex Case-Insensitive Match

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

### `!~`

Not Regex Match

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

### `!~*`

Not Regex Case-Insensitive Match

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

### `~~`

Like Match

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

### `~~*`

Case-Insensitive Like Match

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

### `!~~`

Not Like Match

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

### `!~~*`

Not Case-Insensitive Like Match

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

## Logical Operators

- [AND](https://spice.ai/docs/sql/operators#and)
- [OR](https://spice.ai/docs/sql/operators#or)

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:

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

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

### `OR`

Logical Or

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

## Bitwise Operators

- [& (bitwise and)](https://spice.ai/docs/sql/operators#op_bit_and)
- [| (bitwise or)](https://spice.ai/docs/sql/operators#op_bit_or)
- [# (bitwise xor)](https://spice.ai/docs/sql/operators#op_bit_xor)
- [>> (bitwise shift right)](https://spice.ai/docs/sql/operators#op_shift_r)
- [<< (bitwise shift left)](https://spice.ai/docs/sql/operators#op_shift_l)

### `&`

Bitwise And

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

### `|`

Bitwise Or

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

### `#`

Bitwise Xor (interchangeable with `^`)

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

### `>>`

Bitwise Shift Right

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

### `<<`

Bitwise Shift Left

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

## Type Casting Operators

- [`CAST(expr AS type)`](https://spice.ai/docs/sql/operators#op_cast): Explicit type conversion
- [`::` (PostgreSQL-style cast)](https://spice.ai/docs/sql/operators#op_double_colon): Shorthand type conversion

### `CAST(expr AS type)`

Converts an expression to the specified data type.

```sql
> 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`.

```sql
> 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)](https://spice.ai/docs/sql/operators#op_str_cat)
- [\`@>\` (array contains)](https://spice.ai/docs/sql/operators#op_arr_contains)
- [`<@` (array is contained by)](https://spice.ai/docs/sql/operators#op_arr_contained_by)

### `||`

String or array concatenation. When both operands are strings, concatenates them. When both are arrays (lists), invokes [`array_concat`](https://spice.ai/docs/sql/scalar-functions#array_concat); when one side is a list and the other is a scalar of the element type, invokes [`array_append`](https://spice.ai/docs/sql/scalar-functions#array_append) or [`array_prepend`](https://spice.ai/docs/sql/scalar-functions#array_prepend).

```sql
> 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`](https://spice.ai/docs/sql/scalar-functions#array_has_all). Only supported with list/array arguments.

```sql
> 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.

```sql
> 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.

```sql
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 `''`:

```sql
> 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:

```sql
> 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:

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

Supported escape sequences:

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