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

Abierto
#9,112 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
30/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
postgresql, python, sql, sqlite
Área
databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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

Lenguaje dominante
Python
Estrellas
9.8k
Forks
1.4k
Merge medio
13 h 19 min
PR fusionados (30 d)
243

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de tobymao/sqlglot

Todos los issues de tobymao/sqlglot

Issues similares

Más issues de Python

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.