Optimizer drops parentheses for `NOT (<date> = DATE_TRUNC(...))`, changing query results

Aperta
  • Qualcosa non va?
#9,107 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

@parthdembla007 ci sta già lavorando.

Dal 15/11/2026.

  • #9108 di @parthdembla007 — aperta

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
28/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Ferma
Stack tecnologico
python
Ambito
databases

Direzione di ricerca

Start in the optimizer's simplify rules: simplify_parens and simplify_datetrunc in sqlglot/optimizer/simplify.py, plus _parenthesize_nested_connector, which the reporter blames for deciding parens are unnecessary because expression.parent is still the Paren being unwrapped. Reproduce with the DuckDB snippet in the issue, then follow the rule ordering where sort_comparison moves DATE_TRUNC left before the NOT is pushed in. Done looks like the optimized query returning the same 4 rows as the original and a new test under tests/fixtures/optimizer covering NOT with the literal on either side, for = and <>. Note a pull request (#9108) is already open against this, so coordinate before starting.

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

Descrizione

The simplifier rewrites NOT (DATE '2024-01-03' = DATE_TRUNC('day', x)) into
NOT x < ... AND x >= .... Because the AND isn't parenthesized, NOT only applies to the
first comparison, so the optimized query returns different rows. It only happens when the
literal is on the left side; NOT (DATE_TRUNC('day', x) = DATE '2024-01-03') is fine.

Reproduced on main (b294f7fa8) with DuckDB 1.5.6:

import duckdb
import sqlglot
from sqlglot.optimizer import optimize

con = duckdb.connect()
con.execute("CREATE TABLE t AS SELECT DATE '2024-01-01' + CAST(i AS INT) AS x FROM range(5) r(i)")

sql = "SELECT COUNT(*) FROM t WHERE NOT (DATE '2024-01-03' = DATE_TRUNC('day', x))"
optimized = optimize(sqlglot.parse_one(sql, read="duckdb"), schema={"t": {"x": "DATE"}}, dialect="duckdb").sql("duckdb")

print(optimized)
print("original: ", con.execute(sql).fetchone()[0])
print("optimized:", con.execute(optimized).fetchone()[0])

Output:

SELECT COUNT(*) AS "_col_0" FROM "t" AS "t" WHERE NOT "t"."x" < CAST('2024-01-04' AS DATE) AND "t"."x" >= CAST('2024-01-03' AS DATE)
original:  4
optimized: 2

Expected: "t"."x" < CAST('2024-01-03' AS DATE) OR "t"."x" >= CAST('2024-01-04' AS DATE), i.e. 4 rows.

It happens for every unit (year, quarter, month, week, day, hour) and for <> as well.

Cause, as far as I can tell: the reversed comparison is only rewritten after sort_comparison
moves DATE_TRUNC to the left. When the enclosing Paren is processed, simplify_parens unwraps
it and then simplify_datetrunc runs in the same step, but expression.parent is still the
Paren being removed, so _parenthesize_nested_connector decides no parens are needed.

I found this by comparing query results in DuckDB before and after optimize(); I used Claude
to help with the testing and investigation. I have a small fix with tests and can open a PR.

Lingua principale
Python
Stelle
9.8k
Fork
1.4k
Merge medio
13h 19m
PR unite (30g)
243

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.