A DuckDB extension for optimized-plan inspection and cross-system SQL transpilation. LPTS takes DuckDB's post-optimizer logical plan and reconstructs equivalent SQL as a sequence of named CTEs.

PRAGMA lpts('<query>');

Example:

INSTALL lpts FROM community;
LOAD lpts;

SET lpts_input_dialect = 'duckdb';
SET lpts_dialect = 'duckdb';

CREATE TABLE users (id INTEGER, name VARCHAR, age INTEGER);
INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 22), (3, 'Carol', 28);

PRAGMA lpts('SELECT name FROM users WHERE age > 25');

WITH
t0_scan (t0_name) AS (
    SELECT  "name"
    FROM    memory.main.users
    WHERE   (age>25)
)
SELECT  t0_name AS "name"
FROM    t0_scan;

LPTS plans the query through DuckDB, optimizes it, then serializes the optimized logical plan. For more details, check the related report.

LPTS extends DuckDB's EXPLAIN with a SQL format. EXPLAIN (FORMAT SQL) <query> returns the optimized logical plan rendered as equivalent CTE SQL — the same output as PRAGMA lpts, but exposed as a first-class EXPLAIN statement (similar to the plan-as-SQL output in Umbra).

EXPLAIN (FORMAT SQL) SELECT name FROM users WHERE age > 25;

┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan   ││
│└───────────────────────────┘│
└─────────────────────────────┘
WITH
t0_scan (t0_name) AS (
    SELECT  "name"
    FROM    memory.main.users
    WHERE   (age>25)
)
SELECT  t0_name AS "name"
FROM    t0_scan;

Because it is a genuine EXPLAIN statement, every client treats it like any other EXPLAIN: the CLI prints the SQL as plain multi-line text (no result box), while JDBC, Python, and other clients receive the standard two-column (explain_key, explain_value) EXPLAIN result with the SQL in explain_value. It honors lpts_dialect just like PRAGMA lpts, and a query that cannot be planned or converted raises a normal error.

The dialect settings accept these values:

To verify round-trip correctness, turn on the lpts_check session setting (see Settings). Once on, every top-level SELECT runs normally and is transparently compared against its LPTS rewrite. Nondeterministic queries — for example when row order or tie-breaking is not fully specified — are detected and pass without error.

• Inspect optimized DuckDB plans as SQL.
• Debug optimizer rewrites such as filter pushdown, join reordering, top-N, materialized CTEs, and subquery decorrelation.
• Generate a CTE program that communicates the optimized execution shape.
• Emit SQL for another engine with lpts_dialect.
• Convert other SQL dialect syntax to DuckDB SQL with lpts_input_dialect, then execute or inspect it.

LPTS is intended to cover all logical operators produced by optimized DuckDB SELECT plans. The regression suite round-trips all 22 TPC-H queries, the SQLStorm TPC-H corpus (17k queries, zero incorrect results), and DuckDB's own sqllogic test corpus (~3300 files run under lpts_check, gated in CI-style via make test: zero wrong translations and zero invalid-SQL failures). It exercises joins, aggregates, windows, set operations, CTEs, recursive CTEs, lateral joins, table functions, DuckLake scans, and inserts.

Anything LPTS cannot faithfully reproduce fails explicitly with an LPTS_<CODE>: ... "not supported" error (a NotImplementedException) — it never silently emits wrong SQL.

By default, LPTS avoids data-dependent optimizers so generated SQL does not bake in snapshot-specific facts such as WHERE false from current table statistics. Enable lpts_enable_data_dependent_optimizers when you want DuckDB's full optimized plan shape and accept that the SQL may depend on planning-time data.

Turn on lpts_check to verify LPTS transparently:

SET lpts_check = true;

Once on, every top-level SELECT you run is intercepted. LPTS runs the original query and its LPTS rewrite side by side and compares their result bags with an order-independent hash. The query returns its normal rows unchanged.

By default the check is strict. If the rewrite's result bag differs, LPTS raises Invalid Input Error: LPTS check failed: .... If LPTS cannot rewrite the query at all, it raises Invalid Input Error: LPTS check: unsupported query (LPTS could not check it): .... Nondeterministic queries — no fully specified order, random(), unordered aggregates, and the like — are detected and pass without error.

Queries that read LPTS's own table functions (lpts_query, print_ast_query, lpts_normalize_query) are skipped, as are queries running under statement verification (for example PRAGMA enable_verification).

Set the environment variable LPTS_CHECK_LOG to a file path to switch to log mode. With lpts_check on and LPTS_CHECK_LOG set, LPTS never raises; instead it appends one line per intercepted SELECT tagging the outcome: <n> OK (bags matched), <n> WRONG (bags differed), <n> UNSUPPORTED (LPTS deliberately refused the query with an LPTS_<CODE>: ... "not supported" error), <n> FAIL (LPTS could not rewrite it and the error was not a deliberate refusal — a translation bug), or <n> NONDETERMINISTIC: <reason> (rewritten but nondeterministic, so the comparison is not trusted, with the heuristic's explanation). <n> is the 1-based interception index. This is how DuckDB's own sqllogic corpus is run through LPTS (see docs/test.md).

CREATE TABLE events (id INTEGER, ts TIMESTAMP, name VARCHAR, "order" INTEGER);
INSERT INTO events VALUES
    (1, TIMESTAMP '2024-01-15 08:09:10', 'alpha', 10),
    (11, TIMESTAMP '2024-01-16 11:12:13', 'beta', 20);

SET lpts_dialect = 'postgres';

-- Return generated CTE SQL directly in the shell.
PRAGMA lpts(
    'SELECT strftime(ts, ''%Y-%m-%d'') AS day
     FROM events
     WHERE id > 10
     ORDER BY day'
);

-- Return generated CTE SQL as a table row, useful in scripts and tests.
SELECT sql
FROM lpts_query(
    'SELECT strftime(ts, ''%Y-%m-%d'') AS day
     FROM events
     WHERE id > 10
     ORDER BY day'
);

WITH
t0_scan (ts) AS (
    SELECT  ts
    FROM    events
    WHERE   (id>10)
)
SELECT    to_char(ts, 'YYYY-MM-DD') AS "day"
FROM      t0_scan
ORDER BY  to_char(ts, 'YYYY-MM-DD') ASC NULLS LAST;

SET lpts_dialect = 'duckdb';

-- Turn on transparent round-trip checking once per session.
SET lpts_check = true;

-- Runs normally and returns its rows; raises only if LPTS rewrites it wrong.
SELECT name FROM events WHERE id > 10 ORDER BY name;

name
----
beta

-- Print the AST tree to stdout for interactive debugging.
PRAGMA print_ast('SELECT name FROM events WHERE id > 10 ORDER BY name');

-- Return the AST tree as a table row, useful for tools and regression tests.
SELECT ast
FROM print_ast_query('SELECT name FROM events WHERE id > 10 ORDER BY name');

SET lpts_input_dialect = 'mysql';

-- Normalize source-dialect SQL to DuckDB SQL before planning or execution.
SELECT sql
FROM lpts_normalize_query(
    'SELECT `order`, DATE_FORMAT(ts, ''%Y-%m-%d %H:%i:%s'') AS formatted FROM events LIMIT 5, 10'
);

SELECT "order", strftime(ts, '%Y-%m-%d %H:%M:%S') AS formatted FROM events LIMIT 10 OFFSET 5

• Building - build, local loading, updating, and CLion setup
• Tests - SQLLogicTest conventions and the DuckDB-corpus coverage gate (make test)
• Benchmark - SQLStorm benchmark runner

Maintainer: ila