SQL Reference

Complete SQL reference for Spice.ai including SELECT syntax, subqueries, DML statements, aggregate functions, AI functions, JSON operators, and search capabilities.

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.

SELECT Syntax

Subqueries

EXPLAIN

Information Schema

AI Functions

Operators and Literals

Scalar Functions

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.

Aggregate Functions

Window Functions

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

SELECT
  dept_id,
  employee_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;

JSON Functions and Operators

Search

Prepared Statements

Positional Arguments

DML (Data Manipulation Language)

INSERT Statement

Data Types

Spice uses Apache Arrow data types internally. For data type compatibility with accelerators, see Data Type Reference. 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.

Comparing Decimals with Fractional Literals

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:

SELECT count(*) AS n
FROM (SELECT CAST('12345678.90' AS DECIMAL(10, 2)) AS price) AS t
WHERE price = 12345678.90;

+---+
| n |
+---+
| 1 |
+---+

Bound Float64 and Float32 query parameters, such as $1 in a prepared statement, 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.

Table of Contents

Window Functions

Data Types

Comparing Decimals with Fractional Literals