Repository navigation
Description
PR #412 adds one comparison rule: a quoted literal is read as the number or date it spells, and DATE / TIMESTAMP compare on their declared types. These shapes still answer differently from PostgreSQL 16, on main (f63222d), on #412's head (0687815), or on both. Items 1, 3 and 5 answer with HTTP 200 where PostgreSQL refuses the statement (items 1 and 5) or returns fewer rows (item 3).
Fixture
CREATE TABLE t (id KEYWORD, g KEYWORD, s KEYWORD, s2 KEYWORD, n INT, x DOUBLE, y DOUBLE, d DATE, d2 DATE, ts TIMESTAMP, b BOOLEAN, PRIMARY KEY (id)). "—" means the field is absent from the document. PostgreSQL 16.14 holds the same rows, with an absent field stored as NULL (ts is timestamp).
| id | g | s | s2 | n | x | y | d | d2 | ts | b |
|---|---|---|---|---|---|---|---|---|---|---|
| r1 | g1 | 1 | a | 3 | 0.25 | 1.55 | 2024-01-31 | 2024-02-01 | 2024-01-31T10:30:00Z | true |
| r2 | g1 | 5 | z | 5 | 1.5 | 1.45 | 2024-03-01 | 2024-02-28 | 2024-03-01T06:00:00Z | false |
| r3 | g2 | 12 | — | -2 | — | — | 2023-12-25 | — | 2023-12-25T23:59:59Z | — |
| r4 | g3 | — | m | — | 2.0 | 2.25 | — | 2024-01-31 | — | true |
| r5 | g3 | 3 | 3 | 10 | -1.25 | -1.55 | 2024-02-29 | 2024-02-29 | 2024-02-29T00:00:00Z | false |
All statements were executed on Elasticsearch 8.18.3 through the gateway.
1. A quoted fraction, or an out-of-range number, beside a bare integer column runs
| statement | main |
#412 | PostgreSQL 16 |
|---|---|---|---|
SELECT id FROM t WHERE n = '1.5' |
0 rows | 0 rows | ERROR invalid input syntax for type integer: "1.5" |
SELECT id FROM t WHERE n IN ('1','1.5') |
0 rows | 0 rows | same error |
SELECT id FROM t WHERE n BETWEEN '1' AND '1.5' |
0 rows | 0 rows | same error |
SELECT id FROM t WHERE n > '1.5' |
r1, r2, r5 | r1, r2, r5 | same error |
SELECT id FROM t WHERE n = '1.0' |
0 rows | 0 rows | ERROR invalid input syntax for type integer: "1.0" |
SELECT id FROM t WHERE n = '99999999999' |
0 rows | 0 rows | ERROR value "99999999999" is out of range for type integer |
When the same literal is compared with an expression of the column, both builds refuse it by name: WHERE COALESCE(n, 0) = '1.5' gives Type mismatch: 'BIGINT' is not compatible with 'VARCHAR' in expression: COALESCE(n, 0) = '1.5'. main refuses it at parse, #412 once the mapping is read.
Mechanism: a bare-column comparison is sent as a query on the mapping, with the literal exactly as written: {"term":{"n":{"value":"1.5"}}}, {"range":{"n":{"gt":"1.5"}}}. Elasticsearch then reads 1.5 against the integer field itself.
- Expected: refused by name, as PostgreSQL does and as the scripted path already does. A whole number outside the column type's range is refused too.
- Fix direction: apply the comparison rule's whole-number and range check on the bare-column (query DSL) path as well.
2. BETWEEN with one DATE bound and one TIMESTAMP bound is refused over a bare column
| statement | main |
#412 | PostgreSQL 16 |
|---|---|---|---|
SELECT id FROM t WHERE ts BETWEEN CAST('2024-01-01' AS DATE) AND CAST('2024-02-01T00:00:00Z' AS TIMESTAMP) |
refused at parse: Type mismatch: output 'DATE' is not compatible with input 'TIMESTAMP' |
refused, same message, once the mapping is read | r1 |
SELECT id FROM t WHERE d BETWEEN CAST('2024-01-01' AS DATE) AND CAST('2024-02-01T00:00:00Z' AS TIMESTAMP) |
same | same | r1 |
A DATE and a TIMESTAMP already compare elsewhere: WHERE ts > d runs on both builds and matches PostgreSQL (r1, r2, r3).
- Expected: accepted. Each bound is compared on its declared type.
- Fix direction: let
BETWEENtake bounds of the two temporal types, as>=/<=written separately do.
3. A bare DATE column compared with a timestamp literal keeps a row PostgreSQL drops
| statement | main |
#412 | PostgreSQL 16 |
|---|---|---|---|
SELECT id FROM t WHERE d < '2024-01-31T10:00:00Z' |
r1, r3 | r1, r3 | r3 |
Mechanism: {"range":{"d":{"lt":"2024-01-31T10:00:00Z"}}}. Elasticsearch compares instants, 2024-01-31T00:00Z < 10:00Z, so the row dated 2024-01-31 is kept. PostgreSQL reads the literal as a date (2024-01-31) and drops that row. On #412, the scripted rendering of the same comparison already reads the literal as a date (LocalDate.parse("2024-01-31")), so the query-DSL path and the scripted path disagree there.
- Expected: r3 only. Today the extra row comes back with HTTP 200 and no error.
- Fix direction: in the range query, render the literal in the column's declared type (its date part for a
DATEcolumn).
4. GREATEST / LEAST over text or booleans: refused by name; PostgreSQL accepts them
| statement | main |
#412 | PostgreSQL 16 |
|---|---|---|---|
SELECT id, GREATEST(s, 'b') AS v FROM t |
refused at parse: GREATEST requires numeric arguments but got VARCHAR for 'b' |
refused: GREATEST requires numeric arguments, or date and timestamp arguments, but got KEYWORD for s |
b, b, b, b, b |
SELECT id, LEAST(s, s2) AS v FROM t |
accepted, fails in Elasticsearch: cannot implicitly cast def [java.lang.String] to double |
refused (… got KEYWORD for s) |
1, 5, 12, m, 3 |
SELECT id, GREATEST(b, FALSE) AS v FROM t |
refused at parse (… got BOOLEAN for false) |
refused (… got BOOLEAN for b) |
true, false, false, true, false |
SELECT id, GREATEST(s, '2') AS v FROM t |
refused at parse | refused | 2, 5, 2, 2, 3 (text order: '12' < '2') |
SELECT id, GREATEST(id, s) AS v FROM t |
fails in Elasticsearch (cannot implicitly cast def [java.lang.String] to double) |
refused | r1, r2, r3, r4, r5 |
SELECT id FROM t WHERE LEAST(s, s2) = '1' |
refused at parse | refused | r1 |
PR #412 turns the Elasticsearch failure of the column-only forms into a refusal by name. NULL arguments are skipped (r3's LEAST(s, s2) = 12, r4's = m). PostgreSQL ran with collation en_US.utf8, and for these values its order is the code-point order.
- Expected: text compared as text, booleans with
false < true, NULLs skipped. - Fix direction: render a
compareTo-based maximum / minimum for string and boolean arguments instead ofMath.max/Math.min.
5. + over text answers a concatenation, and - over text fails in Elasticsearch
| statement | main = #412 |
PostgreSQL 16 |
|---|---|---|
SELECT id, id + 'a' AS v FROM t |
r1a, r2a, r3a, r4a, r5a | ERROR operator does not exist: text + unknown |
SELECT id, id + NULL AS v FROM t |
r1null, r2null, r3null, r4null, r5null (the text null) |
same error |
SELECT id, UPPER(s) + s AS v FROM t |
11, 55, 1212, NULL, 33 | ERROR operator does not exist: text + text |
SELECT id, COALESCE(s, 'z') + NULL AS v FROM t |
1null, 5null, 12null, znull, 3null | ERROR operator does not exist: text + unknown |
SELECT id, id - 'a' AS v FROM t |
400, all shards failed |
ERROR operator does not exist: text - unknown |
Painless's + concatenates strings, and the type check lets two text operands through. Whatever the rule, + NULL must not give the text null. Since #412, n + s (INT + KEYWORD) is refused by name, and an operator after a CASE applies to every branch: CASE WHEN n > 0 THEN s ELSE 'z' END + 'a' now concatenates on every row, where main did it on the ELSE rows only.
- Expected: refused by name, as PostgreSQL does and as
n + salready is. The remedy isCONCAT(…). - Fix direction: refuse
+and-when both operands are text, in the type check after the mapping is read.
6. A refusal names ABS(n) as NUMERIC in arithmetic and as INT in a comparison
Since #412, ABS is typed by its argument, and a comparison's refusal says so. Arithmetic and NULLIF refusals over the same ABS(n) still name NUMERIC, the function's declared output. Every statement below is refused, as PostgreSQL refuses it; only the message differs.
| statement | #412's refusal |
|---|---|
SELECT id FROM t WHERE ABS(n) = 'a' |
Type mismatch: 'INT' is not compatible with 'VARCHAR' in expression: ABS(n) = 'a' |
SELECT id, ABS(n) + 'a' AS v FROM t |
Type mismatch: output 'NUMERIC' is not compatible with input 'VARCHAR' |
SELECT id, NULLIF(ABS(n), 'a') AS v FROM t |
NULLIF(ABS(n), 'a') compares NUMERIC with VARCHAR: NULLIF requires two arguments of comparable types, so cast one of them |
- Expected: one type in every message, the argument's.
- Fix direction: build the arithmetic and
NULLIFrefusal messages from the reported type, as the comparison rule does.
7. A whole-number literal is typed BIGINT
2 is a BIGINT in this engine, where PostgreSQL types a literal that fits 32 bits as integer. So an expression over an INT column and a literal reports BIGINT: GREATEST(n, 2) is BIGINT on #412 (its values are whole numbers), where PostgreSQL's pg_typeof(GREATEST(n, 2)) is integer. Whatever declares a column from the reported type follows: CREATE TABLE … AS SELECT, JDBC / Arrow metadata.
- Expected:
INTwhen the literal fits,BIGINTpast theINTrange, as PostgreSQL does. - Fix direction: type a whole-number literal by its value, and let the operators' common-type rule widen it where needed.
Evidence level
RUN on Elasticsearch 8.18.3 for every row, on both builds. PostgreSQL 16.14 answers measured on the same rows. Item 6 was read off the refusal messages, item 7 off the resolved statement and PostgreSQL's pg_typeof.
Activity
fupelaqu commentedon Nov 12, 2026
Progress: item 1a is settled
Item 1a, a quoted fraction beside a bare
INTcolumn (WHERE n = '1.5'and itsIN,BETWEENand>forms), is settled. It will not become a refusal: on this point the engine follows DuckDB rather than PostgreSQL.Since #455 (engine
0.25.0), a quoted number beside a column declared with a whole-number type is cast to that type as DuckDB 1.5.5 casts it ('1.5'::INTEGER= 2, half away from zero), and the query sent to Elasticsearch carries that whole number:n = '1.5'termon"2"n > '1.5'{"range":{"n":{"gt":"2"}}}n IN ('1.5', '3'){"terms":{"n":["2","3"]}}n BETWEEN '1.5' AND '2.4'{"range":{"n":{"gte":"2","lte":"2"}}}A whole-number text (
'3') is sent as written, and aDOUBLEcolumn keeps the literal (x = '1.5'stays atermon"1.5"). The query-builder tests pin these shapes for every Elasticsearch major, and an Elasticsearch 8 run checks the rows against DuckDB's answers.On the fixture above, the four
'1.5'statements of item 1 return the rows reported (n > '1.5': r1, r2, r5; the others: 0 rows). Those are now the expected answers.Still open in this issue:
n = '99999999999') and 6: not re-measured yet;BOOLEANbeside whole numbers and quoted arguments inGREATEST/LEASTare answered since feat(sql): quoted arguments and implicit comparisons read as DuckDB does (SCHEMA-5b) #460.