feat(engine): carry keys through set operations and column lists - #147

Copilot was unable to review this pull request because the user who requested the review has insufficient remaining quota.

Reviewed changes

This review covers the whole PR at 5655da1: key inference through set operations, column lists, sibling WITH queries and key columns left out of the select list, on Postgres, SQLite and MySQL, plus MySQL parameter typing through derived tables, WITH queries, set operations and ON clauses.

• Postgres set operations — set_keys keeps the left side's keys for intersect/except (with all or without). A non-all set operation is keyed on its whole row when every column passes one table column through on both sides, with one type.
• Postgres omitted key columns — a key column left out of the select list still counts when the WHERE pins it to a value (pinned_by_value), when an aliased index expression keeps it (kept_expression), or when an inner equality keeps a same-typed identity column in the list (equated_output).
• Postgres column lists and sibling WITH queries — renamed_keys/inner_name map keys by position. Catalog::enclosing_bodies holds deparsed sibling and enclosing WITH queries, so Source::Enclosing now has keys.
• SQLite compound selects — compound_keys adds a whole-row key over arms that are all plain columns of one affinity and collation. It keeps the left side's keys for intersect/except. A key column the WHERE pins may also be left out.
• MySQL keys — WITH queries and views with column lists are now read by position. set_keys keys the whole row only when every column is an exact number on both sides. A key column the WHERE pins may be left out.
• MySQL parameter types — compared now looks inside WITH bodies, derived tables, each side of a set operation and join ON clauses. A parameter compared on both sides of a set operation keeps a type only when the two sides agree.
• Oracle cases, docs and rebuilt components — new statements in subquery_keys, sqlite_subquery_keys and mysql_subquery_keys; reblessed expectations; postgres.mdx and mysql.mdx updates; three rebuilt engine.core.wasm.

  ^{｜ Fix all ➔ ｜ Fix 👍s ➔ ｜ View workflow run ｜ Using claude-opus-5-5 ｜ 𝕏}

✅ No new issues found. Both findings from the previous review are fixed.

Reviewed changes

This run covers 745f233, the one commit since the review at 5655da1.

• Fixed WITH query shadowing on Postgres — Catalog::scoped now adds an entry for every WITH query in scope, and its statement is None when the query is in a recursive clause, isn't a SELECT, or doesn't deparse. When the nearest entry for a name has no statement, enclosing_body returns None. This means an inner WITH query hides an outer one with the same name even when the engine can't read it. A recursive clause's own Source::With bodies are never read, so the upto cutoff can't skip a forward reference to a later WITH query in the same clause.
• Added an oracle case — subquery_keys/recursive_shadows_enclosing.sql is the failing statement from the earlier review, and it now infers many.
• Corrected postgres.mdx — the docs now give the whole-row condition for set operations and say that intersect/except keep the left side's keys with or without all. They also cover the alias rule for an indexed expression, and the "leaves no key" line now exempts an expression that a unique index covers.
• Rebuilt the Postgres component — packages/postgres/engine/engine.core.wasm was rebuilt in the same commit.

  ^{｜ View workflow run ｜ Using claude-opus-5-5 ｜ 𝕏}