Repository navigation
[BUG] PostgreSQL / pgvector: a distance operator (<->, <#>, <=>) followed by a comparison does not parse #2851
Description
In PostgreSQL the <-> family (pgvector, pg_trgm, PostGIS) are ordinary operators that bind tighter than <, > and =. So this is valid and common with pgvector:
SELECT * FROM items WHERE embedding <-> '[1,2,3]' < 5
SELECT id FROM items WHERE embedding <=> $1 < 0.3 ORDER BY embedding <=> $1 LIMIT 10JSqlParser: current master (90ac3a7).
CCJSqlParserUtil.parse("SELECT a <-> b < 1 FROM t");
// ParseException: Encountered: "<" / "<", at line 1, column 16| Statement | Result |
|---|---|
SELECT embedding <-> '[1,2,3]' AS distance FROM items |
parses |
SELECT * FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5 |
parses |
SELECT a FROM t WHERE e && f |
parses |
SELECT * FROM items WHERE embedding <-> '[1,2,3]' < 5 |
Encountered: "<" |
SELECT a <-> b < 1 FROM t |
Encountered: "<" |
My reading of the grammar: <-> and <#> are parsed with the comparison operators (GeometryDistance next to JsonOperator, Matches, ... in the comparison production), so a second comparison after it has no left side to attach to. In PostgreSQL they are "any other operator" and rank above the comparison operators.
Found with a list of statements I ran against master.
Activity
fix(parser): parse vector distances before relational comparisons (JS…