[
https://issues.apache.org/jira/browse/CALCITE-7857?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18128550#comment-18128550
]
Julian Hyde edited comment on CALCITE-7857 at 10/19/26 12:28 AM:
-----------------------------------------------------------------
Why a whole new data type? Why not {{ARRAY}} with a constraint?
{code:sql}
CREATE TABLE my_table (
id INT,
embedding ARRAY<REAL> CHECK (CARDINALITY(embedding) = 1536)
);
{code}
I believe that Calcite should support vectors, and this seems a well-considered
proposal. I suspect that there are many others who would like vector support in
Calcite, so let's get their input before we move forward with the proposal.
I see a tension between perception and reality. There is clearly a strong
incentive to look like other vector databases, and in particular adopt the
{{VECTOR}} data type. But if we create a new type, we are not building on the
work we have done in the existing {{ARRAY}} type. People use Calcite not for
its parser but because it deals with the hard parts of query compilation -- in
particular planning -- with relatively little effort and few surprises.
Perhaps {{VECTOR}} could be syntactic sugar for a fixed-length {{ARRAY}} type?
(You can use either {{VECTOR}} or {{ARRAY}} in the {{CREATE TABLE}} statement,
but the columns would appear in the catalog as {{ARRAY}}.)
Are there any places that {{ARRAY}} and {{VECTOR}} are at odds? E.g. do we
allow {{ORDER BY an_array_column}} and would we want to disallow {{ORDER BY
a_vector_column}}?
I would be inclined to accept {{VECTOR}} of any element type (as we do for
{{ARRAY}}) but strongly suggest that people use {{REAL NOT NULL}} and {{TINYINT
NOT NULL}}, and write implementations of important functions such as
{{COSINE_SIMILARITY}} only for those types. {{ArrayList<Float>}} would be a
foolish implementation in Java... a custom {{List}} backed by a {{float[]}}
seems much more time and memory efficient.
was (Author: julianhyde):
Why a whole new data type? Why not {{ARRAY}} with a constraint?
{code:sql}
CREATE TABLE my_table (
id INT,
embedding ARRAY<REAL> CHECK (CARDINALITY(embedding) = 1536)
);
{code}
> 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)
