Skip to content

Comparison and type rules disagree with PostgreSQL: quoted fractions beside an integer column run, a DATE column keeps a row for a timestamp literal, mixed DATE/TIMESTAMP BETWEEN and text/boolean GREATEST are refused, text + text concatenates #415

Open

Description

@fupelaqu

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 BETWEEN take 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 DATE column).

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 of Math.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 + s already is. The remedy is CONCAT(…).
  • 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 NULLIF refusal 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: INT when the literal fits, BIGINT past the INT range, 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

  1. fupelaqu commented on Nov 12, 2026

    @fupelaqu
    ContributorAuthor

    Progress: item 1a is settled

    Item 1a, a quoted fraction beside a bare INT column (WHERE n = '1.5' and its IN, BETWEEN and > 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:

    statement query sent
    n = '1.5' a term on "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 a DOUBLE column keeps the literal (x = '1.5' stays a term on "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:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions