ES 6.8: a raw date doc-value reaches Painless un-normalised — DATE_FORMAT/DATETIME_FORMAT in every venue, and any temporal expression compared with a date column #371

Activity

1. fupelaqu commented on Sep 19, 2026

   

   fupelaqu

   on Sep 19, 2026

   ContributorAuthor

   More actions

   ⚠️ Correction — half (a) is NOT simple, and the recommendation changes

   I claimed above that (a) is "one line at two sites and MEASURED to work". That claim came from testing
   the ONE shape the defect report names — a bare date column — and it does not survive enumerating the
   other receivers that reach the same call site. Measured, on the emission of every documented shape:

   shape	receiver the operand holds	one-line normalisation
   DATE_FORMAT(d, 'yyyy')	raw doc-value	fixes it
   DATETIME_FORMAT(ts, 'yyyy')	raw doc-value	fixes it
   DATE_FORMAT(DATE_TRUNC(d, MONTH), 'yyyy')	LocalDate	breaks it
   DATE_FORMAT(DATE_ADD(d, INTERVAL 1 DAY), 'yyyy')	LocalDate	breaks it
   DATE_FORMAT(CAST(d AS DATE), 'yyyy')	LocalDate	breaks it
   DATE_FORMAT(CURRENT_DATE, 'yyyy')	LocalDate	breaks it
   DATE_FORMAT(LAST_DAY(d), 'yyyy')	LocalDate	breaks it

   java.time.LocalDate has no toInstant(), so appending the UTC normalisation unconditionally trades
   one broken receiver for five — the same trap #368 recorded, hit again by testing only the shape in the
   report.

   The actual mechanism, found by instrumenting the call site

   The narrowing is already REQUESTED for the bare-column case and never reaches the emission.
   Instrumenting DateFormat.toPainlessCall for DATE_FORMAT(d, 'yyyy') prints:

   arg=[param1]  identifier.painlessMethods=[.toInstant().atZone(ZoneId.of('Z')).toLocalDate()]
   

   while the declaration the script actually carries is

   def param1 = (doc['d'].size() == 0 ? null : doc['d'].value);
   

   — no methods at all. So the operand's own chain asks for the normalisation, and the parameter
   declaration is rendered without it. PainlessParam identity is the doc-value STRING, so the object
   whose method list is mutated is not necessarily the object the prologue renders.

   That is the same parameter-identity defect as #370, in a different disguise: one fix, in how a
   parameter's declaration is rendered, addresses both. Appending a second normalisation at the call site
   papers over it and, as measured above, breaks five working shapes.

   Revised recommendation

   Do NOT take the one-liner. Either fix parameter identity (with #370, and re-measure this family
   against it), or document the ES 6.8 limitation for DATE_FORMAT / DATETIME_FORMAT on the
   function pages and the version-support matrix, as originally directed.

   Nothing for this was merged: the change was written, measured, refuted and reverted; #372 carries none
   of it. The 6.8 rows stay assume-skipped in PredicateFunctionResultSpec with the verbatim
   Elasticsearch errors.
2. fupelaqu

   added a commit that references this issue on Sep 19, 2026

   docs(sql): record the Elasticsearch 6.8 limit of DATE_FORMAT / DATETI…

   ...

   bc96f7b
3. fupelaqu commented on Oct 6, 2026

   

   fupelaqu

   on Oct 6, 2026

   ContributorAuthor

   More actions

   Re-measured on Elasticsearch 6.8.23: main (f63222d) and #412's head (0687815)

   On 6.8, #412 makes a computed date compared with a date column run, which is half (b) of this issue. Half (a) and three neighbouring shapes still fail. Everything was executed through the gateway. On 8.18.3, every statement below answers correctly on both builds; that is the control.

   Runs on 6.8 with #412, fails on main

   On an index mapped by hand, as in PredicateFunctionResultSpec: d and ts are date, seven documents dated 2025-01-06 … 12, and ts is a year later for d6.

   statement	main	#412	expected (the spec's sets)
   WHERE LAST_DAY(d) = d	Cannot cast org.elasticsearch.script.JodaCompatibleZonedDateTime to java.time.chrono.ChronoZonedDateTime	none	none
   WHERE LAST_DAY(d) > d	same	all 7	all 7
   WHERE DATE_TRUNC(d, MONTH) < d	same	all 7	all 7
   WHERE DATE_ADD(d, INTERVAL 1 DAY) = d	same	none	none

   On a table declared through elasticsql (d, d2 DATE, ts TIMESTAMP; five rows), compared with PostgreSQL 16.14:

   statement	main	#412	PostgreSQL 16
   WHERE CURRENT_DATE > d	Cannot cast …JodaCompatibleZonedDateTime to java.time.chrono.ChronoZonedDateTime	r1 r2 r3 r5	r1 r2 r3 r5
   WHERE CAST(ts AS DATE) = d	same	r1 r2 r3 r5	r1 r2 r3 r5
   WHERE DATE_ADD(d, INTERVAL 1 DAY) = d2	same	r1	r1
   WHERE COALESCE(d, d2) > CAST('2024-01-01' AS DATE)	Cannot cast java.time.ZonedDateTime to org.elasticsearch.script.JodaCompatibleZonedDateTime	r1 r2 r4 r5	r1 r2 r4 r5
   WHERE d2 = d + 1	Cannot apply [+] operation to types [org.elasticsearch.script.JodaCompatibleZonedDateTime] and [java.lang.Integer]	r1	r1
   WHERE LAST_DAY(d) = d	cast error as above	r1 r5	r1 r5
   WHERE DATE_TRUNC(d, MONTH) < d	cast error as above	r1 r3 r5	r1 r3 r5

   Still fails on 6.8, on both builds

   statement	Elasticsearch's error
   WHERE DATE_FORMAT(d, 'yyyy') = '2025', its IN form, DATETIME_FORMAT, the projection SELECT DATE_FORMAT(d, 'yyyy-MM-dd'), and DATE_FORMAT(d, 'yyyy') = DATE_FORMAT(ts, 'yyyy')	Cannot cast org.elasticsearch.script.JodaCompatibleZonedDateTime to java.time.temporal.TemporalAccessor
   WHERE CAST(d AS TIME) = CAST('00:00:00' AS TIME), its <> and + INTERVAL 1 HOUR > forms	dynamic method [org.elasticsearch.script.JodaCompatibleZonedDateTime, toLocalTime/0] not found
   WHERE d2 > d, WHERE ts > d, WHERE d2 > DATE_TRUNC(d, MONTH)	400 failed to parse date field [d] with format [strict_date_optional_time||epoch_millis]: Parse failure at index [0] of [d]
   SELECT id, CAST(TRUE AS INT) AS v FROM t	compile error Extraneous conditional statement

   • A date column compared with another bare date column fails for a different reason than this issue. The ES 6 bridge renders the comparison as a range query whose bound is the other column's name: {"range":{"d2":{"gt":"d||"}}}, and {"gt":"d||/M"} for DATE_TRUNC(d, MONTH). Elasticsearch then parses d as a date. The ES 7 / 8 / 9 bridges render a script that compares the two doc values, and on 8.18.3 it gives PostgreSQL's rows: r1; r1 r5; r1 r2 r3. With fix(sql): date arithmetic follows the SQL engines; type checks wait for the column types #412, WHERE d2 > d + 0 runs on 6.8 (r1).
   • CAST(TRUE AS INT) renders (true ? 1 : 0), a constant conditional that Painless 6.8 refuses to compile. CAST(b AS INT) over the column runs (1, 0, NULL, 1, 0).

   The five esMajor >= 7 cancels in PredicateFunctionResultSpec (#412's head)

   test (line)	statements	6.8 with #412
   DATE_FORMAT in a predicate (230)	DATE_FORMAT(d, …) = / IN …	still fails
   DATETIME_FORMAT in a predicate (241)	DATETIME_FORMAT(d, …) = …	still fails
   a temporal function compared with a date column (303)	LAST_DAY(d) = d, LAST_DAY(d) > d, DATE_TRUNC(d, MONTH) < d, DATE_ADD(d, INTERVAL 1 DAY) = d, DATE_TRUNC(d, QUARTER) < d	all five run and give the asserted sets
   a TIME receiver, not narrowed twice (333)	CAST(d AS TIME) …	still fails
   two temporal chains (431)	LAST_DAY(d) = LAST_DAY(ts)	runs and gives the asserted set (all but d6), on main too

   Guards 303 and 431 no longer hold. These rows were measured through the gateway (GatewayApi.run). The spec's selected goes through the client venue, so re-enabling the two tests confirms them there.