This section provides a comprehensive reference for SQL support in Spice.ai, including syntax, data types, operators, functions, and system features. The reference is organized by topic for ease of navigation.
Spark-compatible scalar functions such as array, bit_get, date_add, like, and parse_url follow the semantics documented in the Spark SQL built-in function reference (opens in a new tab).
Window functions perform calculations across sets of rows related to the current row. Spice supports window functions using the OVER clause with aggregate and ranking functions. See Aggregate Functions (opens in a new tab) for functions that support the OVER clause, including ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE.
Example:
vector_search) (opens in a new tab)text_search) (opens in a new tab)rrf) (opens in a new tab)rerank) (opens in a new tab)Spice uses Apache Arrow data types internally. For data type compatibility with accelerators, see Data Type Reference (opens in a new tab). Common SQL types include:
| SQL Type | Description |
|---|---|
INT, BIGINT | Integer types |
FLOAT, DOUBLE | Floating-point types |
VARCHAR, TEXT | String types |
BOOLEAN | Boolean type |
TIMESTAMP | Timestamp with nanosecond precision |
DATE | Date type |
DECIMAL | Arbitrary precision numeric |
Use CAST(expression AS type) or expression::type to convert between types.
A fractional number literal such as 12345678.90 is planned as a DOUBLE (Float64). When the literal is compared with or cast to a DECIMAL, Spice converts it from its decimal digits rather than from its binary floating-point value, within the limits described below, so the comparison matches the value as written:
+---+ | n | +---+ | 1 | +---+
Bound Float64 and Float32 query parameters, such as $1 in a prepared statement (opens in a new tab), have no written digits, so Spice converts them from the shortest decimal spelling of the value.
For both literals and parameters, the conversion applies when the digits fit: at most 15 significant digits and 15 digits before the decimal point (6 for a Float32 parameter), no more fraction digits than the decimal's scale, and no more digits than its precision. Any other value keeps the standard floating-point cast, with its rounding and overflow behavior. For example, CAST(1.234 AS DECIMAL(10, 2)) needs rounding, CAST(0.30000000000000004 AS DECIMAL(38, 18)) has 17 significant digits, and a bound 1e30 cast to DECIMAL(38, 0) has 31 digits before the decimal point, so all three keep the floating-point cast.
As an exception, a literal that a Float64 stores as a different, shorter number, such as 4.0700000000000003 (stored as 4.07), is converted from its written digits and rounded half away from zero to the decimal's scale. When one statement writes two different numbers that read as the same Float64, such as price = 4.0700000000000003 OR price = 4.07, Spice cannot tell those literals apart, and both keep the floating-point cast.
To compare against a value outside these limits exactly, write it as a string cast, for example CAST('0.30000000000000004' AS DECIMAL(38, 18)), or pass a Decimal128 parameter.
Refer to each section for detailed syntax, supported features, and examples.