SELECT without LIMIT returns only 10 rows on the non-scroll search path

[fupelaqu]

SELECT without LIMIT returns only 10 rows on the non-scroll search path

Severity: P2 — silent wrong answer, third member of the #205/#207 truncation family, found
by #207's regression spec on its first live run.

Summary

A plain (non-aggregation) SELECT with no LIMIT executed through the non-scroll search path
(search / searchAs / searchAsync) emits no top-level size on the Elasticsearch
request, so Elasticsearch applies its default of 10 hits. A 45-row index returns 10
arbitrary rows with HTTP 200 and no truncation flag.

bridge/src/main/scala/app/softnetwork/elastic/sql/bridge/package.scala (requestToSearchRequest):

limit match {
  case Some(l) => _search limit l.limit from l.offset.map(_.offset).getOrElse(0)
  case _       => _search        // <- no size → ES default 10
}

Evidence

Observed live while validating #207's WindowPartitionCompletenessSpec (ES 8.18): a windowed
SELECT over 45 docs with no LIMIT returned exactly 10 base rows — while the window enrichment
(built from aggregations) correctly covered all 15 partitions. Every existing
WindowFunctionSpec query that asserts a row count carries an explicit LIMIT 20/10/100,
which is why this never surfaced. The #207 spec now carries LIMIT 100 with a comment pointing
here, deliberately keeping the two truncations separate.

Scope note

The scroll/extraction path is unaffected (it pages through everything — that's what #197
hardened). The question here is what the one-shot search path should do when the statement has
no LIMIT:

1. Emit size = index.max_result_window (10000 default) and fail/warn if hits.total exceeds
   what was returned — mirrors the GROUP BY without LIMIT silently returns only the top 10 groups (silent wrong answer) #205 choice (explicit ceiling, loud beyond it);
2. Route un-LIMITed SELECTs to the scroll path internally;
3. At minimum document the implicit 10-row default — silently inheriting the ES default is the
   one option that is clearly wrong (same argument as GROUP BY without LIMIT silently returns only the top 10 groups (silent wrong answer) #205).

A regression test must assert row count > 10 on a plain SELECT without LIMIT.

[fupelaqu]

Decision (project lead, 2026-08-08): implement option 2 — route un-LIMITed SELECTs to the scroll path internally. The scroll path already pages through everything correctly (#197); the one-shot search path keeps handling statements with an explicit LIMIT.