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
@@ -962,6 +962,9 @@ pub enum ItemRead {
     Table,
     /// One result column: a bare reference to the column of this name of item `item`.
     Column { item: usize, column: String },
+    /// One result column: `coalesce(c, '[]')` of such a reference `c`, which is that column's
+    /// array or an empty one, and never NULL.
+    Defaulted { item: usize, column: String },
     /// `*` or `item.*`: every column of item `item` in order, or of something else (`None`).
     Star { item: Option<usize> },
 }
@@ -989,7 +992,7 @@ pub struct ItemQuery {
 /// a `(row).*` hides how many columns a target is.
 pub fn item_reads(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
     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));
+    let read = |i: usize| reads.iter().any(|r| matches!(r, ItemRead::Column { item, .. } | ItemRead::Defaulted { item, .. } | ItemRead::Star { item: Some(item) } if *item == i));
     (0..queries.len()).any(read).then_some((reads, queries))
 }
 
@@ -1020,7 +1023,21 @@ fn reads_here(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
     let mut reads = Vec::with_capacity(select.target_list.len());
     for target in &select.target_list {
         let Some(NodeEnum::ResTarget(rt)) = target.node.as_ref() else { return None };
+        // A bare reference to a column of an item with a statement of its own.
+        let column_of = |c: &protobuf::ColumnRef| match strings(&c.fields).as_deref() {
+            Some([name, column]) => items.named(name).map(|item| (item, column.clone())),
+            // A bare name says which item it reads only when there is one.
+            Some([column]) => items.only().map(|item| (item, column.clone())),
+            _ => None,
+        };
         let read = match rt.val.as_deref()?.node.as_ref() {
+            Some(NodeEnum::CoalesceExpr(c)) => match c.args.as_slice() {
+                [first, default] if is_empty_array(default) => match first.node.as_ref() {
+                    Some(NodeEnum::ColumnRef(c)) => column_of(c).map_or(ItemRead::Other, |(item, column)| ItemRead::Defaulted { item, column }),
+                    _ => ItemRead::Other,
+                },
+                _ => ItemRead::Other,
+            },
             // `(row).*` is as many columns as the row's type has.
             Some(NodeEnum::AIndirection(i)) if i.indirection.iter().any(|n| matches!(n.node, Some(NodeEnum::AStar(_)))) => return None,
             Some(NodeEnum::ColumnRef(c)) => match c.fields.split_last() {
@@ -1050,6 +1067,18 @@ fn reads_here(sql: &str) -> Option<(Vec<ItemRead>, Vec<ItemQuery>)> {
     Some((reads, items.queries))
 }
 
+/// `'[]'`, as JSON text or cast to `json` or `jsonb`.
+fn is_empty_array(node: &Node) -> bool {
+    match node.node.as_ref() {
+        Some(NodeEnum::AConst(c)) => matches!(&c.val, Some(protobuf::a_const::Val::Sval(s)) if s.sval == "[]"),
+        Some(NodeEnum::TypeCast(t)) => {
+            let json = t.type_name.as_ref().and_then(|n| last_string(&n.names)).is_some_and(|n| n == "json" || n == "jsonb");
+            json && t.arg.as_deref().is_some_and(is_empty_array)
+        }
+        _ => false,
+    }
+}
+
 /// The FROM items of one SELECT, through its joins.
 struct Items<'a> {
     version: i32,
@@ -2195,6 +2224,18 @@ mod tests {
         assert_eq!(target_reads("SELECT id FROM users UNION SELECT id FROM posts"), None);
     }
 
+    #[test]
+    fn an_empty_array_default_of_a_column_read_from_a_with_query_reads_it() {
+        let defaulted = |item: usize, column: &str| ItemRead::Defaulted { item, column: column.into() };
+        let (reads, _) = item_reads("WITH a AS (SELECT jsonb_agg(1) AS xs) SELECT coalesce(a.xs, '[]'), coalesce(xs, '[]'::jsonb) FROM a").unwrap();
+        assert_eq!(reads, vec![defaulted(0, "xs"), defaulted(0, "xs")]);
+        // Any other default, a third argument or an expression is no read of the column.
+        let (reads, _) = item_reads("WITH a AS (SELECT jsonb_agg(1) AS xs) SELECT xs, coalesce(xs, '{}'), coalesce(xs, '[]', '[]'), coalesce(xs || '[]', '[]') FROM a").unwrap();
+        assert_eq!(reads, vec![ItemRead::Column { item: 0, column: "xs".into() }, ItemRead::Other, ItemRead::Other, ItemRead::Other]);
+        // A defaulted read is a read of the item.
+        assert!(item_reads("WITH a AS (SELECT jsonb_agg(1) AS xs) SELECT coalesce(a.xs, '[]') FROM a").is_some());
+    }
+
     #[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() };

diff --git a/engine/postgres/src/jsonshape.rs b/engine/postgres/src/jsonshape.rs
@@ -67,6 +67,19 @@ pub struct Outcome {
     /// Whether the probe's own analysis saw an outer join, which is the one thing that can null
     /// a whole row under `to_jsonb(alias)`.
     pub outer_join: bool,
+    /// Per column, the JSON a WITH query or a derived table builds in it, for one the probe reads
+    /// straight from such an item: it stands for the column's type. Empty where there is none.
+    pub built: Vec<Option<Built>>,
+}
+
+/// The JSON a column read from a WITH query or a derived table holds, as that item's own
+/// statement builds it: its type, whether the item's result never has it NULL, and the paths
+/// inside it whose type the engine could not derive.
+#[derive(Debug, Clone)]
+pub struct Built {
+    pub ty: Type,
+    pub non_null: bool,
+    pub unresolved: Vec<String>,
 }
 
 /// A resolved shape: the column's type, whether the column can be SQL NULL, and the paths
@@ -601,8 +614,19 @@ impl ResolveCtx<'_> {
                 let (of, items_non_null) = self.node(items, &join(path, "[]"), unresolved);
                 (Type::List { of: Box::new(of), nullable_items: !items_non_null }, *non_null)
             }
-            Shape::Column { probe, index } => match self.probes.get(*probe).and_then(|o| o.as_ref()).and_then(|o| o.columns.get(*index)) {
-                Some(col) if col.type_oid != oid::UNKNOWN => {
+            Shape::Column { probe, index } => match self.probes.get(*probe).and_then(|o| o.as_ref()).and_then(|o| Some((o.columns.get(*index)?, o.built.get(*index).and_then(Option::as_ref)))) {
+                // JSON a WITH query or a derived table builds, NULL where the item's row has it so
+                // or a join here nulls the item. A path the item could not type goes under this
+                // one, a path inside its array (`[].tags`) as this module writes its own.
+                Some((col, Some(built))) => {
+                    unresolved.extend(built.unresolved.iter().map(|p| match p.as_str() {
+                        WHOLE => display_path(path),
+                        p if p.starts_with("[]") => display_path(&format!("{path}{p}")),
+                        p => display_path(&join(path, p)),
+                    }));
+                    (built.ty.clone(), built.non_null && !col.nullable)
+                }
+                Some((col, None)) if col.type_oid != oid::UNKNOWN => {
                     let ty = if col.whole_row { self.registry.render_row(col.type_oid) } else { self.registry.render(col.type_oid) };
                     (crate::postgres::with_document(crate::postgres::with_values(ty, col.values.as_deref()), col.document.as_ref()), !col.nullable)
                 }
@@ -662,9 +686,12 @@ fn join(path: &str, key: &str) -> String {
     }
 }
 
+/// The path [`display_path`] gives the value itself.
+const WHOLE: &str = "the whole value";
+
 fn display_path(path: &str) -> String {
     if path.is_empty() {
-        "the whole value".to_string()
+        WHOLE.to_string()
     } else {
         path.to_string()
     }
@@ -943,7 +970,7 @@ mod tests {
     fn a_scalar_subquery_that_may_find_no_row_is_nullable_whatever_its_value() {
         let registry = TypeRegistry::with_builtins();
         let found = vec![column("a0", oid::TEXT, "text", false)];
-        let resolved = |sql: &str| resolve(&shapes(sql).columns[0].1, &[Some(Outcome { columns: found.clone(), outer_join: false })], &registry, &[]);
+        let resolved = |sql: &str| resolve(&shapes(sql).columns[0].1, &[Some(Outcome { columns: found.clone(), outer_join: false, built: Vec::new() })], &registry, &[]);
         // Whether the column is nullable, and whether the first field of the object it is.
         let whole = |sql: &str| !resolved(sql).non_null;
         let field = |sql: &str| match resolved(sql).ty {
@@ -1150,6 +1177,7 @@ mod tests {
                 column("a2", oid::INT4, "int4", true),
             ],
             outer_join: false,
+            built: Vec::new(),
         };
         let r = resolve(&p.columns[0].1, &[Some(outcome)], &registry, &[]);
         assert!(r.non_null);
@@ -1183,6 +1211,7 @@ mod tests {
         let outcome = Outcome {
             columns: vec![column("a0", oid::UUID, "uuid", false), column("a1", oid::UNKNOWN, "unknown", true)],
             outer_join: false,
+            built: Vec::new(),
         };
         let r = resolve(&p.columns[0].1, &[Some(outcome)], &registry, &[]);
         assert_eq!(r.unresolved, vec!["n"]);
@@ -1201,6 +1230,7 @@ mod tests {
         let outcome = Outcome {
             columns: vec![column("a0", oid::INT8, "int8", false), column("a1", NUMERIC, "numeric", false), column("a2", BYTEA, "bytea", false)],
             outer_join: false,
+            built: Vec::new(),
         };
         let r = resolve(&p.columns[0].1, &[Some(outcome)], &registry, &[]);
         assert!(r.unresolved.is_empty());
@@ -1217,11 +1247,22 @@ mod tests {
         let registry = TypeRegistry::with_builtins();
         let p = shapes("SELECT to_jsonb(u) AS author FROM users u");
         let columns = vec![column("id", oid::UUID, "uuid", false), column("name", oid::TEXT, "text", true)];
-        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns: columns.clone(), outer_join: false })], &registry, &[]);
+        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns: columns.clone(), outer_join: false, built: Vec::new() })], &registry, &[]);
         assert!(r.non_null);
         assert_eq!(r.ty, Type::Object { fields: vec![field("id", scalar("uuid"), false), field("name", scalar("text"), true)] });
         // An outer join in the probe's own analysis is what can null the whole row.
-        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns, outer_join: true })], &registry, &[]);
+        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns, outer_join: true, built: Vec::new() })], &registry, &[]);
         assert!(!r.non_null);
     }
+
+    #[test]
+    fn a_path_an_item_could_not_type_goes_under_the_path_that_reads_it() {
+        let registry = TypeRegistry::with_builtins();
+        let p = shapes("SELECT jsonb_build_object('posts', ps.posts) AS author FROM ps");
+        let item = Type::List { of: Box::new(Type::Object { fields: vec![field("tags", Type::Unknown, true)] }), nullable_items: false };
+        let built = Built { ty: item, non_null: true, unresolved: vec!["[].tags".into(), "[]".into(), WHOLE.into()] };
+        let outcome = Outcome { columns: vec![column("a0", oid::JSONB, "jsonb", false)], outer_join: false, built: vec![Some(built)] };
+        let r = resolve(&p.columns[0].1, &[Some(outcome)], &registry, &[]);
+        assert_eq!(r.unresolved, vec!["posts[].tags", "posts[]", "posts"]);
+    }
 }