GROUP BY without LIMIT silently returns only the top 10 groups

Severity: P1 — silent wrong answer. No error, no warning, no truncation flag. The query
succeeds and returns a plausible-looking result set that omits most of the data.

Found: 2026-08-07, by the extraction-vs-trino benchmark's correctness gate, on a 10,000,000-doc
index. Present in R1 as shipped.

Summary

SELECT category, COUNT(*), AVG(amount) FROM t GROUP BY category returns 10 rows on an index
with 100 distinct categories. The 10 returned are the top 10 by document count, covering
1,006,074 of 10,000,000 documents — 10% of the data. Nothing in the response indicates the
other 90 groups exist.

In SQL, GROUP BY with no LIMIT means every group. Here it silently means Elasticsearch's
default terms bucket size, which is 10.

Reproduction

Index: 10,000,000 docs, verified cardinality via ES cardinality aggregation.

 10 groups |  1,006,074 docs | SELECT category, COUNT(*) AS cnt FROM bench_events_10m GROUP BY category
100 groups | 10,000,000 docs | SELECT category, COUNT(*) AS cnt FROM bench_events_10m GROUP BY category LIMIT 200
 10 groups |  2,005,072 docs | SELECT country,  COUNT(*) AS cnt FROM bench_events_10m GROUP BY country
  8 groups | 10,000,000 docs | SELECT status,   COUNT(*) AS cnt FROM bench_events_10m GROUP BY status

Ground truth from Elasticsearch: category = 100 distinct, country = 50, status = 8.

So:

Adding LIMIT 200 returns all 100 groups and accounts for all 10,000,000 documents, which
confirms both the cause and that the data itself is complete.

Root cause

sql/src/main/scala/app/softnetwork/elastic/sql/query/GroupBy.scala, Bucket.update — both
branches derive the bucket size solely from the statement's LIMIT:

// line 72
this.copy(identifier = field.identifier, size = request.limit.map(_.limit))
// line 75
this.copy(identifier = identifier.update(request), size = request.limit.map(_.limit))

With no LIMIT, size is None, no size is emitted into the terms aggregation, and
Elasticsearch applies its documented default of 10 buckets.

Two distinct concepts are conflated: LIMIT bounds the result rows; the terms size bounds
how many groups Elasticsearch computes at all. They are not the same, and the absence of one
must not silently set the other.

Why it has not been caught before

It is invisible whenever true cardinality happens to be ≤ 10 — which describes essentially every
fixture in the test suites (the JOIN fixtures are 100 orders × 10 customers). status above
demonstrates this: 8 distinct values, correct answer, no hint that anything is wrong. The bug only
appears at realistic cardinality, which is exactly where nobody was testing.

Impact

Any user aggregating over a field with more than 10 distinct values gets a wrong answer with no
indication. This is worse than an error: a dashboard, a report, or a downstream ETL consuming
GROUP BY output will silently carry a 90%-incomplete result. It affects every consumer surface
— REPL, JDBC, ADBC, Flight SQL — because the defect is in the SQL→ES transform, not a client.

It also silently interacts with LIMIT: GROUP BY x LIMIT 20 computes at most 20 groups
server-side, so even a user who adds a LIMIT for pagination gets a truncated aggregation, not a
paginated view of a complete one.

Suggested fix

The correct default is "all groups", not 10. Options, roughly in order of preference:

1. Emit an explicit large size when the statement has no LIMIT (e.g. the
   search.max_buckets ceiling), so the default matches SQL semantics.
2. Use a composite aggregation for unbounded GROUP BY, which paginates groups properly and
   has no arbitrary cap — the semantically correct construct for "all groups".
3. At minimum, never let the result be silently wrong: if the terms response reports
   sum_other_doc_count > 0, surface a truncation warning or fail loudly.

Also decouple LIMIT from bucket size: LIMIT should bound returned rows after grouping, not
the number of groups Elasticsearch computes.

Whatever is chosen, a regression test must assert group count and total doc coverage at
cardinality > 10 — the existing fixtures cannot catch this class of bug.

Notes

Found while running the Arrow Flight SQL vs Trino extraction benchmark; its correctness gate
aborted the session rather than publish timings for a query that returned wrong data. The
benchmark's S3 scenario (GROUP BY pushdown) is blocked until this is fixed and will be re-run
against a corrected build.