Repository navigation
Optimizer: Columns left unqualified in DML statements #8652
Description
Opening an issue with the motivations for the changes in the PR #8651
Columns in DML statements are currently left unqualified even when they are resolvable, resulting in validation being skipped and passing for invalid syntax:
from sqlglot import parse_one
from sqlglot.errors import OptimizeError
from sqlglot.optimizer.qualify import qualify
schema = {"x": {"a": "INT", "b": "INT"}, "y": {"b": "INT", "c": "INT"}}
for sql in [
# `c` only exists in y, `a` only in x: both are resolvable but are left unqualified
"UPDATE x SET b = c FROM y WHERE a = y.b",
# `c` is not a column of x: passes validation
"INSERT INTO x (a, c) SELECT b, c FROM y",
# `c` exists in no table in scope: passes validation
"DELETE FROM x WHERE c = 1",
]:
try:
print(f"{sql}\n -> {qualify(parse_one(sql), schema=schema).sql()}")
except OptimizeError as e:
print(f"{sql}\n -> OptimizeError: {e}")Output on main:
UPDATE x SET b = c FROM y WHERE a = y.b
-> UPDATE "x" SET "b" = "c" FROM "y" AS "y" WHERE "a" = "y"."b"
INSERT INTO x (a, c) SELECT b, c FROM y
-> INSERT INTO "x" ("a", "c") SELECT "y"."b" AS "b", "y"."c" AS "c" FROM "y" AS "y"
DELETE FROM x WHERE c = 1
-> DELETE FROM "x" WHERE "c" = 1
Output on #8651:
UPDATE x SET b = c FROM y WHERE a = y.b
-> UPDATE "x" SET "b" = "y"."c" FROM "y" AS "y" WHERE "x"."a" = "y"."b"
INSERT INTO x (a, c) SELECT b, c FROM y
-> OptimizeError: Unknown column: c
DELETE FROM x WHERE c = 1
-> OptimizeError: Column 'c' could not be resolved. Line: 1, Col: 21
Let me know if there's a preferred/simpler way to handle this. Thanks!
Activity
georgesittas commentedon Oct 16, 2026
I'm not sure if we can safely qualify identifiers in these DDL/DML statements, like we do for queries. Even if it's safe for some dialects, it may not be for others– this needs to be researched very carefully across the supported dialects. It has been intentionally left out of scope until now, so lots of pieces are likely missing.
I'm not sure I want to deal with this right now, given the blast radius and the added complexity. If you can test this across dialects and prove that the change is low-risk, we will consider helping you get a patch in.
Thanks!