New SQL engine: phase 6 (broaden coverage beyond 5.2 cutover) + parity-gate hardening #1525

Summary

Tracking issue for phase 6 of the new SQL engine (#1285) — the work that broadens what the new engine owns beyond the 5.2 cutover scope (phases 0–5). Everything here currently falls back to legacy under the auto default (or is rejected to force that fallback), so none of it is a 5.2 correctness risk; each item is a deliberate, separately-validated migration of a query shape off the legacy path.

⚠️ Sequencing: the safety of the 5.2 cutover (auto + allowFullScan:false) rests on the legacy fallback. Phase-6 features shrink that fallback set, so each one is a fresh silent-divergence surface. Land phase 6 in 5.3+, not the 5.2 line, and gate each feature on hardened differential coverage (see "Parity-gate hardening").

Capability work (from sqlEngine/PLAN.md)

• Subquery decorrelation
• UNION / UNION ALL
• EXPLAIN / EXPLAIN ANALYZE
• Cost-based join reordering with histograms
• Spillable hash join / hash aggregate (enforce maxHashRows with spill)

Parity debt that gates broader coverage (found during #1285 cutover work)

• Real DISTINCT-in-aggregate dedup. Currently rejected → legacy fallback (PR New SQL engine on the Resource API (phases 0-4; phase-5 cutover gated) #1285): PhysicalHashAggregate ignored the per-agg distinct flag and step()-ed every row, so COUNT(DISTINCT x) counted all rows (real-engine: 5 vs legacy 3). Implement per-accumulator dedup, then drop the guard.
• allowFullScan:true parity gaps. Enabling the flag routed full-scan shapes onto the new engine and produced 19 northwind failures:
  • Missing-attribute materialization: legacy backfills absent columns with null (N keys); new engine omits them (N−1 keys).
  • SELECT *, col AS alias projection semantics (extra/retained columns).
  • Attribute-level authorization on scans: a fully-restricted-attribute read returned [{},{},{}] instead of an empty set (leaks row existence).
  • All-null-group bare-aggregate key omission (legacy drops the key; new engine emits null).
  • These gate flipping sql.allowFullScan default to true. Until then it stays false (full scans fall back to legacy).

Deferred phase-4 items (fall back today)

• PhysicalRelationshipJoin (declared-relationship fast path)
• Projection pushdown through joins
• Single-table predicates in an INNER JOIN ... ON pushed into the inner scan
• INSERT … SELECT
• No-WHERE UPDATE / DELETE under allowFullScan:false

Parity-gate hardening (precondition for any of the above)

The differential (integrationTests/apiTests/sql-engine-differential.test.mjs) had a systematic blind spot: aggregate cases used only AS aliases, which masked both the unaliased-label bug (broke scale.test.ts → CI shard 4/6) and the DISTINCT-dedup bug. Before migrating any shape onto the new engine, the differential must cover bare/unaliased and DISTINCT variants of every shape, plus full-scan variants once allowFullScan work begins.

Burn-in informs prioritization

5.2 ships auto; the sql-engine v2 fallback: <reason> log lines measure which shapes real traffic actually hits → that's the data-driven phase-6 priority order.

Parent: #1285