An OFFSET written alongside a GROUP BY never reaches Elasticsearch. The statement succeeds and returns the first page of groups, so paging through dimension members silently re-serves page one.
Mechanism
The aggregation emission branch (size: 0, _source: false, taken whenever the statement has aggregations and no row fields) is reached before the branch that would carry limit ... from ..., so the offset is never translated.
Impact
Pre-existing for the aggregate-bearing form. It becomes reachable for the aggregate-free shape that BI tools emit (SELECT category FROM t GROUP BY category), which is why it is fixed alongside #253.
Resolution
Rejected loudly at validation, on every Elasticsearch major:
OFFSET is not supported when GROUP BY is present: group results are not paginated
(the offset used to be ignored silently). Remove the OFFSET, or page by key ranges.
A real group offset needs the Elasticsearch composite aggregation with after_key paging, which is deliberately out of scope here.
Behaviour change: GROUP BY x LIMIT 10 OFFSET 5 previously "worked" (returning the first 10 groups with the offset dropped) and now errors.
An
OFFSETwritten alongside aGROUP BYnever reaches Elasticsearch. The statement succeeds and returns the first page of groups, so paging through dimension members silently re-serves page one.Mechanism
The aggregation emission branch (
size: 0,_source: false, taken whenever the statement has aggregations and no row fields) is reached before the branch that would carrylimit ... from ..., so the offset is never translated.Impact
Pre-existing for the aggregate-bearing form. It becomes reachable for the aggregate-free shape that BI tools emit (
SELECT category FROM t GROUP BY category), which is why it is fixed alongside #253.Resolution
Rejected loudly at validation, on every Elasticsearch major:
A real group offset needs the Elasticsearch
compositeaggregation withafter_keypaging, which is deliberately out of scope here.Behaviour change:
GROUP BY x LIMIT 10 OFFSET 5previously "worked" (returning the first 10 groups with the offset dropped) and now errors.