[
https://issues.apache.org/jira/browse/CALCITE-7857?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18129060#comment-18129060
]
Lantao Jin commented on CALCITE-7857:
-------------------------------------
Thanks [~julianhyde],
On ARRAY + CHECK: I did consider it, but the dimension needs to live in the
type. A lot of the places where it matters have no table behind them: {{CAST(?
AS VECTOR(768))}}, a parameter in {{INSERT ... VALUES (?, ?)}}, {{COALESCE(v,
w)}}, the arguments of a distance function. With the dimension in the type, a
mismatch there is caught at validation; a CHECK constraint can't help with any
of that. And as Andrew says, an adapter has to know a column is a vector to
generate the right type for PostgreSQL, SQL Server, Oracle or Phoenix, and to
decide what it can push down.
I agree we should build on ARRAY rather than next to it. Making VECTOR sugar
for a fixed-length ARRAY would mean teaching {{ArraySqlType}} about cardinality
first (today {{createArrayType}} asserts {{maxCardinality == -1}}), and
unparse, push-down and comparability would still have to tell a vector from an
array, so it would be a separate type in practice anyway. What I could do
instead is let a VECTOR be used anywhere an ARRAY of its element type is
accepted, either through an implicit cast to ARRAY or by making
{{VectorSqlType}} a subtype of {{ArraySqlType}}. Then CARDINALITY, {{v[i]}} and
the existing array functions just work. Do you have a preference between the
two?
ORDER BY is indeed where they differ. On main you can ORDER BY or take MIN of
an array column (lexicographic). I made VECTOR non-comparable, like SQL Server
and Oracle (pgvector does allow it), because ordering embeddings isn't
meaningful. It's easy to allow later if people want it; taking it away later
would be much harder. Other differences: elements can't be null, the dimension
is fixed, you can cast from a string like {{'[1, 2.5, -3]'}}, and each dialect
spells the type differently.
Any numeric element type is fine with me, with REAL and TINYINT as the
recommended ones and the first to get functions like COSINE_SIMILARITY.
You're right about {{ArrayList<Float>}}. That's what the PR does now, since it
reuses the array code path. I'd change it to an immutable {{List<Float>}}
backed by a {{float[]}} (and {{byte[]}} for TINYINT), so anything that treats
it as a List or calls JDBC {{getArray}} still works, and distance functions can
read the primitive array. Should be close to what Andrew describes for Phoenix.
I can do that in this PR or as a follow-up, whichever you prefer.
> 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
> Priority: Major
> Labels: pull-request-available
>
> 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)
