SQL: NOT IN, correlated IN, and scalar subqueries remain unsupported #125

Summary

Three subquery shapes stay rejected after #111's IN / EXISTS work landed. Each is rejected for a different reason, so they are worth separating.

1. NOT IN (subquery) — a correctness trap, not a missing feature

NOT IN is not an anti-join. Under SQL's three-valued logic, one NULL among the subquery's rows makes c NOT IN (…) UNKNOWN for every c, so the query returns no rows — whereas an anti-join returns every unmatched left row.

-- hosts.service contains a NULL
SELECT service FROM metrics WHERE service NOT IN (SELECT service FROM hosts);
--   SQL:       0 rows
--   anti-join: every metrics row

Currently: unsupported feature: NOT IN (subquery): its NULL semantics are not an anti-join.

To lower it we need one of:

• a nullability proof for the subquery's key column — the catalog's Column.nullable reaches L3, so a non-nullable key could take the anti-join path safely;
• or an explicit NOT IN join kind that carries the null-propagating semantics.

NOT EXISTS is the safe spelling and lowers today (JoinKind::Anti).

2. Correlated IN (subquery)

SELECT service FROM metrics m WHERE service IN (SELECT h.service FROM hosts h WHERE h.region = m.service)

Needs both a key match (m.service = h.service) and the lifted correlation, conjoined into one semi-join predicate. EXISTS only needs the correlation, which is why it landed first. Currently: unsupported feature: correlated IN (subquery).

3. Scalar subqueries

SELECT service, (SELECT MAX(bytes) FROM metrics) FROM metrics;      -- in the select list
SELECT service FROM metrics WHERE bytes > (SELECT MAX(bytes) FROM metrics);  -- in a predicate

Not a join at all: a one-row, one-column result broadcast into an expression. Expr::ScalarSubquery has no expression-IR counterpart. Options: a QueryExpr-valued scalar leaf, or a cross-join against the 1×1 relation plus a projection. Currently: unsupported feature: subquery-valued expression in predicate.

Correlated scalar subqueries are strictly harder again.

Notes

• Also still rejected, deliberately: an outer reference that is not a top-level filter conjunct of the subquery (correlated subquery whose outer reference is below its filter). That needs real decorrelation, not a predicate lift.
• Rejection sites: crates/frontend-sql/src/sql/mod.rs (lower_in_subquery, split_correlation) and crates/frontend-sql/src/sql/expr.rs (ScalarSubquery).
• Each rejection is pinned by a test in sql_lowering.rs, so none of these can start silently mislowering.