colinhacks · Oct 22, 2026 · Oct 22, 2026 · Oct 22, 2026 · Oct 22, 2026 · Oct 22, 2026
diff --git a/engine/core/src/analysis.rs b/engine/core/src/analysis.rs
@@ -105,6 +105,29 @@ pub fn odd_pairs(sql: &str, lexed: &Lexed, names: &[&str]) -> Vec<Diagnostic> {
     found
 }
 
+/// Two result columns of one name. A driver builds each row as an object keyed by name, so it
+/// keeps the last of them, and nothing a type says of the first is true of the row. One diagnostic
+/// at each later column of a name: at the bytes `at` gives for that column (its index in
+/// `analysis.columns`), which the database's own parse knows, else on the statement's first word.
+pub fn clashes(sql: &str, analysis: &mut Analysis, at: &dyn Fn(usize) -> Option<(usize, usize)>) {
+    let mut found = Vec::new();
+    for (i, column) in analysis.columns.iter().enumerate() {
+        if let Some(first) = analysis.columns[..i].iter().position(|c| c.name == column.name) {
+            let (start, length) = at(i).unwrap_or_else(|| first_word(sql));
+            let message = format!("Columns {} and {} are both named \"{}\", and a row keeps only the last of them. Give one of them another name with `as`.", first + 1, i + 1, column.name);
+            found.push(diag(message, start, length, 14));
+        }
+    }
+    analysis.diagnostics.extend(found);
+}
+
+/// The statement's first word, past any whitespace.
+fn first_word(sql: &str) -> (usize, usize) {
+    let start = sql.len() - sql.trim_start().len();
+    let length = sql[start..].bytes().take_while(|b| b.is_ascii_alphanumeric() || *b == b'_').count();
+    (start, length.max(1))
+}
+
 /// A diagnostic at least one byte long.
 pub fn diag(message: impl Into<String>, start: usize, length: usize, code: u32) -> Diagnostic {
     Diagnostic { message: message.into(), start, length: length.max(1), code }
@@ -144,6 +167,7 @@ pub fn bare_of(sql: &str, dialect: Dialect, diagnostics: Vec<Diagnostic>) -> Ana
 #[cfg(test)]
 mod tests {
     use super::*;
+    use crate::api::Column;
 
     #[test]
     fn a_json_object_of_an_odd_count_of_arguments_is_an_error_at_its_name() {
@@ -176,4 +200,18 @@ mod tests {
         assert_eq!(at("select json_build_object('a', café, 'b')").len(), 1);
         assert_eq!(at("select json_build_object(variadicé, 'a', 1)").len(), 1);
     }
+
+    #[test]
+    fn two_columns_of_one_name_are_an_error_at_the_later() {
+        let mut a = bare_of("select a.x, b.x, a.x from a, b", Dialect::Postgres, Vec::new());
+        a.columns = ["x", "x", "x"].iter().map(|n| Column { name: n.to_string(), ty: Type::Unknown, nullable: true, type_name: String::new(), comment: None }).collect();
+        clashes("  select a.x, b.x, a.x from a, b", &mut a, &|i| (i == 1).then_some((14, 1)));
+        assert_eq!(a.diagnostics.iter().map(|d| (d.code, d.start, d.length)).collect::<Vec<_>>(), [(14, 14, 1), (14, 2, 6)]);
+        assert_eq!(a.diagnostics[0].message, "Columns 1 and 2 are both named \"x\", and a row keeps only the last of them. Give one of them another name with `as`.");
+        assert!(a.diagnostics[1].message.starts_with("Columns 1 and 3 are both named"));
+        let mut b = bare_of("select 1 as x, 2 as y", Dialect::Postgres, Vec::new());
+        b.columns = ["x", "y"].iter().map(|n| Column { name: n.to_string(), ty: Type::Unknown, nullable: true, type_name: String::new(), comment: None }).collect();
+        clashes("select 1 as x, 2 as y", &mut b, &|_| None);
+        assert!(b.diagnostics.is_empty());
+    }
 }
diff --git a/engine/core/src/schema.rs b/engine/core/src/schema.rs
@@ -154,9 +154,13 @@ fn scalar(name: &str, opts: SchemaOptions) -> Value {
         "int2" | "int4" => json!({"type": "integer", "format": "int32"}),
         "int8" if opts.in_json || opts.int8_as_number => json!({"type": "integer", "format": "int64"}),
         "int8" => json!({"type": "string", "format": "int64"}),
+        // JSON has no number for a non-finite value, so Postgres writes a float's or a decimal's
+        // inside a document as text.
+        "float4" if opts.in_json => json!({"anyOf": [{"type": "number", "format": "float"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}),
+        "float8" if opts.in_json => json!({"anyOf": [{"type": "number", "format": "double"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}),
         "float4" => json!({"type": "number", "format": "float"}),
         "float8" => json!({"type": "number", "format": "double"}),
-        "numeric" if opts.in_json => json!({"type": "number", "format": "decimal"}),
+        "numeric" if opts.in_json => json!({"anyOf": [{"type": "number", "format": "decimal"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}),
         "numeric" => json!({"type": "string", "format": "decimal"}),
         "text" | "varchar" | "bpchar" | "name" => json!({"type": "string"}),
         "uuid" => json!({"type": "string", "format": "uuid"}),
@@ -165,6 +169,8 @@ fn scalar(name: &str, opts: SchemaOptions) -> Value {
         "time" => json!({"type": "string", "format": "time"}),
         // Postgres writes its own hex inside a document.
         "bytea" if opts.in_json => json!({"type": "string", "format": "hex"}),
+        // A row whose columns were not read (a subquery's) is an object of them inside a document.
+        "record" if opts.in_json => json!({"type": "object"}),
         // `format: byte` is the table's word; `contentEncoding` is the JSON Schema 2020-12
         // spelling of the same base64, kept beside it for validators.
         "bytea" => json!({"type": "string", "format": "byte", "contentEncoding": "base64"}),
@@ -230,7 +236,10 @@ mod tests {
     #[test]
     fn inside_json_the_database_spells_int8_numeric_and_bytea() {
         assert_eq!(s("int8").json_schema(IN_JSON), json!({"type": "integer", "format": "int64"}));
-        assert_eq!(s("numeric").json_schema(IN_JSON), json!({"type": "number", "format": "decimal"}));
+        // A non-finite float or decimal is text inside a document: `{"f": "Infinity"}`.
+        assert_eq!(s("numeric").json_schema(IN_JSON), json!({"anyOf": [{"type": "number", "format": "decimal"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}));
+        assert_eq!(s("float8").json_schema(IN_JSON), json!({"anyOf": [{"type": "number", "format": "double"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}));
+        assert_eq!(s("float8").json_schema(WIRE), json!({"type": "number", "format": "double"}));
         assert_eq!(s("bytea").json_schema(IN_JSON), json!({"type": "string", "format": "hex"}));
         assert_eq!(s("uuid").json_schema(IN_JSON), s("uuid").json_schema(WIRE));
         assert_eq!(s("timestamptz").json_schema(IN_JSON), s("timestamptz").json_schema(WIRE));
@@ -300,7 +309,7 @@ mod tests {
         assert_eq!(post.json_schema(INT8N), expected);
         assert_eq!(post.json_schema(IN_JSON), expected);
         let list = Type::List { of: Box::new(s("numeric")), nullable_items: true };
-        assert_eq!(list.json_schema(WIRE), json!({"type": "array", "items": {"anyOf": [{"type": "number", "format": "decimal"}, {"type": "null"}]}}));
+        assert_eq!(list.json_schema(WIRE), json!({"type": "array", "items": {"anyOf": [{"anyOf": [{"type": "number", "format": "decimal"}, {"enum": ["NaN", "Infinity", "-Infinity"]}]}, {"type": "null"}]}}));
         let texts = Type::List { of: Box::new(s("text")), nullabl
BD0
e_items: true };
         assert_eq!(texts.json_schema(WIRE), json!({"type": "array", "items": {"type": ["string", "null"]}}));
         let posts = Type::List { of: Box::new(post), nullable_items: false };

diff --git a/engine/mysql/src/mysql.rs b/engine/mysql/src/mysql.rs
@@ -400,7 +400,7 @@ pub fn analyze(sql: &str, lexed: &Lexed, text: Option<MysqlText>, host: &Host) -
         None => ("many".to_string(), Some(misread.unwrap_or_else(|| "the engine could not parse the statement, so nothing bounds its rows".into()))),
     };
     let rewritten = vectors_as_text(rewritten, &raw);
-    Some(Analysis {
+    let mut analysis = Analysis {
         kind: kind.to_string(),
         params,
         columns,
@@ -416,7 +416,9 @@ pub fn analyze(sql: &str, lexed: &Lexed, text: Option<MysqlText>, host: &Host) -
         // otherwise than the parser (`||`, a spaced `count (`): which tables a FROM names does not
         // turn on that.
         tables: read.as_ref().map(crate::mysql_params::tables),
-    })
+    };
+    crate::analysis::clashes(sql, &mut analysis, &|_| None);
+    Some(analysis)
 }
 
 /// How deep in derived tables the engine reads a derived table's columns: each level is a round

diff --git a/engine/postgres/src/jsonshape.rs b/engine/postgres/src/jsonshape.rs
@@ -301,13 +301,11 @@ impl Builder<'_> {
                     // A bare name is a column before it is a FROM item, of the innermost query
                     // that has one of that name: it is the item's whole row only when no FROM in
                     // scope has such a column, for certain.
-                    match f.args.first().and_then(bare_alias) {
-                        // The old or the new row of a RETURNING is the target relation's row, or
-                        // NULL where the statement has no such row.
-                        Some(alias) if f.args.len() == 1 && self.no_column(&alias) => match self.rows.as_ref().and_then(|r| r.whole(&alias)).map(|(target, missing)| (target.to_string(), missing)) {
-                            Some((target, true)) => force_nullable(self.row(&target)),
-                            Some((target, false)) => self.row(&target),
-                            None => self.row(&alias),
+                    // `u.*` says the whole row outright.
+                    match f.args.first() {
+                        Some(arg) if f.args.len() == 1 => match star_alias(arg).or_else(|| bare_alias(arg).filter(|alias| self.no_column(alias))) {
+                            Some(alias) => self.whole(&alias),
+                            None => Shape::Unknown,
                         },
                         _ => Shape::Unknown,
                     }
@@ -353,6 +351,10 @@ impl Builder<'_> {
                     }
                 }
             }
+            // `u.*` as a value, a function's argument (`jsonb_agg(u.*)`, `jsonb_build_object('u',
+            // u.*)`), is the whole row, as `u` is: never the row's columns one by one, which is
+            // what it would be in a probe's select list.
+            Some(NodeEnum::ColumnRef(_)) if star_alias(expr).is_some() => self.whole(&star_alias(expr).expect("a star alias")),
             Some(NodeEnum::TypeCast(t)) => match (t.arg.as_deref(), casts_to_json(t)) {
                 (Some(arg), true) => self.walk(arg),
                 _ => self.leaf(expr),
@@ -387,8 +389,22 @@ impl Builder<'_> {
         }
     }
 
+    /// A whole row by its alias: the old or the new row of a RETURNING is the target relation's
+    /// row, or NULL where the statement has no such row.
+    fn whole(&mut self, alias: &str) -> Shape {
+        match self.rows.as_ref().and_then(|r| r.whole(alias)).map(|(target, missing)| (target.to_string(), missing)) {
+            Some((target, true)) => force_nullable(self.row(&target)),
+            Some((target, false)) => self.row(&target),
+            None => self.row(alias),
+        }
+    }
+
     /// One value expression, as a column of the probe for the constructor's own `FROM` context.
     fn leaf(&mut self, expr: &Node) -> Shape {
+        // A star expands to several columns of the probe, so it cannot be one of them.
+        if is_star(expr) {
+            return Shape::Unknown;
+        }
         if let Some(n) = param_number(expr) {
             return Shape::Param(n);
         }
@@ -627,6 +643,14 @@ struct ResolveCtx<'a> {
 }
 
 impl ResolveCtx<'_> {
+    /// A probe's column as a type: what the catalog says of it, with what the probe's own
+    /// analysis said of an array it builds (`array[x, null]` holds a NULL element) and of the
+    /// values and the document a CHECK keeps it to, as a result column gets them.
+    fn column(&self, col: &DescribedColumn) -> Type {
+        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(crate::postgres::with_items(ty, col.nullable_items), col.values.as_deref()), col.document.as_ref())
+    }
+
     /// One shape as (type, whether the value can never be null).
     fn node(&self, shape: &Shape, path: &str, unresolved: &mut Vec<String>) -> (Type, bool) {
         match shape {
@@ -656,10 +680,7 @@ impl ResolveCtx<'_> {
                     }));
                     (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)
-                }
+                Some((col, None)) if col.type_oid != oid::UNKNOWN => (self.column(col), !col.nullable),
                 _ => {
                     unresolved.push(display_path(path));
                     (Type::Unknown, false)
@@ -675,13 +696,14 @@ impl ResolveCtx<'_> {
                                 unresolved.push(display_path(&join(path, &col.name)));
                                 Type::Unknown
                             } else {
-                                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())
+                                self.column(col)
                             };
                             Field { name: col.name.clone(), ty, nullable: col.nullable }
                         })
                         .collect();
-                    (Type::Object { fields }, !outcome.outer_join)
+                    // An outer join can null the whole row, which is then every column NULL: a
+                    // column the probe proved non-null says the row is there.
+                    (Type::Object { fields }, !outcome.outer_join || outcome.columns.iter().any(|c| !c.nullable))
                 }
                 None => {
                     unresolved.push(display_path(path));
@@ -816,6 +838,14 @@ fn casts_to_json(t: &protobuf::TypeCast) -> bool {
         .is_some_and(|n| n == "json" || n == "jsonb")
 }
 
+/// The `u` of `u.*`, a whole row by its alias.
+fn star_alias(node: &Node) -> Option<String> {
+    match node.node.as_ref()? {
+        NodeEnum::ColumnRef(c) if c.fields.len() == 2 && is_star(node) => last_string(&c.fields[..1]),
+        _ => None,
+    }
+}
+
 /// A bare `u`, the alias form `to_jsonb` takes for a whole row.
 fn bare_alias(node: &Node) -> Option<String> {
     match node.node.as_ref()? {
@@ -993,6 +1023,19 @@ mod tests {
         let p = shapes("SELECT to_jsonb(u) AS author FROM users u");
         assert_eq!(p.probes[0].sql, "SELECT u.* FROM users u");
         assert_eq!(p.columns[0].1, Shape::Row { probe: 0 });
+        // `u.*` as a function's argument is the whole row too, never the row's first column,
+        // under an aggregate, its FILTER, an object and `to_jsonb` alike.
+        for sql in [
+            "SELECT jsonb_agg(u.*) AS authors FROM users u",
+            "SELECT json_agg(u.*) FILTER (WHERE u.id IS NOT NULL) AS authors FROM users u",
+        ] {
+            let p = shapes(sql);
+            assert_eq!(p.columns[0].1, Shape::Array { items: Box::new(Shape::Row { probe: 0 }), non_null: false }, "{sql}");
+            assert!(p.probes[0].sql.starts_with("SELECT u.* FROM users u"), "{sql}: {}", p.probes[0].sql);
+        }
+        let o = shapes("SELECT jsonb_build_object('author', u.*) AS j FROM users u");
+        assert_eq!(o.columns[0].1, Shape::Object { fields: vec![("author".into(), Shape::Row { probe: 0 })], non_null: true });
+        assert_eq!(shapes("SELECT to_jsonb(u.*) AS author FROM users u").columns[0].1, Shape::Row { probe: 0 });
         let r = shapes("SELECT row_to_json(u) AS author FROM users u");
         assert_eq!(r.columns[0].1, Shape::Row { probe: 0 });
         // A bare name is a column before it is a FROM item: `doc` is a column of `users`, here
@@ -1301,8 +1344,12 @@ mod tests {
         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, built: Vec::new() })], &registry, &[]);
+        // An outer join in the probe's own analysis is what can null the whole row, unless a
+        // column it proved non-null says the row is there (an unmatched row is every column NULL).
+        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns: columns.clone(), outer_join: true, built: Vec::new() })], &registry, &[]);
+        assert!(r.non_null);
+        let nullable: Vec<_> = columns.into_iter().map(|c| DescribedColumn { nullable: true, ..c }).collect();
+        let r = resolve(&p.columns[0].1, &[Some(Outcome { columns: nullable, outer_join: true, built: Vec::new() })], &registry, &[]);
         assert!(!r.non_null);
     }