Repository navigation
Description
Summary
When a model's SQL combines queries with UNION [ALL] BY NAME and a later branch has a column the first branch doesn't, SQLMesh builds the table without that column. There is no error or warning: the plan applies, audits pass, and the column is simply missing. DuckDB itself returns the column (filled with NULL for rows from branches that lack it).
There are two separate causes, one in each of SQLMesh and sqlglot:
- A
*that reads aBY NAMEunion is expanded to the first branch's columns only. This happens in sqlglot 30.8.0, which SQLMesh pins, and is fixed in sqlglot 30.17.0. - A model whose query is itself a
BY NAMEunion gets its column list from the first branch only, and SQLMesh then builds the table from that list. This is in SQLMesh, and a newer sqlglot doesn't fix it.
We found tables in our project that had been missing columns for this reason. I'll open a PR for cause 2.
Versions
- SQLMesh 0.236.4 (PyPI) and
mainat 8c71de8 (2026-11-10) - sqlglot 30.8.0
- DuckDB 1.5.6
- Python 3.12
Reproduction
import pathlib
import tempfile
import duckdb
import sqlglot
import sqlmesh
from sqlmesh import Context
from sqlmesh.core.config import Config, DuckDBConnectionConfig, GatewayConfig, ModelDefaultsConfig
MODELS = {
"x": "MODEL (name t.x, kind FULL); SELECT 1 AS a",
"y": "MODEL (name t.y, kind FULL); SELECT 2 AS a, 'two' AS b",
# 1. A SELECT * over a BY NAME union in a CTE
"star_over_cte": """
MODEL (name t.star_over_cte, kind FULL);
WITH u AS (SELECT a FROM t.x UNION ALL BY NAME SELECT a, b FROM t.y)
SELECT * FROM u
""",
# 2. A model whose query is itself the BY NAME union (no star)
"union_query": """
MODEL (name t.union_query, kind FULL);
SELECT a FROM t.x UNION ALL BY NAME SELECT a, b FROM t.y
""",
}
print(f"sqlmesh {sqlmesh.__version__}, sqlglot {sqlglot.__version__}, duckdb {duckdb.__version__}")
with tempfile.TemporaryDirectory() as tmp:
root = pathlib.Path(tmp)
(root / "models").mkdir()
for name, sql in MODELS.items():
(root / "models" / f"{name}.sql").write_text(sql)
config = Config(
gateways={"local": GatewayConfig(connection=DuckDBConnectionConfig(database=str(root / "db.duckdb")))},
default_gateway="local",
model_defaults=ModelDefaultsConfig(dialect="duckdb"),
)
context = Context(paths=tmp, config=config)
for name in ("t.star_over_cte", "t.union_query"):
model = context.get_model(name)
print(f"\n{name}")
print(" rendered: ", context.render(name).sql("duckdb"))
print(" columns_to_types:", {k: v.sql("duckdb") for k, v in model.columns_to_types.items()})
context.plan(auto_apply=True, no_prompts=True)
print("\nColumns of the built tables (DuckDB returns ['a', 'b'] for both queries):")
for name in ("t.star_over_cte", "t.union_query"):
print(f" {name}: {list(context.fetchdf(f'SELECT * FROM {name}').columns)}")Output (plan output trimmed):
sqlmesh 0.236.4, sqlglot 30.8.0, duckdb 1.5.6
t.star_over_cte
rendered: WITH "u" AS (SELECT "x"."a" AS "a" FROM "db"."t"."x" AS "x" UNION ALL BY NAME SELECT "y"."a" AS "a", "y"."b" AS "b" FROM "db"."t"."y" AS "y") SELECT "u"."a" AS "a" FROM "u" AS "u"
columns_to_types: {'a': 'INT'}
t.union_query
rendered: SELECT "x"."a" AS "a" FROM "db"."t"."x" AS "x" UNION ALL BY NAME SELECT "y"."a" AS "a", "y"."b" AS "b" FROM "db"."t"."y" AS "y"
columns_to_types: {'a': 'INT'}
Columns of the built tables (DuckDB returns ['a', 'b'] for both queries):
t.star_over_cte: ['a']
t.union_query: ['a']
Expected: both tables have columns a and b, as running either query in DuckDB gives.
Actual: both tables have only a.
For t.union_query, the rendered query is correct, but SQLMesh builds the table with:
CREATE OR REPLACE TABLE "db"."sqlmesh__t"."t__union_query__3398152808" AS
SELECT CAST("a" AS INT) AS "a"
FROM (
SELECT "x"."a" AS "a" FROM "db"."sqlmesh__t"."t__x__3776746655" AS "x"
UNION ALL BY NAME
SELECT "y"."a" AS "a", "y"."b" AS "b" FROM "db"."sqlmesh__t"."t__y__1644800246" AS "y"
) AS "_subquery"Cause 1: * over a BY NAME union (sqlglot)
qualify expands SELECT * FROM u from the columns of u. For a set operation, sqlglot 30.8.0's Resolver.get_source_columns_from_set_op handles BigQuery's ON (...), LEFT, FULL and INNER forms, but a plain BY NAME falls through to columns = set_op.named_selects (sqlglot/optimizer/resolver.py:129), and SetOperation.named_selects only looks at the leftmost branch.
sqlglot fixed this in 30.17.0 with tobymao/sqlglot#8136 ("properly support UNION BY NAME in qualify, type inference"): SetOperation.named_selects now folds in every branch when the set operation is BY NAME. With sqlglot 30.17.0, t.star_over_cte's * expands to a, b.
SQLMesh pins sqlglot~=30.8.0 (pyproject.toml:26), so it can't get the fix. SQLMesh main doesn't load models on 30.17.0 as it is:
TypeError: _parse_join() got an unexpected keyword argument 'alias_tokens'
That's SQLMesh's _parse_join override in sqlmesh/core/dialect.py:317, which doesn't accept the alias_tokens argument that newer sqlglot passes. There may be more. #5840 asks for a sqlglot upgrade for another reason.
Cause 2: a model whose query is a BY NAME union (SQLMesh)
SqlModel.columns_to_types builds the model's columns from query.selects (sqlmesh/core/model/definition.py:1493). For a set operation, sqlglot's SetOperation.selects returns the leftmost branch's projections. That's right for a positional union, but a BY NAME union also outputs the columns that only later branches have. This is unchanged in sqlglot 30.31.0, so upgrading sqlglot doesn't fix it.
When every type in that list is known, EngineAdapter._order_projections_and_filter (sqlmesh/core/engine_adapter/base.py:2934) wraps the query in SELECT CAST(...) AS ... FROM (<query>) AS _subquery with the listed columns only, which is the statement above. The incomplete list also reaches downstream models through the schema, so their * expands without the missing columns, and column_descriptions misses comments on them.
The same applies to BigQuery's FULL UNION ALL BY NAME. For INNER UNION ALL BY NAME, the list instead includes left-branch columns the query doesn't return.
Why it's easy to miss
Nothing reports it. The * case produces no linter violation at all. The table is built, audits on the remaining columns pass, and a column documented in column_descriptions can be absent from the table.
Workaround
Name every column in the model's final SELECT instead of using *, and move a union that is the whole query into a CTE. A named column that only a later branch has can't be resolved by sqlglot, so SQLMesh falls back to the unoptimized query (recorded as an AmbiguousOrInvalidColumn violation, which is silent unless the linter is enabled), and DuckDB resolves the column. The cost is that SQLMesh no longer knows the model's column types. A * left in the final SELECT of a model that falls back leaves columns_to_types as None, so downstream models can't be optimized either.
Proposed fix
-
Cause 1: upgrade sqlglot to 30.17.0 or later.
-
Cause 2: derive a model's columns from all branches of a
BY NAMEset operation. For BigQuery's modifiers, that means sqlglot's rules: anONlist names the columns,LEFTkeeps the left branch's, andINNERthe ones both branches have. WithFULLor no modifier it means every branch's columns, in order of first appearance. My PR does this.Because a model's inferred columns aren't part of its fingerprint, the fix changes the columns of existing incremental models without creating new versions, so their next
runwould insert columns their tables don't have. The PR describes this. It may need a state migration or a release note.
Activity