DuckDB: printf-style FORMAT from BigQuery/Spark/SQLite/Postgres is written as DuckDB FORMAT, which uses {} placeholders

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

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
30/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
postgresql, python, sql, sqlite
Ambito
databases

Direzione di ricerca

Start by running the provided sqlglot.transpile snippet to confirm the four faulty outputs, then trace how exp.Format is parsed from BigQuery/Spark/SQLite/Postgres versus DuckDB and where DuckDBGenerator.format_sql writes it. The issue notes DuckDB PRINTF currently parses as Anonymous, so compare that parse path with DuckDB fmt-style FORMAT and the UUID_STRING template. Done means printf-style formats emit DuckDB PRINTF (or {} FORMAT for %s-only), DuckDB fmt-style FORMAT round-trips correctly, and tests cover the affected dialect pairs.

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

Descrizione

Fully reproducible code snippet

BigQuery FORMAT, Spark FORMAT_STRING, SQLite FORMAT and Postgres FORMAT all use printf-style % specifiers and parse to exp.Format. The DuckDB generator writes that as DuckDB FORMAT, but DuckDB's FORMAT uses fmt-style {} placeholders, so the query runs and just returns the format string:

import sqlglot, duckdb

for d, q in [
    ("bigquery", "SELECT FORMAT('%d-%s', 1, 'a')"),
    ("spark", "SELECT FORMAT_STRING('%d-%s', 1, 'a')"),
    ("sqlite", "SELECT FORMAT('%d-%s', 1, 'a')"),
    ("postgres", "SELECT FORMAT('%s-%s', 1, 'a')"),
]:
    out = sqlglot.transpile(q, read=d, write="duckdb")[0]
    print(d, out, "->", duckdb.sql(out).fetchall()[0][0])

On main (b294f7f) with DuckDB 2.0.0:

bigquery SELECT FORMAT('%d-%s', 1, 'a') -> %d-%s
spark    SELECT FORMAT('%d-%s', 1, 'a') -> %d-%s
sqlite   SELECT FORMAT('%d-%s', 1, 'a') -> %d-%s
postgres SELECT FORMAT('%s-%s', 1, 'a') -> %s-%s

Expected 1-a in all four. DuckDB has PRINTF for this (SELECT PRINTF('%d-%s', 1, 'a') -> 1-a). One catch: DuckDB's PRINTF only takes strings for %s (PRINTF('%s', 1) errors), so a %s-only format like the Postgres one is better written as FORMAT('{}-{}', 1, 'a'). DuckDBGenerator.format_sql already does this for the single '%s' case.

The other direction has the same mix-up: DuckDB FORMAT('{}-{}', 1, 2) parses to exp.Format too and is written as BigQuery FORMAT('{}-{}', 1, 2) / Spark FORMAT_STRING('{}-{}', 1, 2), and DuckDB PRINTF parses as Anonymous.

So exp.Format currently means printf-style when read from BigQuery/Spark/SQLite/Postgres and fmt-style when read from DuckDB (the DuckDB UUID_STRING template also builds FORMAT('{:02x}', ...) as exp.Format). Possible fix: treat exp.Format as printf-style, parse DuckDB PRINTF into it, generate PRINTF for DuckDB (or FORMAT with {} when %s is the only specifier), and give DuckDB's fmt-style FORMAT its own handling. I have a branch for the generator side and can open a PR if this direction sounds right.

Official Documentation

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.