Lantao Jin created CALCITE-7857:
-----------------------------------
Summary: Add a VECTOR data type
Key: CALCITE-7857
URL: https://issues.apache.org/jira/browse/CALCITE-7857
Project: Calcite
Issue Type: New Feature
Components: core
Reporter: Lantao Jin
h2. Motivation
Vector (embedding) search has become a standard database feature, and most SQL
engines now have a vector type whose dimension is part of the type: PostgreSQL
with pgvector {{vector(n)}} (also {{halfvec(n)}}, {{bit(n)}}), SQL Server 2025
{{VECTOR(n [, float16])}}, Oracle 26ai {{VECTOR(n, FLOAT32|INT8|BINARY)}},
Snowflake {{VECTOR(FLOAT|INT, n)}}, MariaDB, MySQL and TiDB {{VECTOR(n)}},
Cassandra 5 {{VECTOR<FLOAT, n>}}, SAP HANA {{REAL_VECTOR(n)}}. DuckDB uses
fixed-size arrays {{FLOAT[n]}}, and Spark has an open SPIP (SPARK-58787) for
{{VECTOR(n, type)}}.
Calcite has no vector type, and cannot represent an embedding's dimension:
{{RelDataTypeFactory.createArrayType(elementType, maxCardinality)}} accepts a
cardinality, but {{SqlTypeFactoryImpl.createArrayType}} asserts
{{maxCardinality == -1}}, and {{ArraySqlType}} stores only the element type.
Downstream projects work around this. The OpenSearch SQL plugin's RFC for
vector search in PPL on Calcite (opensearch-project/sql#5875) emulates a vector
type with a custom {{ArraySqlType}} subclass; the proposed Milvus adapter
(CALCITE-7353) maps vector fields to existing types through adapter metadata.
Neither can check dimensions during validation, generate the right type in SQL
for a database that has a vector type, or give distance functions a precise
operand type.
h2. Proposal
Add a type {{VECTOR(dimension [, elementType])}}, where {{dimension}} is a
positive integer and {{elementType}} is {{REAL}} (4-byte float, the default) or
{{TINYINT}} (1-byte integer).
{code:sql}
SELECT CAST(ARRAY[1, 2.5, -3] AS VECTOR(3)); -- type VECTOR(3, REAL)
SELECT CAST('[1, 2.5, -3]' AS VECTOR(3)); -- same value
SELECT CAST(ARRAY[1, 2, 3] AS VECTOR(3, TINYINT));
SELECT CAST(v AS REAL ARRAY), CAST(v AS VARCHAR) FROM t;
-- With the server module's DDL
CREATE TABLE docs (id INT, embedding VECTOR(768));
INSERT INTO docs VALUES (1, ARRAY[...]), (2, '[...]'); -- implicit cast on
INSERT
{code}
h2. Semantics
# *Type.* New {{SqlTypeName.VECTOR}}, {{SqlTypeFamily.VECTOR}} and class
{{VectorSqlType}} (a sibling of {{ArraySqlType}}, not a subclass), created by a
new method {{RelDataTypeFactory.createVectorType(elementType, dimension)}}. The
type's digest is {{VECTOR(3, REAL)}}; {{getComponentType()}} returns the
element type, and {{getPrecision()}} returns the dimension, so JDBC reports the
dimension as the column size.
# *Nullability.* Elements are never null; a vector value as a whole may be null.
# *Explicit casts.* An {{ARRAY}} of numbers and a {{VECTOR}} cast to each
other, element by element. A character string such as {{'[1, 2.5, -3]'}}
(numbers separated by commas, in square brackets) casts to a {{VECTOR}}, and a
{{VECTOR}} casts to a string in the same format. A {{VECTOR}} casts to a
{{VECTOR}} of the same dimension with a different element type. Casts to and
from other types (numbers, {{BINARY}}, {{VARIANT}}, {{MULTISET}}, {{MAP}}) are
not allowed.
# *Dimension checks.* If the operand of {{CAST}} is an array constructor or a
character literal, a wrong number of elements is a validation error; otherwise
it is a run-time error, as is a null element.
# *Implicit conversions.* None in expressions. A vector can be assigned to a
{{VECTOR}} of the same dimension and element type, and {{CASE}}, {{COALESCE}}
and {{UNION ALL}} accept vectors of the same dimension and element type. With
type coercion, {{INSERT}} and {{UPDATE}} cast an {{ARRAY}}, a character string,
or a vector with another element type to the target column's {{VECTOR}} type.
# *Comparability.* {{VectorSqlType.getComparability()}} returns {{NONE}}, so
{{=}}, {{<>}}, {{<}}, {{IS DISTINCT FROM}}, {{NULLIF}}, {{MIN}} and {{MAX}}
reject vectors. The validator also rejects vectors as keys of {{ORDER BY}},
{{GROUP BY}}, {{PARTITION BY}}, window {{ORDER BY}}, {{SELECT DISTINCT}},
aggregate {{DISTINCT}}, {{WITHIN DISTINCT}} and {{WITHIN GROUP}}, {{UNION}},
{{INTERSECT}}, {{EXCEPT}} and {{USING}}/{{NATURAL}} joins; {{IN}} and
{{BETWEEN}} already reject them. These checks use the type's comparability, so
they also apply to user-defined structured types whose comparability is
{{NONE}}. SQL Server and Oracle have similar restrictions; pgvector allows
ordering and equality. Allowing equality later would be backward-compatible,
but disallowing it later would not.
# *Run time.* As with {{ARRAY}}, a vector is a {{java.util.List}} of its
elements; JDBC returns it as a {{java.sql.Array}}.
# *SQL generation.* The Calcite dialect writes {{VECTOR(n, elementType)}}. The
PostgreSQL dialect writes pgvector's {{VECTOR(n)}} for {{REAL}} elements. Other
dialects throw {{UnsupportedOperationException}}; by default,
{{SqlDialect.supportsDataType}} now returns false for {{VECTOR}}.
# *Serialization.* {{RelJson}} writes a vector type with its {{component}} and
{{dimension}}. There are no {{RexLiteral}}s of type {{VECTOR}}; a constant
vector is a {{CAST}} of an array constructor.
h2. Scope
This issue: the type, parser, validator, casts, Enumerable run time,
{{RelJson}}, SQL generation for the Calcite and PostgreSQL dialects, and
documentation in {{reference.md}}.
Follow-up issues:
* Distance functions, such as {{COSINE_DISTANCE}}, {{L2_DISTANCE}} and
{{INNER_PRODUCT}}, in {{SqlLibraryOperators}} for each library (pgvector's
{{<->}}, {{<=>}} and {{<#>}} operators, {{VECTOR_DISTANCE}} in SQL Server and
Oracle, {{VECTOR_COSINE_SIMILARITY}} in Snowflake), with SQL generation for
each dialect.
* More element types (DOUBLE, half precision, bit vectors), sparse vectors, and
a maximum dimension in {{RelDataTypeSystem}}.
* SQL generation for SQL Server, Oracle, Snowflake, MySQL and MariaDB; mapping
of vendor vector types in the JDBC adapter.
* Casts to and from {{VARIANT}}; element access {{v[i]}} and {{CARDINALITY(v)}}
without a cast to {{ARRAY}}.
* Comparability of a {{ROW}} that contains a vector: {{ROW(v, 1) = ROW(v, 1)}}
is not yet rejected, because a record type is always comparable.
Out of scope: index-aware planning, such as rules that use an
approximate-nearest-neighbor index for {{ORDER BY distance LIMIT k}}.
h2. Open questions
# Syntax: {{VECTOR(n, type)}} (SQL Server, Oracle, the Spark SPIP),
{{VECTOR(type, n)}} (Snowflake), {{VECTOR<type, n>}} (Cassandra), or {{type
VECTOR(n)}} (by analogy with {{type ARRAY}})?
# Element type names: Calcite's ({{REAL}}, {{TINYINT}}) or vendor names
({{FLOAT32}}, {{INT8}})? Which element types belong in the first version?
# Should {{VECTOR}} be a subtype of {{ARRAY}} for coercion, so that array
functions accept vectors directly, or stay a distinct type as proposed?
# Should vectors support equality (comparability {{UNORDERED}}), and therefore
{{GROUP BY}} and {{DISTINCT}}, as pgvector does?
# Should the element type have a default ({{REAL}}), or be required?
# Should a cast from a string accept {{NaN}} and infinite values? (pgvector
rejects them.)
h2. References
* pgvector: https://github.com/pgvector/pgvector
* SQL Server vector data type:
https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type
* Oracle VECTOR data type:
https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/create-tables-using-vector-data-type.html
* Snowflake VECTOR data type:
https://docs.snowflake.com/en/sql-reference/data-types-vector
* SPARK-58787 (SPIP: VECTOR type)
* OpenSearch SQL RFC, vector search in PPL:
https://github.com/opensearch-project/sql/issues/5875
* CALCITE-7353 (Milvus adapter)
* Precedents for a new type: CALCITE-6738 (UUID) and follow-ups CALCITE-6777,
CALCITE-6779, CALCITE-6992, CALCITE-7128, CALCITE-7158, CALCITE-7716,
CALCITE-7727, CALCITE-7768; CALCITE-4918 (VARIANT)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
