You are viewing a plain text version of this content. The canonical link for it is here.

Posted to issues@hop.apache.org by "bamaer (via GitHub)" <gi...@apache.org> on 2026/09/19 11:30:58 UTC

[PR] Fixes #8411 : Emit native vector column types from database dialects … (hop)

bamaer opened a new pull request, #8463:
URL: https://github.com/apache/hop/pull/8463

…that support them

Fixes #8411

`IDatabase.getFieldDefinition()` had no case for the Vector value type added in #8409, so a vector
field became a text column and the user had to hand-edit the generated DDL to `vector(1536)`.

Six dialects now emit their own vector column, and three of them needed a value binding as well:
the DDL alone would have produced a table Hop could not then insert into.

| Dialect | Dimension known | Dimension unknown | Binding |
|---|---|---|---|
| PostgreSQL | `VECTOR(n)` | `VECTOR` | yes, unspecified type |
| Oracle 23ai | `VECTOR(n, FLOAT32)` | `VECTOR(*, *)` | no |
| SQL Server 2025 | `VECTOR(n)` | text | no |
| MySQL 9 | `VECTOR(n)` | text | yes, little endian float32 |
| CrateDB | `FLOAT_VECTOR(n)` | text | yes, float array |
| DuckDB | `FLOAT[n]` | `FLOAT[]` | no |

The dimension comes from the field's length, which is what `ValueMetaVector` already documents it as
carrying. Where a database has no unsized form of its vector type, a field without a dimension keeps
today's behaviour and goes to text.

Availability is decided per server, not per dialect. Oracle and SQL Server compare the version the
driver reports; PostgreSQL cannot, because pgvector is an extension and the same server has the type
in one database and not the next, so the dialect asks the driver's type list instead. With no
connection the declared type stands, so DDL generated offline is still native.

MariaDB, Doris and CockroachDB take their parent dialect's rules **without** the vector ones, each
with a comment saying why, and CrateDB claims its own `FLOAT_VECTOR` ahead of the inherited pgvector
type. None of them inherits a column type its server would reject.

### Also fixed

A vector's length is its number of dimensions, not a number of characters. The fallback for a type a
dialect cannot spell took it for a column width, so a four dimension vector became `VARCHAR(4)` —
a column no vector of that size fits in. It now falls back to the widest text the database has, on
every dialect.

### Known limitation

The MySQL and CrateDB bindings turn on the same condition as the column, a known dimension, because
that is all a binding can see: it is chosen from the dialect and the value, never from the column it
is about to write into. Pointing Table Output at a pre-existing table whose column is text, with a
dimension set on the field, therefore writes the binary form into a text column. PostgreSQL is not
affected, because the unspecified type it sends works for both. The Vector type has not been in a
release yet, so no existing pipeline can be in that position.

### Not done, deliberately

**Four candidate dialects are not implemented**: ClickHouse (`Array(Float32)`), Snowflake
(`VECTOR(FLOAT, n)`), SingleStore (`VECTOR(n)`) and BigQuery (`ARRAY<FLOAT64>`). Every dialect here
was verified against a live server, and three of the six turned out to reject the value Hop sends
even though they accept the DDL. Shipping the other four on documentation alone would risk exactly
that: a column that is created and then cannot be written to. Each is a small, self-contained change
for someone who has that database — the write rule is a few lines, and the thing to check is not the
CREATE TABLE but the INSERT, through a JDBC bind rather than a SQL literal.

**Reading a vector column back is only wired up for PostgreSQL.** Elsewhere a vector column still
comes into Hop as text or binary, so on those dialects a vector can be written natively and not read
back as one. The rule is three lines per dialect (`ColumnTypeRules.vectorColumn`), but it needs the
same per-server verification.

**The dimension is not recovered on the read path.** JDBC does not carry it: PostgreSQL reports a
precision of 2147483647 for both a `vector(1536)` and an unsized one, because the dimension lives in
the catalog. So a table-to-table copy of a `vector(1536)` column produces an unsized `vector` on the
target. Closing that needs a dialect-specific catalog query at the point row metadata is built,
which is outside the type-rule API as it stands.

**Nothing populates a dimension automatically.** Hop has no embedding transform, and the pgvector
Upsert keeps its own `embeddingDimensions` option and its own DDL rather than reading the dimension
off the field, so unless a user sets the length by hand the unsized path is what they get. Those two
paths are worth reconciling separately.

### Testing

- `*VectorTypeRulesTest` in each of the six dialects: column type, version or extension gate, binding.
- A `VECTOR` row added to `BaseFieldDefinitionGoldenTest`, so all 47 dialects record what they emit.
The golden files gained 705 lines and deleted none: no existing column definition changed. The row
also makes an accidental inheritance visible as a diff, which is how the CrateDB case was caught.
- Integration tests, one per dialect, each asserting the generated column type *and* that rows
actually land in it:
- `pgvector/main-0007-vector-ddl` — sized, unsized, round trip
- `database/main-0044-vector-ddl` — MySQL, which is what guards the binding
- `database/main-0045-vector-ddl-no-extension` — a PostgreSQL without pgvector gets text, and
still writes
- `mssql/main-0009-vector-ddl` — the version gate against two real servers: 2025 gets VECTOR,
2022 gets varchar
- `cratedb/main-0005-vector-ddl` — FLOAT_VECTOR through the CrateDB driver
- `duckdb/main-0007-vector-ddl` — FLOAT[3]
- `oracle/main-0009-vector-ddl` — sized and unsized

Two of these paid for themselves immediately. The PostgreSQL binding was missing and the pgvector
test found it: the table was created and then every insert was refused with "column is of type
vector but expression is of type character varying". The CrateDB binding was sending a boxed
`Float[]`, which pgjdbc accepts and the CrateDB driver does not; only running against the real
driver showed it.

Docs updated in `data-types.adoc`, which previously stated that no dialect emits a native vector
column type.

**Please** add a meaningful description for your change here

------------------------

Thank you for your contribution! Follow this checklist to help us incorporate your contribution quickly and easily:
- [x] Run `mvn clean install apache-rat:check` to make sure basic checks pass. A more thorough check will be performed on your pull request automatically.
- [x] If you have a group of commits related to the same change, please squash your commits into one and force push your branch using `git rebase -i`.
- [x] Mention the appropriate issue in your description (for example: `addresses #123`), if applicable.

To make clear that you license your contribution under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0)
you have to acknowledge this by using the following check-box.

- [x] I hereby declare this contribution to be licensed under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0)
- [ ] In any other case, please file an [Apache Individual Contributor License Agreement](https://www.apache.org/licenses/icla.pdf).

--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [PR] Fixes #8411 : Emit native vector column types from database dialects … (hop)

Posted by "hansva (via GitHub)" <gi...@apache.org>.

hansva merged PR #8463:
URL: https://github.com/apache/hop/pull/8463


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org