colinhacks · Oct 24, 2026 · Oct 23, 2026 · Oct 24, 2026 · Oct 24, 2026 · Oct 24, 2026
diff --git a/engine/postgres/src/cardinality.rs b/engine/postgres/src/cardinality.rs
diff --git a/engine/postgres/src/postgres.rs b/engine/postgres/src/postgres.rs
@@ -436,7 +436,12 @@ impl<'a> Engine<'a> {
             None => None,
         };
         let functions: &[String] = parsed.as_ref().map(|p| p.functions.as_slice()).unwrap_or(&[]);
+        let view_rows_of = |functions: &[String], relations: &[String]| catalog_rows(self.host, functions, relations);
         let catalog_rows = catalog_rows(self.host, functions, &relations);
+        // The views the statement reads give the row-count proof the keys their statements keep,
+            // from the keys of the tables they read, which are asked apart from the statement's own.
+        let (view_definitions, view_relations, view_functions) = self.viewed(&relations);
+        let view_rows = if view_definitions.is_empty() { Some(None) } else { view_rows_of(&view_functions, &view_relations).map(Some) };
         for probe in &jsonshape::probes_ahead(&rewritten.sql) {
             self.prewarm(&probe.sql);
         }
@@ -462,7 +467,7 @@ impl<'a> Engine<'a> {
             Some(target) => self.host.query(REQUIRED_COLUMNS_QUERY, vec![json!(target.relation)], Scope::Statement),
             None => Some(Ok(Vec::new())),
         };
-        let (Some(described), Some(trust), Some(operators), Some(catalog_rows), Some(arbiters), Some(written), Some(required)) = (described, trust, operators, catalog_rows, arbiters, written, required) else { return None };
+        let (Some(described), Some(trust), Some(operators), Some(catalog_rows), Some(arbiters), Some(written), Some(required), Some(view_rows)) = (described, trust, operators, catalog_rows, arbiters, written, required, view_rows) else { return None };
         let (raw, described) = match described {
             Ok(d) => d,
             Err(e) => {
@@ -472,6 +477,10 @@ impl<'a> Engine<'a> {
         };
         // The catalog the row-count proof reads, with what the describe and the trust settled.
         let mut catalog = build_catalog(&operators, catalog_rows);
+        if let Some(rows) = view_rows {
+            merge_keys(&mut catalog, build_catalog(&operators, rows));
+        }
+        catalog.views = view_definitions;
         catalog.param_types = raw.params.clone();
         trusted(&mut catalog, &trust);
         let arbiters = arbiters.unwrap_or_default();
@@ -695,6 +704,35 @@ impl<'a> Engine<'a> {
         }
     }
 
+    /// The views among `relations`, and the views those read, as far as their definitions are
+    /// known yet: each view's name to its defining SELECT, and the relations their statements read
+    /// that `relations` does not name, with the functions their statements call. Each such
+    /// relation is prefetched, so a view among them is known a round later.
+    fn viewed(&self, relations: &[String]) -> (HashMap<String, String>, Vec<String>, Vec<String>) {
+        let mut definitions = HashMap::new();
+        let mut extra = Vec::new();
+        let mut functions = Vec::new();
+        let mut seen: HashSet<String> = relations.iter().cloned().collect();
+        let mut queue: Vec<String> = relations.to_vec();
+        while let Some(name) = queue.pop() {
+            let definition = {
+                let known = self.relations.borrow();
+                let views = self.views.borrow();
+                known.iter().find(|(_, (n, _))| *n == name).and_then(|(oid, _)| views.get(oid).cloned())
+            };
+            let Some(definition) = definition else { continue };
+            let parsed = analyze::analyze(&definition).ok();
+            functions.extend(parsed.iter().flat_map(|p| p.functions.iter().cloned()));
+            let unread: Vec<String> = relations_of(parsed.as_ref()).into_iter().filter(|n| seen.insert(n.clone())).collect();
+            definitions.insert(name, definition);
+            // Unanswered, a view among them is only read a round later.
+            let _ = self.prefetch(&unread);
+            extra.extend(unread.iter().cloned());
+            queue.extend(unread);
+        }
+        (definitions, sorted(extra), sorted(functions))
+    }
+
     /// Every column and every column type of the named relations, in one round, so `settle`
     /// and `types` have their answers before the describe is back.
     fn prefetch(&self, relations: &[String]) -> Lazy<()> {
@@ -2322,6 +2360,27 @@ fn sorted(mut names: Vec<String>) -> Vec<String> {
     names
 }
 
+/// The keys, column types and collations of another catalog's relations, and what it knows of
+/// functions, added to `into`: those of the tables a view reads and the functions it calls.
+fn merge_keys(into: &mut Catalog, from: Catalog) {
+    for (rel, keys) in from.unique_keys {
+        into.unique_keys.entry(rel).or_insert(keys);
+    }
+    for (rel, keys) in from.partial_keys {
+        into.partial_keys.entry(rel).or_insert(keys);
+    }
+    for (rel, types) in from.column_types {
+        into.column_types.entry(rel).or_insert(types);
+    }
+    for (rel, columns) in from.nondeterministic {
+        into.nondeterministic.entry(rel).or_insert(columns);
+    }
+    into.single_row.extend(from.single_row);
+    into.functions.extend(from.functions);
+    into.set_returning.extend(from.set_returning);
+    into.volatile.extend(from.volatile);
+}
+
 fn build_catalog(operators: &HashSet<String>, rows: CatalogRows) -> Catalog {
     let mut catalog = Catalog { set_returning_operators: operators.clone(), columns: Columns::from_rows(&rows.columns), ..Catalog::default() };
     for row in rows.columns {

diff --git a/oracle/cases/errors/expected.txt b/oracle/cases/errors/expected.txt
@@ -17,5 +17,5 @@ query_to_xml.sql: one () -> { x: xml? }
 select_logs.sql: many (id: int4) -> { id: int4 }
 select_ordered.sql: maybeOne (id: int4) -> { id: int4 }
 select_parts.sql: many (id: int4) -> { id: int4 }
-select_view.sql: many (id: int4) -> { id: int4, email: text }
+select_view.sql: maybeOne (id: int4) -> { id: int4, email: text }
 upsert_user.sql: none (id: int4, email: text, age: int4) -> {  }
diff --git a/oracle/cases/json_schema_checks/expected.txt b/oracle/cases/json_schema_checks/expected.txt
@@ -11,5 +11,5 @@ set_operation.sql: many () -> { doc: json }
 tuple.sql: many () -> { p: json {"type":"array","prefixItems":[{"type":"string"},{"type":"integer"}],"items":false}? }
 union_tagged.sql: maybeOne (id: int4) -> { payload: json {"anyOf":[{"type":"object","properties":{"type":{"enum":["click"]},"x":{"type":"number"}},"required":["type","x"],"additionalProperties":false},{"type":"object","properties":{"type":{"enum":["key"]},"key":{"type":"string"}},"required":["type","key"],"additionalProperties":false}]}? }
 update.sql: maybeOne (payload: json {"anyOf":[{"type":"object","properties":{"type":{"enum":["click"]},"x":{"type":"number"}},"required":["type","x"],"additionalProperties":false},{"type":"object","properties":{"type":{"enum":["key"]},"key":{"type":"string"}},"required":["type","key"],"additionalProperties":false}]}?, id: int4) -> { payload: json {"anyOf":[{"type":"object","properties":{"type":{"enum":["click"]},"x":{"type":"number"}},"required":["type","x"],"additionalProperties":false},{"type":"object","properties":{"type":{"enum":["key"]},"key":{"type":"string"}},"required":["type","key"],"additionalProperties":false}]}? }
-view.sql: many (id: int4) -> { doc: json {"type":"object","properties":{"name":{"type":"string"},"tags":{"type":"array","items":{"type":"string"}},"kind":{"enum":["a","b"]},"size":{"type":["integer","null"]}},"required":["name","kind"],"additionalProperties":true} }
+view.sql: maybeOne (id: int4) -> { doc: json {"type":"object","properties":{"name":{"type":"string"},"tags":{"type":"array","items":{"type":"string"}},"kind":{"enum":["a","b"]},"size":{"type":["integer","null"]}},"required":["name","kind"],"additionalProperties":true} }
 whole_row.sql: one () -> { all_docs: [docs{ id: int4, doc: json {"type":"object","properties":{"name":{"type":"string"},"tags":{"type":"array","items":{"type":"string"}},"kind":{"enum":["a","b"]},"size":{"type":["integer","null"]}},"required":["name","kind"],"additionalProperties":true} }]? }
diff --git a/oracle/cases/subquery_keys/bare_name.sql b/oracle/cases/subquery_keys/bare_name.sql
@@ -0,0 +1 @@
+select * from (select id, title from posts) s where id = :id
diff --git a/oracle/cases/subquery_keys/cast.sql b/oracle/cases/subquery_keys/cast.sql
@@ -0,0 +1 @@
+select * from (select id::text as id, title from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/cross_join.sql b/oracle/cases/subquery_keys/cross_join.sql
@@ -0,0 +1 @@
+select * from (select p.id from posts p cross join users u) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/distinct.sql b/oracle/cases/subquery_keys/distinct.sql
@@ -0,0 +1 @@
+select * from (select distinct author_id from posts) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/distinct_expression.sql b/oracle/cases/subquery_keys/distinct_expression.sql
@@ -0,0 +1 @@
+select * from (select distinct author_id, title || '!' as t from posts) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/distinct_on.sql b/oracle/cases/subquery_keys/distinct_on.sql
@@ -0,0 +1 @@
+select * from (select distinct on (author_id) author_id, id from posts order by author_id, id) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/expected.txt b/oracle/cases/subquery_keys/expected.txt
@@ -0,0 +1,52 @@
+bare_name.sql: maybeOne (id: int8) -> { id: int8, title: text }
+cast.sql: many (id: text) -> { id: text, title: 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 }
+distinct_on.sql: maybeOne (a: int8) -> { author_id: int8, id: int8 }
+expression.sql: many (id: int8) -> { id: int8, title: text }
+filtered_inside.sql: maybeOne (a: int8, id: int8) -> { id: int8, title: text }
+full_join.sql: many (id: int8) -> { id: int8? }
+group_by.sql: maybeOne (a: int8) -> { author_id: int8, n: int8 }
+group_by_ordinal.sql: maybeOne (a: int8) -> { author_id: int8, n: int8 }
+group_by_two.sql: many (a: int8) -> { author_id: int8, title: text, n: int8 }
+group_key_hidden.sql: many (n: int8) -> { author_id: int8, n: int8 }
+join_many.sql: many (id: int8) -> { id: int8, tag: text }
+join_pinned.sql: maybeOne (id: int8) -> { id: int8, title: text, name: text }
+joined_on_key.sql: maybeOne (id: int8) -> { title: text, name: text }
+key_dropped.sql: many (t: text) -> { title: text }
+lateral_pinned.sql: maybeOne (u: int8, p: int8) -> { name: text, title: text }
+lateral_source.sql: many (id: int8) -> { id: int8 }
+left_join_many.sql: many (id: int8) -> { id: int8 }
+left_join_pinned.sql: maybeOne (id: int8) -> { id: int8, title: text, name: text? }
+left_join_subquery.sql: maybeOne (p: int8, id: int8) -> { name: text, title: 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, author_id: int8, id: int8, email: text, name: text, deleted_at: timestamptz? } !! reylo14: Columns 1 and 3 are both named "id", and a row keeps only the last of them. Give one of them another name with `as`.
+null_extended_side.sql: many (id: int8) -> { id: int8?, name: text }
+partial_key_restricted.sql: maybeOne (n: text) -> { id: int8, name: text }
+partial_key_unrestricted.sql: many (n: text) -> { id: int8, name: text }
+recursive.sql: many (id: int8) -> { id: int8 }
+renamed.sql: maybeOne (id: int8) -> { post_id: int8, title: text }
+rollup.sql: many (a: int8) -> { author_id: int8?, n: int8? }
+second_key.sql: maybeOne (e: text) -> { id: int8, email: text }
+self_join_key.sql: maybeOne (id: int8) -> { id: int8, title: text }
+self_join_nonkey.sql: many (id: int8) -> { id: int8 }
+set_returning.sql: many (id: int8) -> { id: int8, x: int4 }
+star.sql: maybeOne (id: int8) -> { id: int8, author_id: int8, title: text }
+subquery.sql: maybeOne (id: int8) -> { id: int8, title: text }
+two_subqueries.sql: maybeOne (id: int8) -> { title: text, name: text }
+union_all.sql: many (id: int8) -> { id: int8 }
+values_list.sql: many (id: int4) -> { id: int4 }
+view.sql: maybeOne (id: int8) -> { id: int8, title: text }
+view_beside_with.sql: maybeOne (id: int8) -> { title: text }
+view_column_list.sql: maybeOne (id: int8) -> { post_id: int8, headline: text }
+view_group_by.sql: maybeOne (a: int8) -> { author_id: int8, n: int8 }
+view_join.sql: maybeOne (id: int8) -> { id: int8, title: text, name: text }
+view_join_many.sql: many (id: int8) -> { id: int8, tag: text }
+view_of_view.sql: maybeOne (id: int8) -> { id: int8, title: text }
+view_other_schema.sql: maybeOne (id: int8) -> { id: int8, title: text }
+window_inside.sql: maybeOne (id: int8) -> { id: int8, rn: int8 }
+with_joined.sql: maybeOne (u: int8, id: int8) -> { title: text, name: text }
+with_materialized.sql: maybeOne (id: int8) -> { id: int8, title: text }
+with_query.sql: maybeOne (id: int8) -> { id: int8, title: text }
diff --git a/oracle/cases/subquery_keys/expression.sql b/oracle/cases/subquery_keys/expression.sql
@@ -0,0 +1 @@
+select * from (select id + 0 as id, title from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/filtered_inside.sql b/oracle/cases/subquery_keys/filtered_inside.sql
@@ -0,0 +1 @@
+select * from (select id, title from posts where author_id = :a) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/full_join.sql b/oracle/cases/subquery_keys/full_join.sql
@@ -0,0 +1 @@
+select * from (select p.id from posts p full join users u on u.id = p.author_id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/group_by.sql b/oracle/cases/subquery_keys/group_by.sql
@@ -0,0 +1 @@
+select * from (select author_id, count(*) as n from posts group by author_id) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/group_by_ordinal.sql b/oracle/cases/subquery_keys/group_by_ordinal.sql
@@ -0,0 +1 @@
+select * from (select author_id, count(*) as n from posts group by 1) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/group_by_two.sql b/oracle/cases/subquery_keys/group_by_two.sql
@@ -0,0 +1 @@
+select * from (select author_id, title, count(*) as n from posts group by author_id, title) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/group_key_hidden.sql b/oracle/cases/subquery_keys/group_key_hidden.sql
@@ -0,0 +1 @@
+select * from (select author_id, count(*) as n from posts group by author_id) s where s.n = :n
diff --git a/oracle/cases/subquery_keys/join_many.sql b/oracle/cases/subquery_keys/join_many.sql
@@ -0,0 +1 @@
+select * from (select p.id, t.tag from posts p join tags t on t.post_id = p.id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/join_pinned.sql b/oracle/cases/subquery_keys/join_pinned.sql
@@ -0,0 +1 @@
+select * from (select p.id, p.title, u.name from posts p join users u on u.id = p.author_id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/joined_on_key.sql b/oracle/cases/subquery_keys/joined_on_key.sql
@@ -0,0 +1 @@
+select p.title, s.name from posts p join (select id, name from users) s on s.id = p.author_id where p.id = :id
diff --git a/oracle/cases/subquery_keys/key_dropped.sql b/oracle/cases/subquery_keys/key_dropped.sql
@@ -0,0 +1 @@
+select * from (select title from posts) s where s.title = :t
diff --git a/oracle/cases/subquery_keys/lateral_pinned.sql b/oracle/cases/subquery_keys/lateral_pinned.sql
@@ -0,0 +1 @@
+select u.name, s.title from users u cross join lateral (select id, title from posts where author_id = u.id) s where u.id = :u and s.id = :p
diff --git a/oracle/cases/subquery_keys/lateral_source.sql b/oracle/cases/subquery_keys/lateral_source.sql
@@ -0,0 +1 @@
+select s.id from users u cross join lateral (select p.id, u.id as uid from posts p) s where u.id = s.uid and s.id = :id
diff --git a/oracle/cases/subquery_keys/left_join_many.sql b/oracle/cases/subquery_keys/left_join_many.sql
@@ -0,0 +1 @@
+select * from (select u.id from users u left join posts p on p.author_id = u.id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/left_join_pinned.sql b/oracle/cases/subquery_keys/left_join_pinned.sql
@@ -0,0 +1 @@
+select * from (select p.id, p.title, u.name from posts p left join users u on u.id = p.author_id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/left_join_subquery.sql b/oracle/cases/subquery_keys/left_join_subquery.sql
@@ -0,0 +1 @@
+select u.name, s.title from users u left join (select id, title from posts) s on s.id = :p where u.id = :id
diff --git a/oracle/cases/subquery_keys/limit_inside.sql b/oracle/cases/subquery_keys/limit_inside.sql
@@ -0,0 +1 @@
+select * from (select id, title from posts order by id limit 10 offset 5) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/nested.sql b/oracle/cases/subquery_keys/nested.sql
@@ -0,0 +1 @@
+select * from (select * from (select id, title from posts) a) b where b.id = :id
diff --git a/oracle/cases/subquery_keys/not_a_key.sql b/oracle/cases/subquery_keys/not_a_key.sql
@@ -0,0 +1 @@
+select * from (select id, author_id from posts) s join users u on u.id = s.author_id where u.email = :e
diff --git a/oracle/cases/subquery_keys/null_extended_side.sql b/oracle/cases/subquery_keys/null_extended_side.sql
@@ -0,0 +1 @@
+select * from (select p.id, u.name from users u left join posts p on p.author_id = u.id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/partial_key_restricted.sql b/oracle/cases/subquery_keys/partial_key_restricted.sql
@@ -0,0 +1 @@
+select * from (select id, name from users where deleted_at is null) s where s.name = :n
diff --git a/oracle/cases/subquery_keys/partial_key_unrestricted.sql b/oracle/cases/subquery_keys/partial_key_unrestricted.sql
@@ -0,0 +1 @@
+select * from (select id, name from users) s where s.name = :n
diff --git a/oracle/cases/subquery_keys/recursive.sql b/oracle/cases/subquery_keys/recursive.sql
@@ -0,0 +1 @@
+with recursive r (id, depth) as (select id, 0 from posts union all select id, depth + 1 from r where depth < 2) select id from r where r.id = :id
diff --git a/oracle/cases/subquery_keys/renamed.sql b/oracle/cases/subquery_keys/renamed.sql
@@ -0,0 +1 @@
+select * from (select id as post_id, title from posts) s where s.post_id = :id
diff --git a/oracle/cases/subquery_keys/rollup.sql b/oracle/cases/subquery_keys/rollup.sql
@@ -0,0 +1 @@
+select * from (select author_id, count(*) as n from posts group by rollup (author_id)) s where s.author_id = :a
diff --git a/oracle/cases/subquery_keys/second_key.sql b/oracle/cases/subquery_keys/second_key.sql
@@ -0,0 +1 @@
+select * from (select id, email from users) s where s.email = :e
diff --git a/oracle/cases/subquery_keys/self_join_key.sql b/oracle/cases/subquery_keys/self_join_key.sql
@@ -0,0 +1 @@
+select * from (select a.id, b.title from posts a join posts b on b.id = a.id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/self_join_nonkey.sql b/oracle/cases/subquery_keys/self_join_nonkey.sql
@@ -0,0 +1 @@
+select * from (select a.id from posts a join posts b on b.author_id = a.author_id) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/set_returning.sql b/oracle/cases/subquery_keys/set_returning.sql
@@ -0,0 +1 @@
+select * from (select id, unnest(array[1, 2]) as x from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/setup.sql b/oracle/cases/subquery_keys/setup.sql
@@ -0,0 +1,17 @@
+-- A subquery, a WITH query or a view is pinned to one row by a unique key of its rows, as a table
+-- is by its index: a key of a FROM item the select list keeps unchanged, every other item pinned
+-- to one row per row of it, or the select list's GROUP BY or DISTINCT. Anything that can repeat a
+-- row leaves no key.
+create table users (id bigint primary key, email text not null unique, name text not null, deleted_at timestamptz);
+create table posts (id bigint primary key, author_id bigint not null references users (id), title text not null);
+create table tags (post_id bigint not null references posts (id), tag text not null, primary key (post_id, tag));
+create unique index users_live_name on users (name) where deleted_at is null;
+create schema archive;
+create table archive.posts (id bigint primary key, title text not null);
+create view public_posts as select id, title from posts;
+create view renamed_posts (post_id, headline) as select id, title from posts;
+create view post_authors as select p.id, p.title, u.name from posts p join users u on u.id = p.author_id;
+create view author_counts as select author_id, count(*) as n from posts group by author_id;
+create view recent_posts as select * from public_posts where id > 10;
+create view archived as select id, title from archive.posts;
+create view post_tags as select p.id, t.tag from posts p join tags t on t.post_id = p.id;
diff --git a/oracle/cases/subquery_keys/star.sql b/oracle/cases/subquery_keys/star.sql
@@ -0,0 +1 @@
+select * from (select * from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/subquery.sql b/oracle/cases/subquery_keys/subquery.sql
@@ -0,0 +1 @@
+select * from (select id, title from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/two_subqueries.sql b/oracle/cases/subquery_keys/two_subqueries.sql
@@ -0,0 +1 @@
+select a.title, b.name from (select id, title, author_id from posts) a join (select id, name from users) b on b.id = a.author_id where a.id = :id
diff --git a/oracle/cases/subquery_keys/union_all.sql b/oracle/cases/subquery_keys/union_all.sql
@@ -0,0 +1 @@
+select * from (select id from posts union all select id from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/values_list.sql b/oracle/cases/subquery_keys/values_list.sql
@@ -0,0 +1 @@
+select * from (values (1), (1)) s (id) where s.id = :id
diff --git a/oracle/cases/subquery_keys/view.sql b/oracle/cases/subquery_keys/view.sql
@@ -0,0 +1 @@
+select * from public_posts where id = :id
diff --git a/oracle/cases/subquery_keys/view_beside_with.sql b/oracle/cases/subquery_keys/view_beside_with.sql
@@ -0,0 +1 @@
+with posts as (select 1::bigint as id) select v.title from public_posts v where v.id = :id
diff --git a/oracle/cases/subquery_keys/view_column_list.sql b/oracle/cases/subquery_keys/view_column_list.sql
@@ -0,0 +1 @@
+select * from renamed_posts where post_id = :id
diff --git a/oracle/cases/subquery_keys/view_group_by.sql b/oracle/cases/subquery_keys/view_group_by.sql
@@ -0,0 +1 @@
+select * from author_counts where author_id = :a
diff --git a/oracle/cases/subquery_keys/view_join.sql b/oracle/cases/subquery_keys/view_join.sql
@@ -0,0 +1 @@
+select * from post_authors where id = :id
diff --git a/oracle/cases/subquery_keys/view_join_many.sql b/oracle/cases/subquery_keys/view_join_many.sql
@@ -0,0 +1 @@
+select * from post_tags where id = :id
diff --git a/oracle/cases/subquery_keys/view_of_view.sql b/oracle/cases/subquery_keys/view_of_view.sql
@@ -0,0 +1 @@
+select * from recent_posts where id = :id
diff --git a/oracle/cases/subquery_keys/view_other_schema.sql b/oracle/cases/subquery_keys/view_other_schema.sql
@@ -0,0 +1 @@
+select * from archived where id = :id
diff --git a/oracle/cases/subquery_keys/window_inside.sql b/oracle/cases/subquery_keys/window_inside.sql
@@ -0,0 +1 @@
+select * from (select id, row_number() over (order by id) as rn from posts) s where s.id = :id
diff --git a/oracle/cases/subquery_keys/with_joined.sql b/oracle/cases/subquery_keys/with_joined.sql
@@ -0,0 +1 @@
+with s as (select id, title from posts) select s.title, u.name from s join users u on u.id = :u where s.id = :id
diff --git a/oracle/cases/subquery_keys/with_materialized.sql b/oracle/cases/subquery_keys/with_materialized.sql
@@ -0,0 +1 @@
+with s as materialized (select id, title from posts) select * from s where s.id = :id
diff --git a/oracle/cases/subquery_keys/with_query.sql b/oracle/cases/subquery_keys/with_query.sql
@@ -0,0 +1 @@
+with s as (select id, title from posts) select * from s where s.id = :id
diff --git a/packages/postgres/engine.wasm b/packages/postgres/engine.wasm
diff --git a/site/content/docs/postgres.mdx b/site/content/docs/postgres.mdx
@@ -626,7 +626,7 @@ Reylo infers how many rows a query returns, and types `.run()`'s result to match
 | `.run()` returns | `cardinality` | When the query |
 | --- | --- | --- |
 | `Row` | `one` | is an aggregate without a `group by`, or inserts one row with `values (…)` and has `returning`, an `on conflict do update` included |
-| `Row \| null` | `maybeOne` | matches every column of a primary key or unique index with `=`, through joins on the joined table's key; has `limit 1` or `fetch first 1 row only`; is a `distinct` or `group by` over values the `where` pins; is an `update` or `delete` by key with `returning`; or inserts one row with `on conflict do nothing` and `returning` |
+| `Row \| null` | `maybeOne` | matches every column of a primary key or unique index with `=`, through joins on the joined table's key, and through a subquery, a `with` query or a view that keeps the key; has `limit 1` or `fetch first 1 row only`; is a `distinct` or `group by` over values the `where` pins; is an `update` or `delete` by key with `returning`; or inserts one row with `on conflict do nothing` and `returning` |
 | `void` | `none` | is an `insert`, `update`, `delete` or `merge` without `returning` |
 | `Row[]` | `many` | is anything else |
 
@@ -669,6 +669,27 @@ where slug = :slug and not deleted
 
 </Inferred>
 
+**Subqueries, `with` queries and views**
+
+A subquery, a `with` query or a view has a key when it selects a table's key unchanged and joins only one row per row of that table, or when its `group by` or `distinct` lists the columns.
+
+<Inferred>
+
+```sql file="pg_card_subquery.sql"
+select id, total
+from (
+  select o.id, o.total, u.name
+  from orders o
+  join users u on u.id = o.user_id
+  where o.paid
+) paid_orders
+where id = :id::bigint
+```
+
+</Inferred>
+
+A `union all`, a set-returning function such as `unnest` in the select list, or a key column passed through an expression or a cast leaves no key.
+
 **What doesn't count**
 
 Part of a composite key doesn't count, and neither does a `having` or an `insert … select … returning`.