Six time functions emit a statement-sequence Painless — broken in predicates (silent zero rows for IN), and LAST_DAY fails even in SELECT

[fupelaqu]

WEEKDAY(d) (and its DAYOFWEEK spelling) is correct in a SELECT list and wrong in every predicate. The equality and IN forms are a SILENT wrong answer; the ordering forms fail loudly.

Measured on real Elasticsearch 8.18

Index of seven dates, 2025-01-06 (a Monday, weekday 0) through 2025-01-12 (a Sunday, weekday 6):

SELECT id, WEEKDAY(d) AS w FROM weekday_probe    OK    6→0 7→1 8→2 9→3 10→4 11→5 12→6   correct
WHERE WEEKDAY(d) = 0                             OK    ZERO rows          (id 6 is weekday 0)
WHERE WEEKDAY(d) IN (5, 6)                       OK    ZERO rows          (ids 11, 12)
WHERE WEEKDAY(d) NOT IN (0,1,2,3,4)              OK    ALL SEVEN rows     (should be 11, 12)
WHERE WEEKDAY(d) > 4                             FAIL  search_phase_execution_exception, all shards failed
WHERE WEEKDAY(d) BETWEEN 1 AND 2                 FAIL  search_phase_execution_exception, all shards failed
WHERE YEAR(d) = 2025                             OK    all seven          correct (control)

=, IN and NOT IN return HTTP 200 with the wrong rows — the #205 / #253 / #328 family. NOT IN returning everything while IN returns nothing is the signature of a predicate that matched no document at all.

What is established

1. WEEKDAY is the only function in the catalogue whose Painless is a STATEMENT SEQUENCE rather than an expression. A sweep of every accepted spelling in SQLKeywords.functionTokens across f(d), f(d, 1), f(d, 'x') and f(d, MONTH) in a WHERE: 2 broken, 94 clean — and the 2 are WEEKDAY and DAYOFWEEK, the same function.

WHERE WEEKDAY(d) = 6   painless:
  def left = def arg0 = (doc['d'].size() == 0 ? null : doc['d'].value);
             (arg0 == null) ? null : (arg0.get(ChronoField.DAY_OF_WEEK) + 6) % 7;
             left == null ? false : (left == 6)

def left = def arg0 = …; is not valid Painless.

2. Why it is alone. DayOfWeek is the only Extract that puts a NULLABLE value in args (List(identifier)); its siblings pass the ChronoField, which is never null. FunctionN.painless emits the def arg0 = …; prelude precisely when an arg is nullable. That composes as a whole script — which is why SELECT WEEKDAY(d) works, a script_field being a whole script — but a predicate wraps its operand as def left = <…>.

🔴 What is RULED OUT — the obvious fix does not work

Making DayOfWeek.painless emit a single expression instead of a statement sequence — inlining the operand and dropping the prelude — changes none of the measured behaviour above. Same zero rows for = and IN, same loud failure for >. Verified by executing the same probe against a build carrying that change.

So the malformed Painless is real but is not the whole cause, and possibly not the cause at all for the = / IN half. Something upstream of the emission is already producing a predicate that cannot match: the = / IN / NOT IN behaviour is consistent with the comparison being pushed down as a term / terms query on the RAW d field (a date) against integer values, with the function dropped — but YEAR(d) = 2025 is pushed down correctly, so it is not a general "functions are ignored in equality" defect either.

The next step is to dump the generated Elasticsearch query JSON for these five statements and compare WEEKDAY against YEAR. That needs the bridge module; the template/es{N} copy mechanism (copyBridge) makes an ad-hoc probe there awkward, which is where this investigation stopped.

Scope

• Affects WEEKDAY / DAYOFWEEK in WHERE and HAVING, in all of =, IN, NOT IN, >, BETWEEN.
• Does NOT affect the SELECT list, which is correct on all four ES majors.
• Not a regression: present on main and before. YEAR, MONTH, DAY, HOUR are all correct in predicates.
• The published documentation example WEEKDAY(event_date) IN (6, 7) therefore renders correctly but cannot match.

Provenance

Surfaced by an independent review of #366 (the IN double-render fix), which cited WEEKDAY(...) IN (6, 7) as one of the renders that PR corrects. The review reported the mechanism as "compares a ZonedDateTime to an integer"; that reading is not supported — the (… + 6) % 7 arithmetic is present in the emitted binding. The measurements above replace it.

---

⚠️ CORRECTION — the scope is SIX functions, not one, and my sweep was wrong

The filing above says a sweep of every accepted spelling found "2 broken, 94 clean", and concluded the defect was WEEKDAY-only. That sweep was invalid. It generated WHERE f(d) = 1, so every function returning a DATE or a VARCHAR was type-rejected — and the probe counted a rejection as not applicable rather than not tested. Five of the six affected functions were silently skipped by their own type.

The real population is every function that puts the nullable identifier in args (its siblings pass the ChronoField, which is never null). There are six, all in function/time/package.scala:

class	line	spellings
DayOfWeek	373	WEEKDAY, DAYOFWEEK
LastDayOfMonth	420	LAST_DAY, LASTDAY
DateParse	723	DATE_PARSE
DateFormat	805	DATE_FORMAT
DateTimeParse	930	DATETIME_PARSE
DateTimeFormat	1000	DATETIME_FORMAT

Re-probed with type-appropriate comparisons, all emit the same statement sequence:

BROKEN  WHERE WEEKDAY(d) = 0
BROKEN  WHERE DATE_FORMAT(d, 'yyyy') = '2025'
BROKEN  WHERE DATETIME_FORMAT(d, 'yyyy') = '2025'
BROKEN  WHERE DATE_FORMAT(d, 'yyyy') IN ('2025')
BROKEN  WHERE LAST_DAY(d) BETWEEN '2025-01-01'::DATE AND '2025-12-31'::DATE
ok      WHERE YEAR(d) = 2025                           (control: passes the ChronoField)

Executed on real Elasticsearch 8.18

SELECT id, DATE_FORMAT(d,'yyyy') AS y            OK    2025 for all seven      correct
WHERE DATE_FORMAT(d, 'yyyy') = '2025'            FAIL  search_phase_execution_exception
WHERE DATE_FORMAT(d, 'yyyy') IN ('2025')         OK    ZERO rows   ← SILENT, should be all seven
SELECT id, LAST_DAY(d) AS l                      FAIL  search_phase_execution_exception  ← even in SELECT
WHERE LAST_DAY(d) BETWEEN … AND …                rejected: "does not contain a valid search request"
WHERE YEAR(d) = 2025                             OK    all seven               correct (control)

So the damage varies by function AND by venue:

• WEEKDAY — correct in SELECT; silent zero rows for = / IN; loud for > / BETWEEN.
• DATE_FORMAT / DATETIME_FORMAT — correct in SELECT; silent zero rows for IN; loud for =.
• LAST_DAY — fails even in the SELECT list, which is worse than WEEKDAY.

DATE_PARSE / DATETIME_PARSE share the structure; I could not build a comparison for them because a ::DATE cast on the right of a comparison is itself rejected (issue #284's family), so they are unverified rather than clean.

The ruled-out fix still stands, and now matters more

Making DayOfWeek's emission a single expression changed none of the measured behaviour. Since six functions share the structure, a per-function patch was the wrong shape anyway — whatever the real cause is, it is in how a predicate consumes a function whose Painless is a statement sequence, or upstream of that.

Meta

🔴 Worth recording beyond this issue: a type mismatch is not a clean result. A sweep that generates one comparison shape and skips whatever does not typecheck will report a clean bill of health for exactly the functions whose return type it did not think about. Same failure as a corpus differential that never contained the input — the instrument answered confidently about a population it never reached.

---

Provenance checked against v0.22.0 — NOT a regression of 3acc0944

The filing said "not a regression: present on main and before". That was asserted, not measured. It has now been measured, by building a parser from the v0.22.0 tag (675faee7), which does not contain 3acc0944 (fix(BIDC-8): a WHERE predicate with a function now compiles…).

v0.22.0, before the suspect commit:

WHERE WEEKDAY(d) = 0
  def left = (def e0 = (doc['d']…); e0 != null ? e0def arg0 = (doc['d']…);
             (arg0 == null) ? null : (arg0.get(ChronoField.DAY_OF_WEEK) + 6) % 7 : null); …

WHERE DATE_FORMAT(d, 'yyyy') = '2025'      same shape
WHERE YEAR(d) = 2025                       clean — identical to today (control)
SELECT LAST_DAY(d)                         e0def arg0 = … arg0.withDayOfMonth(arg0.lengthOfMonth())
SELECT EPOCHDAY(d)                         .get(ChronoField.EPOCH_DAY) — byte-identical to today

So at v0.22.0 the same predicates were already broken, and broken worse: note e0def arg0, a missing separator where two fragments were concatenated, on top of the statement sequence.

3acc0944 therefore improved this defect rather than causing it. It removed the e0 double-wrap and made a WHERE predicate carrying a function work for the 94 functions whose emission is a single expression — its own release note says such a predicate "previously failed the whole query with the compile error". What it did not do is handle the six functions whose emission is a statement sequence.

#367 is the incomplete half of 3acc0944, not a regression of it. The distinction matters for the fix: there is no earlier behaviour to restore, so the fix has to make the statement-sequence case work for the first time.

Two further results from the same control, both of which also predate 3acc0944:

• LAST_DAY has never worked, in any venue, on any release checked — arg0.lengthOfMonth() is called on a ZonedDateTime, which has no such method (it is on LocalDate). Now filed separately.
• EPOCHDAY's emission is byte-identical at v0.22.0 — .get(ChronoField.EPOCH_DAY), where get() returns an int and EPOCH_DAY requires getLong(). Also filed separately.