Optimizer drops LEFT JOIN when a cast collapses distinct join keys

Chiusa
  • Qualcosa non va?
#8,858 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

@finnhoang05 ci sta già lavorando.

Dal 31/10/2026.

  • #8859 di @finnhoang05 — aperta

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
30/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Ferma
Stack tecnologico
python, sql
Ambito
compilers, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
Python
Stelle
9.8k
Fork
1.4k
Merge medio
14h
PR unite (30g)
212

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di tobymao/sqlglot

Tutte le issue di tobymao/sqlglot

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.