Optimizer drops LEFT JOIN when a cast collapses distinct join keys
Maintainers usually reply within 1 day
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 30/100
Research direction
The bug is in _is_joined_on_all_unique_outputs in the sqlglot optimizer; read that function to see how it compares join keys by .name. Run the provided Python repro to confirm the dropped rows, then adjust the uniqueness check to account for casts. Done means the optimized SQL keeps the LEFT JOIN and returns three rows.
Written by the indexing model from the issue text.
Description
The optimizer removes a LEFT JOIN when its right-hand key is a cast of a distinct column. Distinct timestamps can become the same date after the cast, so removing the join changes the number of rows returned.
Reproduced on main at 6de2df31b0ae8b21155828f575a14dc8903c1b9b, with Python 3.12 and DuckDB 2.0.0. Both the read and write dialects are DuckDB.
import duckdb
from sqlglot import parse_one
from sqlglot.optimizer import optimize
sql = """
SELECT days.day
FROM (VALUES (DATE '2026-10-30'), (DATE '2026-10-31')) AS days(day)
LEFT JOIN (
SELECT DISTINCT events.occurred_at
FROM (VALUES
(TIMESTAMP '2026-10-30 09:00:00'),
(TIMESTAMP '2026-10-30 18:00:00')
) AS events(occurred_at)
) AS event_times ON days.day = CAST(event_times.occurred_at AS DATE)
ORDER BY days.day
"""
optimized = optimize(parse_one(sql, read="duckdb"), dialect="duckdb").sql("duckdb")
with duckdb.connect() as conn:
original = conn.execute(sql).fetchall()
rewritten = conn.execute(optimized).fetchall()
print("original:", [str(row[0]) for row in original])
print("optimized:", [str(row[0]) for row in rewritten])
print(optimized)
Output:
original: ['2026-10-30', '2026-10-30', '2026-10-31']
optimized: ['2026-10-30', '2026-10-31']
The optimized query should preserve all three rows: both events match October 30, and October 31 has no match. The generated SQL currently has no join. TRY_CAST and replacing DISTINCT with GROUP BY events.occurred_at also reproduce the issue.
The cause appears to be _is_joined_on_all_unique_outputs: it compares join_keys by .name, and Cast.name returns the underlying column name without proving that the cast preserves uniqueness.
DuckDB references: casts and join semantics.
Found with Codex assistance.
- Dominant language
- Python
- Stars
- 9.8k
- Forks
- 1.4k
- Avg merge
- 14h 12m
- Merged PRs (30d)
- 225
Getting set up
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Similar issues
-
curriculum documentation quality
Difficulty 2/5 1-3 hours Newbie friendliness 77/100
githubnext/gh-aw-workshop#5367 ·
Maintainers usually reply within 3 days
-
Create Noise Latents ignores randomize_seed and produces the same noise every runPossibly taken @griptapeops claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
griptape-ai/griptape-nodes-library-diffusers#127 ·
Maintainers usually reply within 1 day
-
Jenkins capacity report: metrics history is lost when archiving a month failsPossibly taken @RaphaelFakhri claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
adoptium/infrastructure#4568 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
huggingface/deep-rl-class#723 ·
-
premises-missing
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
WingedGuardian/GENesis-AGI#3928 ·
Maintainers usually reply within 1 day