colinhacks · Oct 26, 2026 · Oct 26, 2026 · Oct 26, 2026
diff --git a/engine/mysql/src/mysql_keys.rs b/engine/mysql/src/mysql_keys.rs
@@ -29,10 +29,10 @@ use sqlparser::ast::{self, Distinct, Expr, GroupByExpr, JoinConstraint, JoinOper
 /// The WITH queries in scope, outermost first: each name in lower case, its body (`None` for one
 /// of a RECURSIVE WITH, or one that renames its columns), and how many of the entries before it
 /// its body sees.
-type Ctes<'a> = Vec<(String, Option<&'a Query>, usize)>;
+pub(crate) type Ctes<'a> = Vec<(String, Option<&'a Query>, usize)>;
 
 /// `outer` with a query's own WITH queries after it.
-fn ctes_of<'a>(q: &'a Query, outer: &[(String, Option<&'a Query>, usize)]) -> Ctes<'a> {
+pub(crate) fn ctes_of<'a>(q: &'a Query, outer: &[(String, Option<&'a Query>, usize)]) -> Ctes<'a> {
     let mut out: Ctes<'a> = outer.to_vec();
     if let Some(w) = &q.with {
         for c in &w.cte_tables {
@@ -548,6 +548,50 @@ fn output_class(q: &Query, outer: &[(String, Option<&Query>, usize)], keys: &Key
     })
 }
 
+/// The table and column that output column `name` (lower case) of a query passes through
+/// unchanged, the table by the name the query writes it, read without the catalog: a derived table
+/// or a WITH query through its select list, down to a table. `outer` are the WITH queries the
+/// query sees ([`ctes_of`]), so that none of their names is read as a table.
+pub(crate) fn output_origin(q: &Query, outer: &[(String, Option<&Query>, usize)], name: &str) -> Option<(String, String)> {
+    let keys = Keys::default();
+    origin_in(q, outer, &keys, name)
+}
+
+fn origin_in(q: &Query, outer: &[(String, Option<&Query>, usize)], keys: &Keys, name: &str) -> Option<(String, String)> {
+    let s = plain(q)?;
+    let ctes = ctes_of(q, outer);
+    let from = From::read(s, &ctes, keys);
+    if from.refused.is_some() {
+        return None;
+    }
+    let through = |i: usize, column: &str| -> Option<(String, String)> {
+        if from.items[i].renamed {
+            return None;
+        }
+        match &from.items[i].source {
+            Source::Table(t) => Some((t.clone(), column.to_string())),
+            Source::Query(q, ctes) => origin_in(q, ctes, keys, column),
+            _ => None,
+        }
+    };
+    s.projection.iter().find_map(|item| match item {
+        SelectItem::UnnamedExpr(e) => match from.place_expr(e)? {
+            Placed::Item(i, c) if c == name => through(i, &c),
+            _ => None,
+        },
+        SelectItem::ExprWithAlias { expr, alias } if alias.value.eq_ignore_ascii_case(name) => match from.place_expr(expr)? {
+            Placed::Item(i, c) => through(i, &c),
+            _ => None,
+        },
+        SelectItem::Wildcard(_) if !from.merged && from.items.len() == 1 => through(0, name),
+        SelectItem::QualifiedWildcard(SelectItemQualifiedWildcardKind::ObjectName(n), _) => match n.0.as_slice() {
+            [ObjectNamePart::Identifier(q)] => from.items.iter().position(|it| it.alias == q.value.to_ascii_lowercase()).and_then(|i| through(i, name)),
+            _ => None,
+        },
+        _ => None,
+    })
+}
+
 /// The unique keys of a query's rows: sets of its output columns no two of its rows share a value
 /// of every one of (NULL aside, which no `=` matches).
 ///

diff --git a/engine/mysql/src/mysql_params.rs b/engine/mysql/src/mysql_params.rs
@@ -201,6 +201,12 @@ pub(crate) fn typed(statement: &Statement, names: &Names, host: &Host) -> Lazy<T
 /// table's where the statement reads one; a qualified one is the table its name or alias names.
 fn compared(statement: &Statement, names: &Names) -> Vec<(String, String, Target)> {
     let mut out = Vec::new();
+    // The statement's WITH queries, each with those its body sees; one that is recursive or
+    // renames its columns has no body to read through.
+    let ctes = match statement {
+        Statement::Query(q) => crate::mysql_keys::ctes_of(q, &[]),
+        _ => Vec::new(),
+    };
     let (from, filters): (Vec<&TableWithJoins>, Vec<&Expr>) = match statement {
         Statement::Query(q) => match q.body.as_ref() {
             SetExpr::Select(s) => (s.from.iter().collect(), s.selection.iter().chain(s.having.iter()).collect()),
@@ -214,28 +220,57 @@ fn compared(statement: &Statement, names: &Names) -> Vec<(String, String, Target
         _ => return out,
     };
     // The tables by the name the statement reads each by; `plain` where every item is a table.

8000
+    // A derived table or a WITH query is read through to the table column each of its columns
+    // passes through unchanged ([`crate::mysql_keys::output_origin`]).
     let mut tables: Vec<(String, String)> = Vec::new();
+    let mut derived: Vec<(String, &Query, &[(String, Option<&Query>, usize)])> = Vec::new();
     let mut plain = true;
+    let mut items = 0;
     for twj in &from {
         for factor in std::iter::once(&twj.relation).chain(twj.joins.iter().map(|j| &j.relation)) {
+            items += 1;
             match factor {
-                TableFactor::Table { name, alias, .. } => match table_name(name) {
-                    Some(table) => {
-                        let by = alias.as_ref().map(|a| a.name.value.clone()).unwrap_or_else(|| table.clone());
-                        tables.push((by.to_ascii_lowercase(), table));
+                TableFactor::Table { name, alias, args: None, .. } => {
+                    // A name one of the statement's WITH queries has is that query, never a table.
+                    let cte = match name.0.as_slice() {
+                        [ast::ObjectNamePart::Identifier(n)] => ctes.iter().rposition(|(c, _, _)| *c == n.value.to_ascii_lowercase()),
+                        _ => None,
+                    };
+                    match (cte, table_name(name)) {
+                        (Some(at), _) => {
+                            plain = false;
+                            let (cte_name, body, sees) = &ctes[at];
+                            let by = alias.as_ref().map(|a| a.name.value.to_ascii_lowercase()).unwrap_or_else(|| cte_name.clone());
+                            if let (Some(body), true) = (body, alias.as_ref().is_none_or(|a| a.columns.is_empty())) {
+                                derived.push((by, body, &ctes[..*sees]));
+                            }
+                        }
+                        (None, Some(table)) => {
+                            let by = alias.as_ref().map(|a| a.name.value.clone()).unwrap_or_else(|| table.clone());
+                            tables.push((by.to_ascii_lowercase(), table));
+                        }
+                        (None, None) => plain = false,
                     }
-                    None => plain = false,
-                },
+                }
+                TableFactor::Derived { lateral: false, subquery, alias: Some(alias), .. } if alias.columns.is_empty() => {
+                    plain = false;
+                    derived.push((alias.name.value.to_ascii_lowercase(), subquery.as_ref(), &ctes[..]));
+                }
                 _ => plain = false,
             }
         }
     }
     let column = |e: &Expr| -> Option<(String, String)> {
         match e {
             Expr::Identifier(i) if plain && tables.len() == 1 => Some((tables[0].1.clone(), i.value.to_ascii_lowercase())),
+            Expr::Identifier(i) if items == 1 && derived.len() == 1 => crate::mysql_keys::output_origin(derived[0].1, derived[0].2, &i.value.to_ascii_lowercase()),
             Expr::CompoundIdentifier(parts) if parts.len() == 2 => {
                 let by = parts[0].value.to_ascii_lowercase();
-                tables.iter().find(|(b, _)| *b == by).map(|(_, t)| (t.clone(), parts[1].value.to_ascii_lowercase()))
+                let name = parts[1].value.to_ascii_lowercase();
+                match tables.iter().find(|(b, _)| *b == by) {
+                    Some((_, t)) => Some((t.clone(), name)),
+                    None => derived.iter().find(|(b, _, _)| *b == by).and_then(|(_, q, sees)| crate::mysql_keys::output_origin(q, sees, &name)),
+                }
             }
             _ => None,
         }

diff --git a/oracle/cases/mysql_limits/expected.txt b/oracle/cases/mysql_limits/expected.txt
@@ -7,7 +7,7 @@ count_limit_param.sql: maybeOne (n: int4) -> { n: int8 }
 count_offset_0.sql: maybeOne () -> { n: int8 }
 count_offset_1.sql: maybeOne () -> { n: int8 }
 count_offset_param.sql: maybeOne (skip: int4) -> { n: int8 }
-derived_limit.sql: maybeOne (id: int4) -> { name: text? }
+derived_limit.sql: maybeOne (id: int8) -> { name: text? }
 key_comma.sql: maybeOne (id: int8) -> { id: int8, name: text? }
 key_limit_0.sql: maybeOne (id: int8) -> { id: int8, name: text? }
 key_limit_5.sql: maybeOne (id: int8) -> { id: int8, name: text? }

diff --git a/oracle/cases/mysql_subquery_keys/expected.txt b/oracle/cases/mysql_subquery_keys/expected.txt
@@ -1,19 +1,19 @@
-bare_name.sql: maybeOne (id: int4) -> { id: int8, title: text }
+bare_name.sql: maybeOne (id: int8) -> { id: int8, title: text }
 comma_join_on_key.sql: maybeOne (id: int8) -> { title: text, name: text }
 compared_as_number.sql: many () -> { email: text }
-cross_join.sql: many (id: int4) -> { id: int8 }
-distinct.sql: maybeOne (a: int4) -> { author_id: int8 }
-distinct_expression.sql: many (a: int4) -> { author_id: int8, t: text? }
+cross_join.sql: many (id: int8) -> { id: int8 }
+distinct.sql: maybeOne (a: int8) -> { author_id: int8 }
+distinct_expression.sql: many (a: int8) -> { author_id: int8, t: text? }
 double_key_exact_literal.sql: maybeOne () -> { id: int8 }
 expression.sql: many (id: int4) -> { id: int8, title: text }
-filtered_inside.sql: maybeOne (a: int4, id: int4) -> { id: int8, title: text }
-group_by.sql: maybeOne (a: int4) -> { author_id: int8, n: int8 }
-group_by_ordinal.sql: maybeOne (a: int4) -> { author_id: int8, n: int8 }
+filtered_inside.sql: maybeOne (a: int4, id: int8) -> { id: int8, title: text }
+group_by.sql: maybeOne (a: int8) -> { author_id: int8, n: int8 }
+group_by_ordinal.sql: maybeOne (a: int8) -> { author_id: int8, n: int8 }
 group_key_hidden.sql: many (n: int4) -> { author_id: int8, n: int8 }
 join_double_column.sql: many (id: int8) -> { name: text }
-join_many.sql: many (id: int4) -> { id: int8, tag: text }
+join_many.sql: many (id: int8) -> { id: int8, tag: text }
 join_on_key.sql: maybeOne (id: int8) -> { title: text, name: text }
-join_pinned.sql: maybeOne (id: int4) -> { id: int8, title: text, name: text }
+join_pinned.sql: maybeOne (id: int8) -> { id: int8, title: text, name: text }
 join_text_column.sql: many (id: int8) -> { title: text, name: text }
 join_unpinned.sql: many (id: int8) -> { title: text, name: text }
 joined_on_key.sql: maybeOne (id: int8) -> { title: text, name: text }
@@ -27,24 +27,24 @@ key_exact_function.sql: maybeOne (x: int4) -> { id: int8 }
 key_inside.sql: maybeOne (id: int4) -> { id: int8, title: text }
 lateral_pinned.sql: maybeOne (u: int8, p: int4) -> { name: text, title: text }
 lateral_source.sql: many (id: int4) -> { id: int8 }
-left_join_many.sql: many (id: int4) -> { id: int8 }
+left_join_many.sql: many (id: int8) -> { id: int8 }
 left_join_on_key.sql: maybeOne (id: int8) -> { title: text, name: text? }
-left_join_pinned.sql: maybeOne (id: int4) -> { id: int8, title: text, name: text? }
-limit_inside.sql: maybeOne (id: int4) -> { id: int8, title: text }
-nested.sql: maybeOne (id: int4) -> { id: int8, title: text }
+left_join_pinned.sql: maybeOne (id: int8) -> { id: int8, title: text, name: text? }
+limit_inside.sql: maybeOne (id: int8) -> { id: int8, title: text }
+nested.sql: maybeOne (id: int8) -> { id: int8, title: text }
 not_a_key.sql: many (e: text) -> { id: int8, email: text }
-null_extended_side.sql: many (id: int4) -> { id: int8?, name: text }
+null_extended_side.sql: many (id: int8) -> { id: int8?, name: text }
 recursive.sql: many (id: int4) -> { id: int8 }
-renamed.sql: maybeOne (id: int4) -> { post_id: int8, title: text }
+renamed.sql: maybeOne (id: int8) -> { post_id: int8, title: text }
 right_join_on_key.sql: maybeOne (id: int8) -> { title: text, name: text? }
-rollup.sql: many (a: int4) -> { author_id: int8?, n: int8 }
+rollup.sql: many (a: int8) -> { author_id: int8?, n: int8 }
 second_key.sql: maybeOne (e: text) -> { id: int8, email: text }
-self_join_nonkey.sql: many (id: int4) -> { id: int8 }
-star.sql: maybeOne (id: int4) -> { id: int8, author_id: int8, title: text }
+self_join_nonkey.sql: many (id: int8) -> { id: int8 }
+star.sql: maybeOne (id: int8) -> { id: int8, author_id: int8, title: text }
 straight_join.sql: maybeOne (id: int8) -> { title: text, name: text }
-subquery.sql: maybeOne (id: int4) -> { id: int8, title: text }
-two_subqueries.sql: maybeOne (id: int4) -> { title: text, name: text }
-two_withs.sql: maybeOne (id: int4) -> { id: int8, title: text }
+subquery.sql: maybeOne (id: int8) -> { id: int8, title: text }
+two_subqueries.sql: maybeOne (id: int8) -> { title: text, name: text }
+two_withs.sql: maybeOne (id: int8) -> { id: int8, title: text }
 union_all.sql: many (id: int4) -> { id: int8 }
 view.sql: maybeOne (id: int8) -> { id: int8, title: text }
 view_column_list.sql: many (id: int8) -> { post_id: int8, headline: text }
@@ -55,6 +55,8 @@ view_merged.sql: maybeOne (id: int8) -> { id: int8, title: text }
 view_of_view.sql: maybeOne (id: int8) -> { id: int8, title: text }
 view_swapped_columns.sql: many (id: text) -> { title: int8, id: text }
 view_temptable.sql: maybeOne (id: int8) -> { id: int8, title: text }
-window_inside.sql: maybeOne (id: int4) -> { id: int8, rn: int8 }
-with_joined.sql: maybeOne (u: int4, id: int4) -> { title: text, name: text }
-with_query.sql: maybeOne (id: int4) -> { id: int8, title: text }
+window_inside.sql: maybeOne (id: int8) -> { id: int8, rn: int8 }
+with_joined.sql: maybeOne (u: int4, id: int8) -> { title: text, name: text }
+with_query.sql: maybeOne (id: int8) -> { id: int8, title: text }
+with_shadows_table.sql: maybeOne (id: int8) -> { id: int8, title: text }
+with_sibling_shadow.sql: maybeOne (id: int8) -> { id: int8 }
diff --git a/oracle/cases/mysql_subquery_keys/setup.sql b/oracle/cases/mysql_subquery_keys/setup.sql
@@ -15,3 +15,4 @@ create view recent_posts as select * from public_posts where id > 10;
 create view post_tags as select p.id, t.tag from posts p join tags t on t.post_id = p.id;
 create table scores (id bigint primary key, score double not null, rate double not null unique);
 create view swapped (title, id) as select id, title from posts;
+create table flags (id tinyint(1) primary key);
diff --git a/oracle/cases/mysql_subquery_keys/with_shadows_table.sql b/oracle/cases/mysql_subquery_keys/with_shadows_table.sql
@@ -0,0 +1 @@
+with flags as (select id, title from posts) select * from flags where flags.id = :id
diff --git a/oracle/cases/mysql_subquery_keys/with_sibling_shadow.sql b/oracle/cases/mysql_subquery_keys/with_sibling_shadow.sql
@@ -0,0 +1 @@
+with a as (select id from flags), b as (select p.id from posts p) select * from b where b.id = :id
diff --git a/packages/mysql/engine.wasm b/packages/mysql/engine.wasm
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		with flags as (select id, title from posts) select * from flags where flags.id = :id
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		with a as (select id from flags), b as (select p.id from posts p) select * from b where b.id = :id