SQL: derived-table joins silently degenerate to a cross product

[zzylol]

Severity: HIGH — silent wrong results

Found in a whole-repo code review. A SQL derived-table join silently degenerates to a cross product.

Location

crates/frontend-sql/src/sql/mod.rs:107-124 (the LogicalPlan::SubqueryAlias arm)

Root cause

For a derived table (FROM (SELECT …) t), the arm lowers the inner plan but drops the alias (other => self.lower_plan(other)), so the inner columns keep their inner qualifier (or none), not the alias t. An outer join key Qualified{t, k} then fails column_id_qualified and falls back to bare-name lookup, and Schema::column_id (crates/ir/src/intent_algebra/schema.rs:165) returns the first column with that name — so both sides of the join predicate bind to the same column.

Reproduction (verified)

SELECT a.v, b.v
FROM (SELECT k, v FROM m) a
JOIN (SELECT k, v FROM n) b ON a.k = b.k

Concatenated join schema is [k, v, k, v] (all table: None). The ON predicate lowers to:

Compare { left: Column(0), op: Eq, right: Column(0) }   // k = k, always true

→ the inner join becomes a cross product. Plain-table joins are unaffected (their qualifiers survive via scan_source(table, alias)); this is specific to the derived-table support added in #27/#29.

Proposed fix

Re-qualify the derived table's output columns with the alias before returning — the derived-table counterpart of what the TableScan arm already does with scan_source(&table, &alias). Then t.col resolves by qualifier and each join side binds to the correct column.

Test

Add a derived_table_join_disambiguates_via_alias case asserting the two sides of the join predicate are distinct ColumnIds (and a self-join-of-derived-tables case).