Story 14.1: ORDER BY ... NULLS FIRST / NULLS LAST

[fupelaqu]

Story 14.1 — Epic 14 R1 Analytical SQL

Accept and translate the ANSI standard ORDER BY col [ASC|DESC] [NULLS FIRST | NULLS LAST] clause. Elasticsearch sort already supports null-handling via the missing parameter (_first | _last); this exposes that control at the SQL surface.

Behavior

• NULLS FIRST → "missing": "_first"
• NULLS LAST → "missing": "_last"
• When omitted, defaults follow the Elasticsearch convention (ASC → nulls last, DESC → nulls first).
• Rejected on GROUP BY / aggregation ORDER BY (ES terms aggregations have no missing parameter) rather than silently dropped.

Scope

• SQL AST: NullOrdering trait + NullsFirst / NullsLast + FieldSort.nullOrdering.
• Parser: optional NULLS FIRST / NULLS LAST after ASC/DESC (case-insensitive).
• Bridge: apply .missing(_first|_last) on the FieldSort builder (covers ES7/8/9; ES6 hand-mirrored).
• Tests: parser round-trip, bridge JSON emission, integration specs for ES 6/7/8/9.
• Docs: documentation/sql/dql_statements.md + web dql.mdx.

Known limitation

ES6 Jest scroll / search_after does not propagate NULLS FIRST / NULLS LAST reliably across batches.

BMAD Story 14.1 (Epic 14 — Analytical SQL).