A literal beside a GROUP BY is rejected with a message saying the SQL is invalid. It is not — a constant is row-invariant, so standard SQL allows it ungrouped in a grouped SELECT list, and every mainstream engine accepts it.
Reproduction
SELECT category, 2 AS flag FROM t GROUP BY category
SELECT SUM(1) AS COL, 2 AS COL2 FROM t GROUP BY 2
Both returned Non-aggregated fields 2 AS COL2 cannot be selected when GROUP BY is present.
Mechanism
Not a grammar gap — SELECT 2 AS COL2 FROM t parses on its own. The non-aggregated-field validator has no arm for a row-invariant literal, so the field falls into invalidFields.
Two sub-cases, and they fail for different reasons:
- (A) the constant IS the group key (
... GROUP BY 2) — rejected by the validator alone.
- (B) the constant is an extra ungrouped column (
SELECT category, 2 AS flag ... GROUP BY category) — accepting it was not enough. An aggregation response carries no hits, so the script_fields entry a constant is emitted as is never fetched under "size": 0, and the column came back null on every row.
Impact
Four statements in the captured BI corpus are Tableau capability probes of shape SELECT SUM(1) AS COL, 2 AS COL2 ... GROUP BY 2 HAVING COUNT(1) > 0. They stay rejected after every other grammar fix.
Resolution
Both cases fixed.
(A) resolves through the ordinal/alias resolution, plus two defects that had to be fixed for the shape to reach Elasticsearch at all: the emitted terms carried neither field nor script (rejected outright by Elasticsearch), and the render was not a fixed point — GROUP BY COL2 re-emitted GROUP BY 2, which re-parses as position 2.
(B) projects the constant into each aggregation row. The statement's row-invariant SELECT items seed the existing parentContext of the aggregation-row recursion, so each constant is placed once at the top and carried into every leaf row — no extra traversal of the result set, no extra allocation. UNION ALL legs each seed their own rows. A value Elasticsearch computes always wins over a constant of the same name.
A literal beside a
GROUP BYis rejected with a message saying the SQL is invalid. It is not — a constant is row-invariant, so standard SQL allows it ungrouped in a grouped SELECT list, and every mainstream engine accepts it.Reproduction
Both returned
Non-aggregated fields 2 AS COL2 cannot be selected when GROUP BY is present.Mechanism
Not a grammar gap —
SELECT 2 AS COL2 FROM tparses on its own. The non-aggregated-field validator has no arm for a row-invariant literal, so the field falls intoinvalidFields.Two sub-cases, and they fail for different reasons:
... GROUP BY 2) — rejected by the validator alone.SELECT category, 2 AS flag ... GROUP BY category) — accepting it was not enough. An aggregation response carries no hits, so thescript_fieldsentry a constant is emitted as is never fetched under"size": 0, and the column came back null on every row.Impact
Four statements in the captured BI corpus are Tableau capability probes of shape
SELECT SUM(1) AS COL, 2 AS COL2 ... GROUP BY 2 HAVING COUNT(1) > 0. They stay rejected after every other grammar fix.Resolution
Both cases fixed.
(A) resolves through the ordinal/alias resolution, plus two defects that had to be fixed for the shape to reach Elasticsearch at all: the emitted
termscarried neitherfieldnorscript(rejected outright by Elasticsearch), and the render was not a fixed point —GROUP BY COL2re-emittedGROUP BY 2, which re-parses as position 2.(B) projects the constant into each aggregation row. The statement's row-invariant SELECT items seed the existing
parentContextof the aggregation-row recursion, so each constant is placed once at the top and carried into every leaf row — no extra traversal of the result set, no extra allocation.UNION ALLlegs each seed their own rows. A value Elasticsearch computes always wins over a constant of the same name.