LPTS 1.1.0 — round-trip correctness, enforced.

This release makes "LPTS never rewrites a query wrongly" an enforced, repeatable invariant: a transparent round-trip checker, all ~3,300 files of DuckDB's own sqllogic corpus run through it, every wrong translation and invalid-SQL emission that surfaced fixed, and the result locked in as a regression gate. Across the corpus (files=3346), LPTS produces 0 wrong rewrites and 0 invalid-SQL failures — every untranslatable query is a deliberate, machine-recognizable LPTS_<CODE>: refusal.

Added

• lpts_check session setting (SET lpts_check = true). Every top-level SELECT is transparently rewritten by LPTS and its result bag compared, by an order-independent hash, against the original. Strict mode raises on a divergence; a LPTS_CHECK_LOG log mode records one verdict per query. Nondeterministic queries are detected and excused.
• DuckDB-corpus coverage gate — lpts_corpus_gate (a standalone C++ driver, no shell/Python dependency) runs DuckDB's entire test/sql/** suite through lpts_check in parallel and diffs per-file verdict counts against a committed baseline. New make coverage-check / make coverage-baseline targets; make test now runs the unit suite plus the gate. The gate fails on any new WRONG or FAIL.

Removed

• PRAGMA lpts_exec('query'), the boolean-returning PRAGMA lpts_check('query'), and the lpts_check_log setting — all superseded by the lpts_check session setting above.

Changed

• DuckDB target bumped to v1.5.4; coverage-gate baseline regenerated against v1.5.4's corpus.
• Non-DuckDB dialect hardening (chiefly Spark): pipeline fusion disabled for the SPARK dialect (stable per-operator CTE layout for downstream consumers); BROADCAST(...) join hints emitted; ambiguous set-op / anti-join conditions over un-aliased self-joins qualified with their owning CTE; set-operation output columns routed through the shared name-dedup path (no more duplicate column names from a self-join UNION/EXCEPT/INTERSECT); untyped NULL rendered as bare NULL in non-DuckDB dialects.

Fixed

• Wrong-results eliminated (→ 0): duplicate generated column names at UNION/EXCEPT/correlated-subquery boundaries (case-insensitively); 3-valued NULL semantics for multi-condition MARK joins (correlated EXISTS / row-IN); HAVING without GROUP BY; read_parquet named-argument preservation; multi-row scalar subqueries excused as nondeterministic.
• Invalid-SQL eliminated (→ 0): constants with no literal form (unnamed structs, MAP/UNION/GEOMETRY/ENUM, NUL bytes); lambda arity/capture/shadowing; list-slice bracket syntax; WITH ORDINALITY and LATERAL table functions; struct-field pushdown paths; window RANGE offsets; quantile/string_agg argument recovery; reserved-word and unicode identifier quoting; and more.
• Everything untranslatable now refuses with a standardized LPTS_<CODE>: message the gate distinguishes from bugs.

Validation

• Coverage gate: files=3346 wrong=0 fail=0 (every non-rewritable query is a deliberate UNSUPPORTED refusal).
• Unit suite: 2131 assertions across 45 test files.
• SQLStorm TPC-H sf=0.001 (17,036 queries under lpts_check): 16,142 success, 0 incorrect.

Compatibility

• DuckDB target: v1.5.4
• extension-ci-tools target: v1.5-variegata
• Release tag: v1.1.0

See CHANGELOG.md for the full per-fix detail.

LPTS 1.0.0 — first stable release.

Added

• EXPLAIN (FORMAT SQL) <query> renders the optimized logical plan as equivalent CTE SQL (the same output as PRAGMA lpts), exposed as a genuine EXPLAIN statement. The CLI prints it as plain multi-line text; JDBC/Python and other clients receive the standard two-column explain_key/explain_value EXPLAIN result. Honors lpts_dialect. Implemented without changes to DuckDB core.

Changed

• Generated SQL is now pretty-printed across multiple lines (instead of a single line), with HAVING fused into its aggregate and join build-side CTEs marked, for more readable output.
• Fewer CTEs are emitted while keeping execution order unambiguous, and column names are now human-readable (e.g. t0_scan (t0_name)) rather than purely positional.
• DuckDB target bumped from v1.5.3 to v1.5.4 (submodule and CI). The 1.5.4 optimizer pushes trivial projections/filters into the scan, so single-table plans may collapse to a plain SELECT with no separate projection CTE; round-trip correctness is unchanged.

Compatibility

• DuckDB target: v1.5.4
• extension-ci-tools target: v1.5-variegata

Changelog

0.9.0 - 2026-05-29

This is the first release-candidate-quality LPTS milestone. It is intended to
stabilize the public DuckDB extension surface before a future 1.0.0 API
stability release.

Compatibility

• DuckDB target: v1.5.3
• extension-ci-tools target: v1.5-variegata
• Release tag: v0.9.0

Public API

• PRAGMA lpts('query') returns generated CTE SQL.
• lpts_query('query') returns generated CTE SQL as a table function.
• PRAGMA lpts_exec('query') executes the generated SQL.
• PRAGMA lpts_check('query') compares the original query and generated SQL with bag equality.
• PRAGMA print_ast('query') prints the LPTS AST tree.
• print_ast_query('query') returns the AST tree as a table function.
• lpts_normalize_query('query') returns input-dialect SQL normalized to DuckDB SQL.

Settings

• lpts_dialect: output dialect for generated SQL.
• lpts_input_dialect: input dialect to normalize before DuckDB parses and plans the query.

Supported dialect values: duckdb, postgres/postgresql, spark, hive,
trino, presto, snowflake, bigquery/bq, redshift, mysql, and
mariadb.

Highlights

• Converts DuckDB optimized logical plans into readable CTE SQL.
• Supports AST inspection through print_ast and print_ast_query.
• Supports round-trip validation through lpts_check.
• Supports input dialect normalization for common SQL syntax differences.
• Supports output dialect rendering for common warehouse and lakehouse engines.
• Exposes function descriptions and examples through duckdb_functions().
• Includes a SQLStorm TPC-H benchmark runner.

Validation

• Local SQLLogicTest suite: 2890 assertions passed in 66 test cases.
• SQLStorm TPC-H sf=0.001 baseline:
  • Total queries: 17036
  • Strict successes: 15833
  • Nondeterministic: 387
  • DuckDB errors: 807
  • LPTS timeouts: 4
  • DuckDB timeouts: 5
• SQLStorm TPC-H sf=0.0001 baseline:
  • Total queries: 17036
  • Strict successes: 15646
  • Not implemented: 578
  • DuckDB errors: 802
  • Nondeterministic: 6
  • Timeouts: 4

The preferred SQLStorm baseline for this release is sf=0.001, because it
reflects the latest stronger LPTS coverage.

Known Caveats

• LPTS serializes DuckDB's optimized logical plan, not the original SQL text or formatting.
• lpts_check can fail for nondeterministic queries, such as queries without a fully specified output order.
• Input dialect support is normalization for common syntax, not a full SQLGlot/Coral-style parser stack.
• Some SQLStorm failures are DuckDB-side errors, timeouts, or scale-factor-dependent optimizer behavior.