colinhacks · Oct 22, 2026 · Oct 22, 2026 · Oct 22, 2026 · Oct 22, 2026
diff --git a/engine/postgres/src/infer.rs b/engine/postgres/src/infer.rs
@@ -953,8 +953,13 @@ impl Reader<'_> {
 /// item's own result's.
 #[derive(Debug, Clone, PartialEq)]
 pub enum ItemRead {
-    /// One result column the items say nothing of: an expression, or a column of a table.
+    /// One result column the items say nothing of: an expression, or a column of an item the
+    /// reader cannot name.
     Other,
+    /// One result column: a bare reference to a column of a table in the statement's own FROM,
+    /// whose provenance is that column whatever set operations its WITH queries and subqueries
+    /// hold.
+    Table,
     /// One result column: a bare reference to the column of this name of item `item`.
     Column { item: usize, column: String },
     /// `*` or `item.*`: every column of item `item` in order, or of something else (`None`).
@@ -983,10 +988,18 @@ pub struct ItemQuery {
 /// own, and those items. `None` when the statement is not a plain SELECT that reads one, or when
 /// a `(row).*` hides how many columns a target is.
 pub fn item_reads(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
-    crate::stack::run(|| item_reads_here(sql))
+    let (reads, queries) = crate::stack::run(|| reads_here(sql))?;
+    let read = |i: usize| reads.iter().any(|r| matches!(r, ItemRead::Column { item, .. } | ItemRead::Star { item: Some(item) } if *item == i));
+    (0..queries.len()).any(read).then_some((reads, queries))
+}
+
+/// Per target of the select list, what it reads of the statement's FROM items, whether or not
+/// any of them is a statement of its own. `None` where [`item_reads`] cannot tell.
+pub fn target_reads(sql: &str) -> Option<Vec<ItemRead>> {
+    crate::stack::run(|| reads_here(sql)).map(|(reads, _)| reads)
 }
 
-fn item_reads_here(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
+fn reads_here(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
     let parsed = crate::stack::parse(sql).ok()?;
     let version = parsed.protobuf.version;
     let top = parsed.protobuf.stmts.first().and_then(|s| s.stmt.as_deref()).and_then(|n| n.node.as_ref())?;
@@ -1016,33 +1029,47 @@ fn item_reads_here(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
                     [name] => ItemRead::Star { item: items.named(name) },
                     _ => ItemRead::Star { item: None },
                 },
-                _ => match strings(&c.fields).as_deref() {
-                    Some([name, column]) => items.named(name).map_or(ItemRead::Other, |item| ItemRead::Column { item, column: column.clone() }),
+                _ => {
                     // A bare name says which item it reads only when there is one.
-                    Some([column]) => items.only().map_or(ItemRead::Other, |item| ItemRead::Column { item, column: column.clone() }),
-                    _ => ItemRead::Other,
-                },
+                    let item = match strings(&c.fields).as_deref() {
+                        Some([name, column]) => items.find(|n| n == Some(name.as_str())).map(|item| (item, column.clone())),
+                        Some([column]) => items.find(|_| true).map(|item| (item, column.clone())),
+                        _ => None,
+                    };
+                    match item {
+                        Some((Item::Query(item), column)) => ItemRead::Column { item, column },
+                        Some((Item::Table, _)) => ItemRead::Table,
+                        _ => ItemRead::Other,
+                    }
+                }
             },
             _ => ItemRead::Other,
         };
         reads.push(read);
     }
-    let read = |i: usize| reads.iter().any(|r| matches!(r, ItemRead::Column { item, .. } | ItemRead::Star { item: Some(item) } if *item == i));
-    if !(0..items.queries.len()).any(read) {
-        return None;
-    }
     Some((reads, items.queries))
 }
 
 /// The FROM items of one SELECT, through its joins.
 struct Items<'a> {
     version: i32,
     with: Option<&'a protobuf::WithClause>,
-    /// The name each item goes by, and its statement where it has one ([`ItemQuery`]).
-    names: Vec<(Option<String>, Option<usize>)>,
+    /// The name each item goes by, and what it is.
+    names: Vec<(Option<String>, Item)>,
     queries: Vec<ItemQuery>,
 }
 
+/// What one FROM item is to the reader.
+#[derive(Debug, Clone, Copy)]
+enum Item {
+    /// A statement of its own, by its place in [`Items::queries`].
+    Query(usize),
+    /// A table, a view or anything else the catalog names, read by name and not a WITH query.
+    Table,
+    /// Anything else: a WITH query or a function with no statement of its own, a LATERAL item.
+    Other,
+}
+
 impl Items<'_> {
     /// `None` for a FROM whose names the reader does not follow: a join with an alias of its own.
     fn read(&mut self, node: &Node, nulled: bool) -> Option<()> {
@@ -1088,7 +1115,12 @@ impl Items<'_> {
                     names.extend(own.into_iter().skip(names.len()));
                     Some(ItemQuery { sql: arm_text(self.version, body, self.with)?, columns: names, with: Some(cte.ctename.clone()), nulled })
                 });
-                (Some(alias(rv.alias.as_ref()).unwrap_or_else(|| rv.relname.clone())), query)
+                let name = Some(alias(rv.alias.as_ref()).unwrap_or_else(|| rv.relname.clone()));
+                if cte.is_none() {
+                    self.names.push((name, Item::Table));
+                    return Some(());
+                }
+                (name, query)
             }
             Some(NodeEnum::RangeSubselect(r)) => {
                 let query = r.subquery.as_deref().and_then(as_select).filter(|_| !r.lateral).and_then(|body| {
@@ -1121,27 +1153,38 @@ impl Items<'_> {
             Some(NodeEnum::JsonTable(t)) => (alias(t.alias.as_ref()), None),
             _ => (None, None),
         };
-        let query = query.map(|q| {
-            self.queries.push(q);
-            self.queries.len() - 1
-        });
-        self.names.push((name, query));
+        let item = match query {
+            Some(q) => {
+                self.queries.push(q);
+                Item::Query(self.queries.len() - 1)
+            }
+            None => Item::Other,
+        };
+        self.names.push((name, item));
         Some(())
     }
 
+    /// The one item whose name `matches`, when exactly one does.
+    fn find(&self, matches: impl Fn(Option<&str>) -> bool) -> Option<Item> {
+        let mut found = self.names.iter().filter(|(n, _)| matches(n.as_deref()));
+        match (found.next(), found.next()) {
+            (Some((_, item)), None) => Some(*item),
+            _ => None,
+        }
+    }
+
     /// The statement of the one item of the FROM, when it has one item and that one has one.
     fn only(&self) -> Option<usize> {
-        match self.names.as_slice() {
-            [(_, query)] => *query,
+        match self.find(|_| true) {
+            Some(Item::Query(query)) => Some(query),
             _ => None,
         }
     }
    
     /// The statement of the item that goes by `name`, when one item does and it has one.
     fn named(&self, name: &str) -> Option<usize> {
-        let mut named = self.names.iter().filter(|(n, _)| n.as_deref() == Some(name));
-        match (named.next(), named.next()) {
-            (Some((_, query)), None) => *query,
+        match self.find(|n| n == Some(name)) {
+            Some(Item::Query(query)) => Some(query),
             _ => None,
         }
     }
@@ -2133,11 +2176,30 @@ mod tests {
         assert_eq!(item_reads("WITH a AS (SELECT ROW(1, 2)::pair AS p) SELECT (p).*, p FROM a"), None);
     }
 
+    #[test]
+    fn a_bare_column_of_a_table_is_read_from_that_table() {
+        let reads = |sql: &str| target_reads(sql).unwrap();
+        // By its name or its alias, or bare where the table is the one item, and through joins.
+        assert_eq!(reads("SELECT id, name FROM users"), vec![ItemRead::Table, ItemRead::Table]);
+        assert_eq!(reads("SELECT u.name, public.users.id FROM users u"), vec![ItemRead::Table, ItemRead::Other]);
+        assert_eq!(reads("SELECT u.name, p.title, name FROM users u JOIN posts p ON p.user_id = u.id"), vec![ItemRead::Table, ItemRead::Table, ItemRead::Other]);
+        assert_eq!(reads("SELECT public.users.id FROM public.users"), vec![ItemRead::Other]);
+        assert_eq!(reads("SELECT x.id FROM public.users x"), vec![ItemRead::Table]);
+        // A WITH query of that name is no table, whatever its statement reads; an expression
+        // and a `*` read no one column.
+        assert_eq!(reads("WITH users AS (SELECT 1 AS id UNION SELECT 2) SELECT users.id FROM users"), vec![ItemRead::Column { item: 0, column: "id".into() }]);
+        assert_eq!(reads("WITH w AS (INSERT INTO t DEFAULT VALUES RETURNING id) SELECT w.id FROM w"), vec![ItemRead::Other]);
+        assert_eq!(reads("SELECT u.id + 1, u.* FROM users u"), vec![ItemRead::Other, ItemRead::Star { item: None }]);
+        assert_eq!(reads("SELECT s.id FROM users u, LATERAL (SELECT u.id) s"), vec![ItemRead::Other]);
+        // A set operation at the top hides which arm a column is.
+        assert_eq!(target_reads("SELECT id FROM users UNION SELECT id FROM posts"), None);
+    }
+
     #[test]
     fn a_derived_table_and_a_function_in_from_are_statements_of_their_own() {
         let column = |item: usize, column: &str| ItemRead::Column { item, column: column.into() };
         let (reads, queries) = item_reads("WITH c AS (SELECT 1 AS one) SELECT s.n, u.id FROM users u LEFT JOIN (SELECT count(*) AS n FROM c) s (total) ON true").unwrap();
-        assert_eq!(reads, vec![column(0, "n"), ItemRead::Other]);
+        assert_eq!(reads, vec![column(0, "n"), ItemRead::Table]);
         assert_eq!(queries, vec![ItemQuery { sql: "WITH c AS (SELECT 1 AS one) SELECT count(*) AS n FROM c".into(), columns: vec!["total".into()], with: None, nulled: true }]);
         // The preserved side of an outer join is not nulled; a FULL JOIN nulls both.
         let nulled = |sql: &str| item_reads(sql).unwrap().1.iter().map(|q| q.nulled).collect::<Vec<_>>();

diff --git a/engine/postgres/src/jsonshape.rs b/engine/postgres/src/jsonshape.rs
@@ -555,7 +555,7 @@ pub(crate) fn select_text(version: i32, items: &[Node], from: &[Node], filter: O
 }
 
 /// `sql` with every `$n` outside strings, quoted identifiers and comments written as `NULL`.
-fn without_placeholders(sql: &str) -> String {
+pub(crate) fn without_placeholders(sql: &str) -> String {
     let Ok(lexed) = crate::lex::lex(sql, crate::dialect::Dialect::Postgres) else { return sql.to_string() };
     let mut out = String::with_capacity(sql.len());
     let mut at = 0;

diff --git a/engine/postgres/src/postgres.rs b/engine/postgres/src/postgres.rs
@@ -699,7 +699,7 @@ impl<'a> Engine<'a> {
         let mut oids: Vec<u32> = raw.params.clone();
         oids.extend(raw.columns.iter().map(|c| c.oid));
         let types = self.types(&oids);
-        let checks = self.checks(&raw, parsed, written, false);
+        let checks = self.checks(sql, &raw, parsed, written, false);
         // Nullability from provenance, then from the SQL.
         let settled = self.settle(sql, &raw, parsed, trust);
         let (Some(()), Some(mut described), Some(checks)) = (types, settled, checks) else { return None };
@@ -742,13 +742,13 @@ impl<'a> Engine<'a> {
     /// takes them only where `=` is the built-in one a CHECK resolved to), and the JSON Schema
     /// they hold it to ([`check_documents`]). The describe names a column's table and attribute
     /// only for a bare reference (through a view or a subquery too), so an expression over the
-    /// column is never one. Nothing under a set operation, whose provenance names the leftmost
-    /// arm only. With `checked_only`, only the JSON and string columns, the ones a CHECK says
-    /// something of.
-    fn checks(&self, raw: &RawDescribe, parsed: Option<&Parsed>, written: &[(u32, i32)], checked_only: bool) -> Lazy<Checks> {
-        let usable = parsed.is_some_and(|p| !p.has_set_op);
+    /// column is never one. Nothing read through a set operation, whose provenance names the
+    /// leftmost arm only ([`own_provenance`]). With `checked_only`, only the JSON and string
+    /// columns, the ones a CHECK says something of.
+    fn checks(&self, sql: &str, raw: &RawDescribe, parsed: Option<&Parsed>, written: &[(u32, i32)], checked_only: bool) -> Lazy<Checks> {
+        let own = own_provenance(sql, parsed, raw.columns.len());
         let json = |oid: u32| !checked_only || jsonshape::is_json(&self.registry.borrow(), oid) || matches!(self.registry.borrow().resolve_domain(oid), crate::pgtypes::oid::TEXT | crate::pgtypes::oid::VARCHAR);
-        let mut pairs: Vec<(u32, i32)> = raw.columns.iter().filter(|c| usable && c.table != 0 && c.attnum > 0 && json(c.oid)).map(|c| (c.table, c.attnum)).collect();
+        let mut pairs: Vec<(u32, i32)> = raw.columns.iter().zip(&own).filter(|(c, own)| **own && c.table != 0 && c.attnum > 0 && json(c.oid)).map(|(c, _)| (c.table, c.attnum)).collect();
         pairs.extend(written.iter().filter(|p| !pairs.contains(p)).copied().collect::<Vec<_>>());
         self.check_rows(&pairs)
     }
@@ -906,9 +906,9 @@ impl<'a> Engine<'a> {
             None => true,
         };
         // A set operation anywhere: provenance names the leftmost arm's column, which says
-        // nothing about the other arms. The statement's own columns stay nullable; a column read
-        // from a WITH query is settled from that query's own probe, whose arms are combined.
-        let degraded = hidden || parsed.is_some_and(|p| p.has_set_op);
+        // nothing about the other arms ([`own_provenance`]). Such a column stays nullable; one
+        // read from a WITH query is settled from that query's own probe, whose arms are combined.
+        let own = own_provenance(sql, parsed, raw.columns.len());
         // A column read straight from a WITH query, a derived table or a function in FROM is as
         // nullable as that item's own result says, whatever the catalog says of the table column
         // behind it.
@@ -918,7 +918,9 @@ impl<'a> Engine<'a> {
         let columns: Vec<DescribedColumn> = raw
             .columns
             .iter()
-            .map(|col| {
+            .zip(&own)
+            .map(|(col, own)| {
+                let degraded = hidden || !own;
                 // A whole-row reference (attribute 0) of the relation whose row type the column is.
                 let own_row = col.table != 0 && col.attnum == 0 && !degraded && registry.get(col.oid).and_then(|t| t.relid) == Some(col.table);
                 let nullable = if col.table != 0 && col.attnum > 0 && !degraded {
@@ -986,8 +988,11 @@ impl<'a> Engine<'a> {
         let mut pending = false;
         for (i, query) in queries.iter().enumerate().filter(|(i, _)| read(*i)) {
             // The query reading itself: the answer is the assumption it is being settled under,
-            // for the columns it reads by name. A `*` there finds no column list of its width.
-            if let Some(entry) = self.with_stack.borrow_mut().iter_mut().find(|(text, _, _)| *text == query.sql) {
+            // for the columns it reads by name. A `*` there finds no column list of its width. A
+            // probe binds nothing, so the statement read from may be one with NULL in place of the
+            // query's placeholders (`jsonshape::select_text`): it is the same query.
+            let same = jsonshape::without_placeholders(&query.sql);
+            if let Some(entry) = self.with_stack.borrow_mut().iter_mut().find(|(text, _, _)| *text == query.sql || jsonshape::without_placeholders(text) == same) {
                 entry.2 = true;
                 let assumed = &entry.1;
                 let named = reads.iter().filter_map(|r| match r {
@@ -1027,7 +1032,7 @@ impl<'a> Engine<'a> {
         let mut out = Vec::with_capacity(count);
         for read in reads {
             match read {
-                ItemRead::Other => out.push(None),
+                ItemRead::Other | ItemRead::Table => out.push(None),
                 ItemRead::Column { item, column } => {
                     let mut named = settled.get(&item).into_iter().flatten().filter(|(n, _)| *n == column);
                     out.push(match (named.next(), named.next()) {
@@ -1139,7 +1144,7 @@ impl<'a> Engine<'a> {
         let types = self.types(&oids);
         // A JSON column a JSON constructor reads keeps the schema its CHECKs hold it to, and a
         // string column the strings they keep it to.
-        let checks = self.checks(&raw, parsed.as_ref(), &[], true);
+        let checks = self.checks(sql, &raw, parsed.as_ref(), &[], true);
         let settled = self.settle(sql, &raw, parsed.as_ref(), trust);
         let (Some(()), Some(mut described), Some(checks)) = (types, settled, checks) else { return None };
         self.stored_sublinks(sql, &mut described);
@@ -1381,6 +1386,28 @@ struct SublinkRows {
     tables: Vec<String>,
 }
 
+/// Per result column of `sql`, whether the provenance the describe reports for it is its own. A
+/// set operation's columns trace to its leftmost arm, so where the statement holds one anywhere,
+/// only a bare reference to a column of a table in its own FROM is sure to be that column,
+/// whatever its WITH queries and subqueries hold: one read through a WITH query, a derived table
+/// or a `*` may come from any arm. A statement libpg_query cannot read owns none.
+fn own_provenance(sql: &str, parsed: Option<&Parsed>, count: usize) -> Vec<bool> {
+    match parsed {
+        Some(p) if !p.has_set_op => return vec![true; count],
+        None => return vec![false; count],
+        Some(_) => {}
+    }
+    // The reads line up with the columns only where every target is one column: a `*` among
+    // targets as many as the columns is one wide when it is the only one, but two of them may be
+    // two columns and none (a WITH query `(select)` has no columns), as [`Engine::item_columns`]
+    // refuses too.
+    let aligned = |reads: &Vec<infer::ItemRead>| reads.len() == count && reads.iter().filter(|r| matches!(r, infer::ItemRead::Star { .. })).count() <= 1;
+    match infer::target_reads(sql).filter(aligned) {
+        Some(reads) => reads.iter().map(|r| matches!(r, infer::ItemRead::Table)).collect(),
+        None => vec![false; count],
+    }
+}
+
 /// The targets of a SELECT that are a scalar or `ARRAY` subquery returning the whole rows of one
 /// of its own FROM's tables (`(select c from child c …)`, `array(select c from child c …)`), and
 /// every table a FROM of the statement names. `None` where a name could mean something the
@@ -2326,4 +2353,17 @@ mod tests {
         let Type::Composite { fields, .. } = &a.columns[0].ty else { panic!("{:?}", a.columns[0].ty) };
         assert_eq!(fields[1].ty, Type::Enum { name: "text".into(), labels: vec!["click".into(), "key".into()] });
     }
+
+    #[test]
+    fn own_provenance_lines_up_with_the_columns() {
+        let own = |sql: &str, count: usize| own_provenance(sql, analyze::analyze(sql).ok().as_ref(), count);
+        let with = "WITH a AS (SELECT p.title AS t, p.title AS t2 FROM posts p UNION ALL SELECT u.email, u.email FROM users u), e AS (SELECT)";
+        // A bare column of a table of the statement's own FROM is its own beside a set operation.
+        assert_eq!(own(&format!("{with} SELECT a.t, u.name FROM a, users u"), 2), vec![false, true]);
+        // One `*` among as many targets as columns is one column wide, and among more it is none.
+        assert_eq!(own(&format!("{with}, b AS (SELECT 1 AS n) SELECT b.*, u.name FROM b, users u"), 2), vec![false, true]);
+        assert_eq!(own(&format!("{with} SELECT e.*, u.name FROM e, users u"), 1), vec![false]);
+        // Two may be two columns and none, so no position says which column a target is.
+        assert_eq!(own(&format!("{with} SELECT a.*, u.name, e.* FROM a, users u, e"), 3), vec![false; 3]);
+    }
 }
diff --git a/oracle/cases/set_operation_provenance/derived_union_beside_table.sql b/oracle/cases/set_operation_provenance/derived_union_beside_table.sql
@@ -0,0 +1 @@
+select u.name, s.n from users u join (select p.user_id as n from posts p union select c.post_id from comments c) s on s.n = u.id
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		select u.name, s.n from users u join (select p.user_id as n from posts p union select c.post_id from comments c) s on s.n = u.id