Operators - Firebolt Documentation

Preview Spark operators in SparkSQL and Databricks compatibility modes. Both modes accept everything listed here unless a row names one mode.

Supported operators

Operator	Support	Notes
Arithmetic: `+`, `-`, `*`, `/`, `%`	With limitations	`/` on two integers is integer division; see below. `%` takes integers only.
`DIV`	SparkSQL mode only	Integer division, as `7 DIV 2` returns `3`.
Bitwise: `&`, `\|`, `^`, `~`, `<<`, `>>`	Supported	`^` is exclusive or, as in Spark.
`>>>`	Not supported	Use `shiftright` on a non-negative value.
Comparison: `=`, `==`, `!=`, `<>`, `<`, `<=`, `>`, `>=`	Supported
`<=>`	Supported	Null-safe equality, the same as `IS NOT DISTINCT FROM`.
`IS [NOT] NULL`, `IS [NOT] TRUE`, `IS [NOT] FALSE`, `IS [NOT] DISTINCT FROM`	Supported
`BETWEEN`, `IN`	Supported	`IN` does not accept `STRUCT` values.
Logical: `AND`, `OR`, `NOT`, `!`	Supported
`&&`	Not supported	Use `AND`.
`\|\|`	Supported	Concatenates strings and arrays.
`::`	Supported	Equivalent to `CAST`.
`LIKE`, `ILIKE`	With limitations	A backslash escapes `_` and `%`. An `ESCAPE` clause with any other character, such as `ESCAPE '/'`, is not supported; write the pattern with a backslash instead.
`LIKE ANY`, `LIKE ALL`, `LIKE SOME`, `ILIKE ANY`, `ILIKE ALL`	Supported	Over a list of patterns.
`RLIKE`, `REGEXP`	Supported	RE2 syntax.
`SIMILAR TO`	Not supported
`[]` subscript	Supported	Zero-based: `array(10, 20)[0]` returns `10`.
`.` field access	Supported	`named_struct('a', 1).a` returns `1`.
`CASE`	Supported	Both the simple and the searched form.

Integer division

Integer division differs from Spark and is the difference most likely to affect a real workload, because division is common and the result is plausible rather than obviously wrong:

SELECT 5 / 2;

Returns 2, where Spark returns 2.5. Spark’s / always produces a DOUBLE or DECIMAL, so any migrated query that divides two integers returns different numbers here, with no diagnostic. Cast one operand to make the intent explicit:

SELECT CAST(5 AS DOUBLE) / 2;   -- 2.5 in both

Other differences

Arithmetic overflow, division by zero, and an invalid cast raise an error, as in Spark’s default ANSI mode. try_cast returns NULL instead.
A string is converted implicitly where a number is expected, as in Spark: '2' * 3 returns 6, and a string that is not a number raises an error.