When simplify resolves a CASE to one of its branches, the branch replaces the CASE without parentheses. If the CASE is an operand of an arithmetic operator and the branch is itself a binary expression, the result changes meaning.
Fully reproducible code snippet
Checked on main (19077cc):
from sqlglot import parse_one
from sqlglot.optimizer.simplify import simplify
for sql in [
"SELECT x * CASE WHEN FALSE THEN NULL ELSE a - b END FROM t",
"SELECT x * (CASE WHEN 1 IS NULL THEN NULL ELSE 1 - b END) FROM t",
"SELECT x - CASE WHEN TRUE THEN a + b ELSE c END FROM t",
]:
print(simplify(parse_one(sql, read="duckdb")).sql(dialect="duckdb"))Output:
SELECT x * a - b FROM t
SELECT x * 1 - b FROM t
SELECT x - a + b FROM t
Expected:
SELECT x * (a - b) FROM t
SELECT x * (1 - b) FROM t
SELECT x - (a + b) FROM t
With values, DuckDB gives SELECT 2 * CASE WHEN FALSE THEN NULL ELSE 10 - 5 END = 10, while the simplified SELECT 2 * 10 - 5 = 15.
The second case shows that parentheses written around the CASE don't help: they are removed while the node is still a CASE, and the CASE is collapsed afterwards.
Context
Found through SQLMesh's @SAFE_SUB macro, which builds CASE WHEN ... IS NULL THEN NULL ELSE a - b END. When an argument is a literal, the rendered model computes the wrong value (SQLMesh/sqlmesh#5649). SQLMesh can work around it by emitting an explicit paren around the branch (SQLMesh/sqlmesh#6074), but hand written CASE expressions hit the same thing.
Official Documentation
Operator precedence (PostgreSQL, same rules in DuckDB): https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE
When
simplifyresolves a CASE to one of its branches, the branch replaces the CASE without parentheses. If the CASE is an operand of an arithmetic operator and the branch is itself a binary expression, the result changes meaning.Fully reproducible code snippet
Checked on main (
19077cc):Output:
Expected:
With values, DuckDB gives
SELECT 2 * CASE WHEN FALSE THEN NULL ELSE 10 - 5 END= 10, while the simplifiedSELECT 2 * 10 - 5= 15.The second case shows that parentheses written around the CASE don't help: they are removed while the node is still a CASE, and the CASE is collapsed afterwards.
Context
Found through SQLMesh's
@SAFE_SUBmacro, which buildsCASE WHEN ... IS NULL THEN NULL ELSE a - b END. When an argument is a literal, the rendered model computes the wrong value (SQLMesh/sqlmesh#5649). SQLMesh can work around it by emitting an explicit paren around the branch (SQLMesh/sqlmesh#6074), but hand written CASE expressions hit the same thing.Official Documentation
Operator precedence (PostgreSQL, same rules in DuckDB): https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE