Wrong results with no error: ES 8 answers 0.0 for MIN / MAX / AVG of a column a whole group lacks, CREATE TABLE … AS SELECT … GROUP BY stores one empty document per source document, and values come back in the class Elasticsearch's JSON gives them (a date per group as epoch milliseconds) #414

Activity

1. fupelaqu commented on Oct 6, 2026

   

   fupelaqu

   on Oct 6, 2026

   ContributorAuthor

   More actions

   A per-group GREATEST / LEAST answers NULL when one of its aggregates is empty; DuckDB and PostgreSQL skip the NULL

   Another wrong result with no error, in the family of #414 (item 1 has the same fixture shape: a column a whole group lacks).

   Measured on core 0.24.0-SNAPSHOT (core main 7fe1d66, #412 merged), through the gateway, on Elasticsearch 8.18.3 and 7.17.29. DuckDB 1.5.5 and PostgreSQL 16.14 ran on the same rows.

   Fixture

   CREATE TABLE zi_gl (id INT, station_id INT, dt DATE, dt2 DATE, n INT, n2 INT, PRIMARY KEY (id)). "—" means the field is absent from the document; DuckDB and PostgreSQL store it as NULL.

   id	station_id	dt	dt2	n	n2
   1	1	2024-01-11	—	5	—
   2	2	2024-02-05	2024-02-03	3	7
   3	3	—	—	—	—

   Station 1 has no dt2 and no n2.

   Measured

   statement	station	ES 8.18.3	ES 7.17.29	DuckDB 1.5.5	PostgreSQL 16
   SELECT station_id, MAX(dt) AS a, MAX(dt2) AS b, GREATEST(MAX(dt), MAX(dt2)) AS g, LEAST(MAX(dt), MAX(dt2)) AS l FROM zi_gl GROUP BY station_id	1	g, l: NULL, NULL	NULL, NULL	2024-01-11, 2024-01-11	2024-01-11, 2024-01-11
   same	2	2024-02-05, 2024-02-03 (as epoch milliseconds, #414 item 4)	same	2024-02-05, 2024-02-03	same
   SELECT station_id, MAX(n) AS a, MAX(n2) AS b, GREATEST(MAX(n), MAX(n2)) AS g, LEAST(MAX(n), MAX(n2)) AS l FROM zi_gl GROUP BY station_id	1	NULL, NULL	NULL, NULL	5, 5	5, 5
   same	2	7, 3	7, 3	7, 3	7, 3
   both statements	3	NULL, NULL	NULL, NULL	NULL, NULL	NULL, NULL
   row level: SELECT id, GREATEST(dt, dt2) AS g, LEAST(dt, dt2) AS l, GREATEST(n, n2) AS gn, LEAST(n, n2) AS ln FROM zi_gl	id 1	2024-01-11, 2024-01-11, 5, 5	same	same	same

   The row-level form is right; only the per-group form loses the value. On ES 8, b (the empty MAX) reads 0.0 for station 1 (#414 item 1) while g reads NULL. On ES 7, b is NULL.

   A materialized view gives the same answer. With SOFTNETWORK-APP/softclient4es-extensions#72, this view stores no g and no l for one_null, and 7 / 3 (or 2024-02-05 / 2024-02-03 for the date form) for both, on Elasticsearch 8.18.3 and 7.17.29:

   CREATE OR REPLACE MATERIALIZED VIEW v REFRESH EVERY 8 SECONDS WITH (delay = '1s', user_latency = '1s') AS
   SELECT s.label, MAX(m.n) AS a, MAX(m.n2) AS b, GREATEST(MAX(m.n), MAX(m.n2)) AS g, LEAST(MAX(m.n), MAX(m.n2)) AS l
   FROM zi_gl AS m JOIN zi_gl_st AS s ON m.station_id = s.id GROUP BY s.label;

   (zi_gl_st: 1 one_null, 2 both, 3 none.) Extensions main does not show the case: its views store no per-group calculation at all.

   Cause

   Core sends the per-group call as a bucket_script with no NULL handling and no gap_policy. From the request it sent on Elasticsearch 8.18.3:

   • numbers: "g": {"bucket_script": {"buckets_path": {"a": "a", "b": "b"}, "script": "Math.max(params.a, params.b)"}};
   • dates: "script": "Math.max(Math.floor(params.a / 86400000.0) * 86400000.0, Math.floor(params.b / 86400000.0) * 86400000.0)".

   Elasticsearch's default gap policy, skip, skips the script for a bucket where one of its buckets_path values is missing. So the cell is absent, and reads NULL. The row-level form follows the rule GREATEST / LEAST were built with: a NULL argument is skipped, and the result is NULL only when every argument is NULL.

   • Expected: 2024-01-11 and 5 for station 1, as DuckDB and PostgreSQL answer; NULL only for station 3.
   • Fix direction: let the per-group script see an empty operand instead of skipping the bucket (a gap policy that keeps the bucket, or the operands' value counts), and drop NULL operands in the script as the row-level form does.

   Evidence level

   The SOFTNETWORK-APP/softclient4es-extensions#72 runs were taken before its final revision, which does not touch these paths. RUN through the gateway on Elasticsearch 8.18.3 and 7.17.29 (core's search, and the view with and without SOFTNETWORK-APP/softclient4es-extensions#72). DuckDB 1.5.5 and PostgreSQL 16.14 answers measured. The bucket_script bodies are the requests core logged.
2. fupelaqu commented on Oct 7, 2026

   

   fupelaqu

   on Oct 7, 2026

   ContributorAuthor

   More actions

   In arrow's set operations

   Item 4 (a per-group date comes back as epoch milliseconds) also breaks arrow's set operations. SELECT MIN(d) AS k FROM t UNION SELECT ts AS k FROM t fails in arrow with Conversion Error: Unimplemented type for cast (DOUBLE -> TIMESTAMP_MS), because the MIN(d) leg reaches DuckDB as a DOUBLE. DuckDB 1.5.5 answers TIMESTAMP over the same data.

   Measured through softclient4es-arrow main (264eac3) with core 0.24.0-SNAPSHOT (#412), on Elasticsearch 8.18.3.