colinhacks · Nov 6, 2026
diff --git a/AGENTS.md b/AGENTS.md
@@ -50,7 +50,7 @@ The live tests in these files skip without both test databases ([JavaScript](#ja
 The engine, crate by crate:
 
 - **Postgres**, `engine/postgres/src/`: `postgres.rs` (the analysis, round by round), `infer.rs` (expression columns' types and nullability), `cardinality.rs` (how many rows a statement returns), `jsonshape.rs` (nested JSON shapes), `levels.rs` (which query an aggregate belongs to), `returning.rs`, `analyze.rs` (parse-tree facts: the statement's kind, locking, the tables it reads and writes, and which parameters may be null, as through `coalesce`), `pgtypes.rs` (the type registry: each catalog type as a type descriptor), `stack.rs` (how deeply a statement may nest), `extension.rs` (the handshake with a server-side extension). The parser is `pg_query` (libpg_query, Postgres 18), patched from `engine/postgres/vendor/` into `engine/postgres/.patched/` by the prepare step.
-- **MySQL**, `engine/mysql/src/`: `mysql.rs` (the analysis over the server's prepare metadata), `mysql_types.rs` (the prepare's type codes, flags and lengths as type descriptors), `mysql_nulls.rs` (what a WHERE implies is not null), `mysql_derived.rs` (derived tables and WITH queries), `mysql_shape.rs` (JSON constructors), `mysql_params.rs` (which parameters may be null). It reads statements with its own copy of `sqlparser` 0.63, cut to the statements it needs (`engine/mysql/vendor/sqlparser`, whose README lists the changes).
+- **MySQL**, `engine/mysql/src/`: `mysql.rs` (the analysis over the server's prepare metadata), `mysql_types.rs` (the prepare's type codes, flags, lengths and decimals as type descriptors, a table's column narrowed by its definition), `mysql_nulls.rs` (what a WHERE implies is not null), `mysql_derived.rs` (derived tables and WITH queries), `mysql_shape.rs` (JSON constructors), `mysql_params.rs` (which parameters may be null). It reads statements with its own copy of `sqlparser` 0.63, cut to the statements it needs (`engine/mysql/vendor/sqlparser`, whose README lists the changes).
 - **SQLite**, `engine/sqlite/`: `src/sqlite.rs` (the analysis over SQLite's resolved tree, and its function tables), `src/sqlite_tree.rs` (the tree as `csrc/reylo_sqlite.c` serializes it), and `csrc/patch-sqlite.py` (the hooks the prepare step applies to the amalgamation).
 - **DuckDB**, `engine/duckdb/src/`: `duckdb.rs` (the analysis over the prepared statement's types, which the host answers a describe with), `nulls.rs` (which columns are never NULL, and how many rows a statement returns), `catalog.rs` (the tables' NOT NULL columns and keys, from `duckdb_columns()` and `duckdb_constraints()`), `types.rs` (DuckDB's logical types as type descriptors). It reads statements with `sqlparser` 0.63 from crates.io, whose DuckDB dialect reads DuckDB's own syntax (`FROM` first, `QUALIFY`, `EXCLUDE`); a statement it does not read is typed from the describe alone.
 - **Core**, `engine/core/src/`: `database.rs` (the `Database` trait each database implements), `types.rs` (the type descriptor format: each parameter's and column's type, and the `StatementDescriptor` an analysis projects to), `comment.rs` (a statement's leading comment), `shape.rs` (the nested-JSON vocabulary every database plans with), `lex.rs`, `config.rs`, `structured.rs` (`row(…)` and `rows(…)` parameters), `schema_type.rs` (a JSON Schema CHECK to a type), `schema.rs` (a type to JSON Schema), `api.rs` (the request protocol), `host.rs` (the answers a host gave and the needs the engine records), `fresh.rs`, `fingerprint.rs`.

diff --git a/engine/facade/src/clients/mysql_async.rs b/engine/facade/src/clients/mysql_async.rs
@@ -29,7 +29,7 @@ impl AsyncAnswerer<Describe> for Answers<'_> {
         async move {
             match conn.prep(sql).await {
                 Ok(statement) => {
-                    let columns: Vec<Value> = statement.columns().iter().map(|c| json!({ "name": c.name_str(), "type": c.column_type() as u8, "flags": c.flags().bits(), "charset": c.character_set(), "length": c.column_length(), "org_table": c.org_table_str(), "org_name": c.org_name_str() })).collect();
+                    let columns: Vec<Value> = statement.columns().iter().map(|c| json!({ "name": c.name_str(), "type": c.column_type() as u8, "flags": c.flags().bits(), "charset": c.character_set(), "length": c.column_length(), "decimals": c.decimals(), "schema": c.schema_str(), "org_table": c.org_table_str(), "org_name": c.org_name_str() })).collect();
                     let params: Vec<Value> = statement.params().iter().map(|p| json!({ "type": p.column_type() as u8, "flags": p.flags().bits(), "charset": p.character_set() })).collect();
                     conn.close(statement).await.map_err(|e| HostError(e.to_string()))?;
                     serde_json::from_value(json!({ "columns": columns, "params": params })).map(Ok).map_err(|e| HostError(e.to_string()))

diff --git a/engine/mysql/src/api.rs b/engine/mysql/src/api.rs
@@ -16,8 +16,8 @@ pub type Answers = reylo_engine_core::api::Answers<MysqlDescribe>;
 
 /// MySQL's prepare metadata as mysql2 reports it: the protocol type code, the flags (bit 1 is
 /// NOT NULL) and the charset (63 is binary) per result column, and the server's type guess per `?`.
-/// A column also carries its display length and the table and column it is a column of, which
-/// are empty for an expression; a host that sends none of them gets the answer that holds
+/// A column also carries its length, its decimals and the schema, table and column it is a column
+/// of, which are empty for an expression; a host that sends none of them gets the answer that holds
 /// whatever they are.
 #[derive(Debug, Clone, Deserialize)]
 pub struct MysqlDescribe {
@@ -32,8 +32,19 @@ pub struct MysqlColumn {
     pub flags: u32,
     #[serde(default)]
     pub charset: u32,
+    /// The column's length in the protocol's own terms: a string's in bytes of the character set
+    /// it is sent in (`varchar(20)` sent as utf8mb4 is 80), a DECIMAL's in digits with its point
+    /// and sign, a binary string's in bytes.
     #[serde(default)]
     pub length: Option<u32>,
+    /// The digits after the point: a DECIMAL's scale, a DATETIME's, TIMESTAMP's or TIME's
+    /// fractional seconds (`datetime(3)` is 3), and 31 where none is fixed.
+    #[serde(default)]
+    pub decimals: Option<u32>,
+    /// The schema of the table the column is a column of: empty for an expression, whether or not
+    /// `org_table` names a table.
+    #[serde(default)]
+    pub schema: Option<String>,
     #[serde(default)]
     pub org_table: Option<String>,
     /// The column's own name in `org_table`, whatever the statement calls it.

diff --git a/engine/mysql/src/mysql.rs b/engine/mysql/src/mysql.rs
@@ -409,6 +409,11 @@ pub fn analyze(sql: &str, lexed: &Lexed, text: Option<MysqlText>, host: &Host, m
         }
         typed.labelled.get(&(table.to_ascii_lowercase(), column.to_ascii_lowercase())).cloned()
     };
+    // Whether a result column is a TEXT column that stores UTF-8, by the same origin.
+    let utf8_of = |c: &crate::api::MysqlColumn| -> bool {
+        let (Some(table), Some(column)) = (c.org_table.as_deref(), c.org_name.as_deref()) else { return false };
+        !column.is_empty() && traced.as_ref().is_some_and(|traced| traced.iter().any(|t| t.eq_ignore_ascii_case(table))) && typed.utf8.contains(&(table.to_string(), column.to_ascii_lowercase()))
+    };
     // What the engine knows of each column being NULL: the server's flags, read where merging
     // could make them wrong with every derived table materialized, and what the engine proves
     // beyond them.
@@ -433,8 +438,9 @@ pub fn analyze(sql: &str, lexed: &Lexed, text: Option<MysqlText>, host: &Host, m
     for position in flagged_wrongly(parsed.as_ref(), never_null.len(), views) {
         never_null[position] = (false, None);
     }
-    // A column of a view may be an expression, which the prepare does not tell from a table's.
-    let views: Option<HashSet<String>> = match raw.columns.iter().any(|c| (other_width(c).is_some() || flag_width(c)) && c.org_table.as_ref().is_some_and(|t| !t.is_empty())) {
+    // A column of a view may be an expression, which the prepare does not tell from a table's:
+    // the views are asked for wherever a column names its table.
+    let views: Option<HashSet<String>> = match raw.columns.iter().any(|c| c.org_table.as_ref().is_some_and(|t| !t.is_empty())) {
         true => host.query(VIEWS_QUERY, Vec::new(), Scope::Session)?.ok().map(|rows| rows.iter().filter_map(|row| row.first().and_then(as_str)).map(str::to_ascii_lowercase).collect()),
         false => Some(HashSet::new()),
     };
@@ -452,7 +458,8 @@ pub fn analyze(sql: &str, lexed: &Lexed, text: Option<MysqlText>, host: &Host, m
         .iter()
         .enumerate()
         .map(|(position, c)| {
-            let ty = settled.get(position).cloned().flatten().unwrap_or_else(|| column_type(c));
+            // A table's own column is of its type whatever is bound, narrowed by its definition.
+            let ty = settled.get(position).cloned().flatten().unwrap_or_else(|| defined_type(c, views.as_ref(), utf8_of(c)));
             // What the statement computes as 1 or 0 is a truth value, which MySQL delivers as an
             // integer; a table's TINYINT(1) or BIT(1) column keeps its type, since it holds
             // whatever was stored in it, and a client reads it as a truth value by its name. Any
@@ -717,7 +724,7 @@ fn prove(q: &Query, raw: Option<&MysqlDescribe>, views: &HashSet<String>, host:
         .columns
         .iter()
         .map(|(name, shape)| {
-            let (ty, non_null) = resolve(shape, &outcomes, &plan, &flows, &documents);
+            let (ty, non_null) = resolve(shape, &outcomes, &plan, &flows, &documents, views);
             (name.clone(), ty, non_null)
         })
         .collect();
@@ -1537,21 +1544,45 @@ fn flag_width(c: &crate::api::MysqlColumn) -> bool {
     matches!((c.r#type, c.length), (1 | 16, Some(1)))
 }
 
-/// Whether a result column is a table's own TINYINT(1) or BIT(1) column, read straight from it
-/// (through a derived table or a WITH query too, which the prepare traces to the table): what an
-/// application stores a truth value in. An expression names no table, and a view's column may be
-/// an expression whose width says nothing, so neither is one; nor is any column where the views
-/// are not known.
-fn flag_column(c: &crate::api::MysqlColumn, views: Option<&HashSet<String>>) -> bool {
+/// Whether a result column is a table's own column, read straight from it (through a derived
+/// table or a WITH query too, which the prepare traces to the table), so that its values are the
+/// table's, of the type its definition says. An expression names no table, and a view's column
+/// may be an expression whose width says nothing, so neither is one; nor is any column where the
+/// views are not known.
+fn table_column(c: &crate::api::MysqlColumn, views: Option<&HashSet<String>>) -> bool {
     let named = |s: &Option<String>| s.as_ref().is_some_and(|s| !s.is_empty());
-    flag_width(c) && named(&c.org_name) && c.org_table.as_ref().is_some_and(|table| !table.is_empty() && views.is_some_and(|views| !views.contains(&table.to_ascii_lowercase())))
+    named(&c.org_name) && c.org_table.as_ref().is_some_and(|table| !table.is_empty() && views.is_some_and(|views| !views.contains(&table.to_ascii_lowercase())))
+}
+
+/// Whether a result column is a table's own TINYINT(1) or BIT(1) column ([`table_column`]): what
+/// an application stores a truth value in.
+fn flag_column(c: &crate::api::MysqlColumn, views: Option<&HashSet<String>>) -> bool {
+    flag_width(c) && table_column(c, views)
 }
 
 /// A result column's type, as its prepare describes it ([`crate::mysql_types::of`]).
 fn column_type(c: &crate::api::MysqlColumn) -> Type {
     crate::mysql_types::of(c.r#type, c.flags, c.charset, c.length)
 }
 
+/// Whether a result column's values are stored under the definition the prepare reports: a table's
+/// own column ([`table_column`]) of a schema the prepare names, other than `information_schema`.
+/// An expression names no schema, even where it names a table, as a column of `information_schema`'s
+/// own views does (`TABLES.TABLE_COMMENT`) and JSON_TABLE's do. [`VIEWS_QUERY`] does not list those
+/// views, and the columns of them that do name the schema are read from the data dictionary, so the
+/// reported length of neither is known to bound its values.
+fn stored_column(c: &crate::api::MysqlColumn, views: Option<&HashSet<String>>) -> bool {
+    table_column(c, views) && c.schema.as_deref().is_some_and(|schema| !schema.is_empty() && !schema.eq_ignore_ascii_case("information_schema"))
+}
+
+/// A result column's type, narrowed by its definition where its values are stored under it
+/// ([`stored_column`], [`crate::mysql_types::declared`]): `utf8` where it is a `TEXT` column known
+/// to store UTF-8.
+fn defined_type(c: &crate::api::MysqlColumn, views: Option<&HashSet<String>>, utf8: bool) -> Type {
+    let ty = column_type(c);
+    if stored_column(c, views) { crate::mysql_types::declared(ty, c, utf8) } else { ty }
+}
+
 /// Each `?` the prepare typed as a VECTOR wrapped in `to_vector(…)`, so that it binds the text of
 /// its floats (`[1,2.5]`), which the server reads as a VECTOR wherever it stands. A VECTOR's bytes
 /// would not do on every driver: mariadb and PlanetScale write every value into the statement's
@@ -1649,28 +1680,30 @@ fn param_type(code: u32, charset: u32) -> Type {
     }
 }
 
-/// One shape plus its probes' prepares as the column's type and whether it is never NULL.
-fn resolve(shape: &Shape, outcomes: &[Option<MysqlDescribe>], plan: &crate::mysql_shape::Plan, flows: &[Vec<u8>], documents: &[Vec<Option<Value>>]) -> (Type, bool) {
+/// One shape plus its probes' prepares as the column's type and whether it is never NULL. A leaf
+/// stored under its definition ([`stored_column`]) takes its narrowing, its `TEXT`'s capacity
+/// counted in code points, since the probe's origin may be a derived column's own name.
+fn resolve(shape: &Shape, outcomes: &[Option<MysqlDescribe>], plan: &crate::mysql_shape::Plan, flows: &[Vec<u8>], documents: &[Vec<Option<Value>>], views: &HashSet<String>) -> (Type, bool) {
     match shape {
         Shape::Object { fields, non_null } => {
             let fields = fields
                 .iter()
                 .map(|(k, s)| {
-                    let (ty, nn) = resolve(s, outcomes, plan, flows, documents);
+                    let (ty, nn) = resolve(s, outcomes, plan, flows, documents, views);
                     Field::new(k.clone(), ty, !nn)
                 })
                 .collect();
             (Type::record(fields), *non_null)
         }
         Shape::Array { items, non_null } => {
-            let (ty, nn) = resolve(items, outcomes, plan, flows, documents);
+            let (ty, nn) = resolve(items, outcomes, plan, flows, documents, views);
             (Type::array(Position::new(ty, !nn)), *non_null)
         }
         Shape::Column { probe, index } => match outcomes.get(*probe).and_then(|o| o.as_ref()).and_then(|o| o.columns.get(*index)) {
             Some(c) if plan.booleans.contains(&(*probe, *index)) => (Type::boolean(), c.flags & 1 != 0),
             // A truth value is an integer to the server, so no other type is in doubt.
             Some(c) => {
-                let ty = column_type(c);
+                let ty = defined_type(c, Some(views), false);
                 let ty = flows.get(*probe).and_then(|f| f.get(*index)).and_then(|reached| widened(&ty, *reached)).unwrap_or(ty);
                 // A JSON column a CHECK holds to a JSON Schema: the values it admits.
                 let ty = match documents.get(*probe).and_then(|d| d.get(*index)).cloned().flatten() {
@@ -2892,6 +2925,56 @@ mod tests {
         panic!("the analysis never settled")
     }
 
+    #[test]
+    fn a_tables_column_takes_its_definition_and_an_expression_or_a_views_column_does_not() {
+        // Each column as MySQL 8.4.11's prepare reports it, sent as utf8mb4 (collation 255).
+        let column = |name: &str, ty: u32, charset: u32, length: u32, decimals: u32, table: &str, org: &str| json!({ "name": name, "type": ty, "flags": 0, "charset": charset, "length": length, "decimals": decimals, "schema": if table.is_empty() { "" } else { "app" }, "org_table": table, "org_name": org });
+        let types = |sql: &str, columns: Vec<Value>| {
+            let mut answers: HashMap<String, crate::api::Answer> = HashMap::new();
+            for _ in 0..8 {
+                let host = Host::new(&answers);
+                if let Some(analysis) = analyze_text(sql, &host) {
+                    return analysis.columns.iter().map(|c| c.ty.short()).collect::<Vec<_>>().join(" ");
+                }
+                for need in host.needs() {
+                    let (id, answer) = match need {
+                        crate::api::Need::Describe { id, .. } => (id, json!({ "mysql": { "columns": columns.clone(), "params": [] } })),
+                        crate::api::Need::Query { id, sql, .. } if sql == VIEWS_QUERY => (id, json!({ "rows": [["recent"]] })),
+                        crate::api::Need::Query { id, sql, .. } if sql == crate::mysql_params::TYPES_QUERY => (id, json!({ "rows": [["users", "body", "text", "text", "", "", "utf8mb4"], ["users", "legacy", "text", "text", "", "", "latin1"]] })),
+                        crate::api::Need::Query { id, .. } => (id, json!({ "rows": [] })),
+                    };
+                    answers.insert(id, serde_json::from_value(answer).unwrap());
+                }
+            }
+            panic!("the analysis never settled")
+        };
+        let name = column("name", 253, 255, 80, 0, "users", "name");
+        let body = column("body", 252, 255, 262_140, 0, "users", "body");
+        let legacy = column("legacy", 252, 255, 262_140, 0, "users", "legacy");
+        let price = column("price", 246, 63, 12, 2, "users", "price");
+        // A TEXT the catalog says stores UTF-8 counts its bytes, and one in latin1 its code points.
+        assert_eq!(types("select name, body, legacy, price from users", vec![name.clone(), body.clone(), legacy, price]), "varchar(≤20) text(≤65535 bytes UTF-8) text(≤65535) decimal(10,2)");
+        // Through a derived table the prepare names the table, and a column's origin there may be
+        // its derived name, so a TEXT counts code points.
+        assert_eq!(types("select name, body from (select name, body from users) d", vec![name, body]), "varchar(≤20) text(≤65535)");
+        // An expression names no table, and a view's column may be an expression.
+        assert_eq!(types("select concat(name, '!') as c, price * 2 as p from users", vec![column("c", 253, 255, 84, 31, "", ""), column("p", 246, 63, 13, 2, "", "")]), "varchar decimal");
+        assert_eq!(types("select name, price from recent", vec![column("name", 253, 255, 80, 0, "recent", "name"), column("price", 246, 63, 12, 2, "recent", "price")]), "varchar decimal");
+        // A column of `information_schema`'s own views, which no list of views holds: an expression
+        // names no schema though it names its view, and the rest name `information_schema`.
+        let in_schema = |mut c: Value, schema: &str| {
+            c["schema"] = json!(schema);
+            c
+        };
+        let comment = in_schema(column("TABLE_COMMENT", 253, 255, 8192, 31, "TABLES", "TABLE_COMMENT"), "");
+        let kind = in_schema(column("COLUMN_TYPE", 252, 255, 67_108_860, 0, "COLUMNS", "COLUMN_TYPE"), "information_schema");
+        assert_eq!(types("select TABLE_COMMENT, COLUMN_TYPE from information_schema.columns", vec![comment, kind]), "varchar text");
+        // A host that sends no schema says nothing of where a column's values come from.
+        let mut bare = column("name", 253, 255, 80, 0, "users", "name");
+        bare.as_object_mut().unwrap().remove("schema");
+        assert_eq!(types("select name from users", vec![bare]), "varchar");
+    }
+
     #[test]
     fn an_integer_the_statement_computes_is_as_wide_as_the_plan_makes_it() {
         let types = |columns: Value, views: &[&str]| {