SQL: subquery-valued predicates (IN/EXISTS/scalar), DISTINCT ON, and median/array_agg unsupported

[zzylol]

Summary

Three structural SQL features are rejected by the lowerer. Grouped because each is individually small; split out if any one is prioritized.

Like #110, none of these are exercised by the existing SQL corpus tests, which pass.

Reproduction

Against the netflow_table / hosts catalog:

-- 1. subquery-valued predicates (three shapes, one error)
SELECT * FROM netflow_table WHERE srcip IN (SELECT ip FROM hosts);
SELECT * FROM netflow_table n WHERE EXISTS (SELECT 1 FROM hosts h WHERE h.ip = n.srcip);
SELECT srcip, (SELECT MAX(pkt_len) FROM netflow_table) FROM netflow_table;
-- unsupported feature: subquery-valued expression in predicate

-- 2. DISTINCT ON
SELECT DISTINCT ON (srcip) srcip, pkt_len FROM netflow_table;
-- unsupported feature: DISTINCT ON

-- 3. long-tail aggregates
SELECT median(pkt_len) FROM netflow_table;      -- unsupported aggregate: median
SELECT array_agg(srcip) FROM netflow_table;     -- unsupported aggregate: array_agg  (WONTFIX)

Location

• crates/frontend-sql/src/sql/expr.rs:191 — subquery-valued expression in predicate
• crates/frontend-sql/src/sql/mod.rs:106 — LogicalPlan::Subquery(_)
• crates/frontend-sql/src/sql/mod.rs:82 — Distinct::On(_)
• crates/frontend-sql/src/sql/mod.rs:469 — UnsupportedAggregate(name)

Notes

• Subqueries. IN (subquery), EXISTS, and a scalar subquery in the select list all hit the same expr.rs:191 rejection. Uncorrelated cases (IN, scalar) reduce to a semi-join / broadcast scalar and are the tractable subset; correlated EXISTS is materially harder. Note that derived tables (FROM (SELECT …)) and WITH do lower today — this is specifically about subqueries in expression position. See also SQL: derived-table joins silently degenerate to a cross product #66 (derived-table joins → cross product), now closed.
• median is the interesting one for this project: it is exactly AggIntent::Quantile { q: 0.5 }, which the lowerer already emits for approx_percentile_cont (which does lower). Mapping median onto it is close to a one-liner and directly feeds the sketch path.
• array_agg — WONTFIX (decided 2026-07-09). It does not pre-aggregate (output is O(input rows)), has no bounded-memory approximate form, and its partial state is the data, so "mergeable" means nothing for it. plan::boundary::realize is an exhaustive match over AggIntent, so adding a variant would force boundary, bind and schema derivation to each handle it — and every answer would be PassThrough. It also appears in neither SQL corpus. unsupported aggregate: array_agg is the intended behaviour. See the comment below.
• DISTINCT ON is Postgres-flavoured "first row per group" — it overlaps with the partitioned-topk work in SQL: partitioned topk (top-k per group via window functions) is unsupported #24.
• Aside, not filed: SELECT SUM(x) FILTER (WHERE …) fails earlier, inside DataFusion 43's parser (ParserError("Expected end of statement, found: (")), so it is not our rejection to fix.

[zzylol]

Re-verified on a clean origin/main worktree (9fdd3d7) — every rejection in this issue reproduces unchanged. (My original measurements were taken on a branch that predated the #40 merge; that affected the PromQL denominators in #108/#109/#112 but nothing on the SQL side, since no SQL lowering work has landed in between.)

[zzylol]

Blocked on #115 for the median part.

median → AggIntent::Quantile { q: 0.5 } is a one-line mapping, but AggIntent::Quantile currently carries no input column, so median(v) and median(w) would lower to identical L3 and get merged by CSE. Adding median on that foundation would ship a "supported" query pattern that computes the wrong answer.

#115 should land first; median then becomes the one-liner it looks like.

[zzylol]

Unblocked — #115 landed in #116, so AggIntent::Quantile now carries col: Option<ColumnId>.

median is now the one-liner it looked like: add "median" => (AggFunc::Quantile(0.5), reducer_col(&name, &agg_fn.args)?) to the aggregate map in crates/frontend-sql/src/sql/mod.rs, alongside the existing approx_percentile_cont arm. It inherits the correct column binding and the expression-argument rejection for free.

[zzylol]

Status

• median / approx_median — landed in feat(sql): lower median / approx_median to the φ=0.5 quantile (#111) #117 (`e66a6e0`). Both lower to AggIntent::Quantile { col, q: 0.5, accuracy }, sharing one intent with approx_percentile_cont(c, 0.5) so CSE merges the spellings. Column binding and expression rejection come from L3: AggIntent::Quantile / Cardinality / TopK drop their input column — distinct aggregates compare equal #115.
• array_agg — proposed wontfix: no sketch/summary representation, pre-aggregates nothing, so it has no place in the intent algebra. Say the word and I will close this item.
• Subquery-valued predicates (IN (subquery), EXISTS, scalar subquery) — untouched. Uncorrelated IN/scalar reduce to a semi-join / broadcast scalar and are the tractable subset; correlated EXISTS is materially harder.
• DISTINCT ON — untouched; overlaps the partitioned-topk work in SQL: partitioned topk (top-k per group via window functions) is unsupported #24.

The remaining three are independent of each other and each warrants its own PR.

[zzylol]

array_agg — closing as WONTFIX

Not a coverage gap; the rejection is the intended behaviour. Recording the reasoning so this does not get re-litigated.

It has nothing to bind to. AggIntent is the "what to compute" vocabulary that lets the planner pick a sketch or a mergeable accumulator per node. array_agg satisfies none of the three properties that makes an entry there useful:

• It does not pre-aggregate — output is O(input rows), it just moves the data.
• It has no bounded-memory approximate form. There is no such thing as an approximate array_agg.
• Its partial state is the full data, so "mergeable" carries no information.

It would cost every consumer. plan::boundary::realize is deliberately an exhaustive match:

Exhaustive over the AggIntent vocabulary — adding a variant without an explicit realization is a compile error, and the coverage-matrix test pins each variant's category.

So an AggIntent::ArrayAgg forces boundary, bind and schema derivation to each handle it, and every one of those answers is PassThrough. That is a variant in the optimizer's central enum that the optimizer never looks at.

No workload asks for it. It appears in neither SQL corpus (netflow.sql, synthetic_packet_trace_queries.sql: 0 occurrences). It reached this issue because I probed 36 hand-written query shapes against lower_sql, not because a real query needed it.

SELECT array_agg(c) continues to fail with unsupported aggregate: array_agg.

---

Reopen this item if a real workload needs array_agg to reach L3 — the argument above is about cost/benefit, not impossibility. median from the same bucket went the other way for exactly that reason: it is Quantile { q: 0.5 } and feeds the sketch path directly (#117).

Still open in this issue: subquery-valued predicates (IN/EXISTS/scalar) and DISTINCT ON.

[zzylol]

Status

• median / approx_median — feat(sql): lower median / approx_median to the φ=0.5 quantile (#111) #117 (e66a6e0).
• array_agg — WONTFIX, pinned by array_agg_is_deliberately_rejected (test(sql): pin array_agg's rejection as a decision, not a gap (#111) #122).
• Subquery-valued predicates — feat(sql): lower IN / EXISTS subquery predicates to semi- and anti-joins (#111) #126 (45de023). IN (uncorrelated) and EXISTS lower to JoinKind::Semi; NOT EXISTS to JoinKind::Anti. The residual WHERE conjuncts stay below the join so they still fold onto the Scan.
  What stays rejected — NOT IN, correlated IN, scalar subqueries — is split to SQL: NOT IN, correlated IN, and scalar subqueries remain unsupported #125, each pinned by its own test. NOT IN in particular is a correctness trap rather than a missing feature: under three-valued logic it is not an anti-join.
• DISTINCT ON — untouched. Postgres-flavoured "first row per group"; overlaps the partitioned-topk work in SQL: partitioned topk (top-k per group via window functions) is unsupported #24, which is worth reading before starting.

Only DISTINCT ON remains in this issue.

[zzylol]

Related open work

• Remaining NOT IN, correlated IN, and scalar-subquery semantics were split into SQL: NOT IN, correlated IN, and scalar subqueries remain unsupported #125.
• This issue contributes to the broader corpus-coverage tracker Add corpus tests from DQC, BGP analytics, ClickBench, and business analytics workloads #132.

[zzylol]

Closing this mixed umbrella after restructuring.

Final disposition:

• median / approx_median: completed by feat(sql): lower median / approx_median to the φ=0.5 quantile (#111) #117.
• array_agg: deliberately unsupported and pinned by test(sql): pin array_agg's rejection as a decision, not a gap (#111) #122.
• Supported IN / EXISTS: completed by feat(sql): lower IN / EXISTS subquery predicates to semi- and anti-joins (#111) #126.
• Remaining NOT IN, correlated IN, and scalar subqueries: SQL: NOT IN, correlated IN, and scalar subqueries remain unsupported #125.
• The sole previously unsplit item, DISTINCT ON: now tracked with focused semantics and acceptance criteria in SQL: lower DISTINCT ON with ordered first-row-per-group semantics #289.

No unresolved work remains uniquely owned by this issue.