explain-lpts-ddl

name	explain-lpts-ddl
description	Reference for LPTS DDL syntax — PRAGMA lpts, lpts_query, the lpts_check round-trip setting, lpts_normalize_query, dialect settings, and print_ast. Auto-loaded when discussing LPTS usage, pragmas, table functions, round-trip checks, or dialect configuration.

LPTS DDL Overview

LPTS extends DuckDB with pragmas, table functions, and a session setting for converting logical plans to SQL strings. All functions are registered in src/lpts_extension.cpp.

Converting a Query to CTE SQL

PRAGMA lpts('query') — Interactive use. Returns the CTE SQL as a single-row result.

D PRAGMA lpts('SELECT name FROM users WHERE age > 25');
┌─────────────────────────────────────────────────────────────────────┐
│ sql                                                                 │
├─────────────────────────────────────────────────────────────────────┤
│ WITH scan_0(t0_age, t0_name) AS (SELECT age, name FROM ...),       │
│ filter_1 AS (SELECT * FROM scan_0 WHERE (t0_age) > (CAST(25 ...))),│
│ projection_2(t1_name) AS (SELECT t0_name FROM filter_1)            │
│ SELECT t1_name AS name FROM projection_2;                           │
└─────────────────────────────────────────────────────────────────────┘

lpts_query('query') — Table function for programmatic use and tests:

SELECT sql FROM lpts_query('SELECT name FROM users WHERE age > 25');

Both call the same pipeline:

auto ast = LogicalPlanToAst(context, planner.plan);
SqlDialect dialect = ReadDialect(context);
auto cte_list = AstToCteList(*ast, dialect);
string result_sql = cte_list->ToQuery(true);

Round-Trip Correctness Check

SET lpts_check = true — The primary correctness mechanism. Once on, every top-level SELECT 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.

D SET lpts_check = true;
D SELECT name FROM users WHERE age > 25;   -- runs normally; raises if rewritten wrong
----
Carol

By default the check is strict. On a mismatch it raises Invalid Input Error: LPTS check failed: ...; when LPTS cannot rewrite the query it raises Invalid Input Error: LPTS check: unsupported query (LPTS could not check it): .... Nondeterministic queries (no fully specified order, random(), unordered aggregates, etc.) are detected and pass without error. Queries that read LPTS's own table functions (lpts_query, print_ast_query, lpts_normalize_query) and queries running under statement verification (for example PRAGMA enable_verification) are skipped.

Setting the LPTS_CHECK_LOG environment variable to a file path switches to log mode: with lpts_check on, LPTS never raises and instead appends one line per intercepted SELECT — <n> FAIL (could not rewrite), <n> OK (bags matched), <n> WRONG (bags differed), or <n> NONDETERMINISTIC: <reason> (rewritten but nondeterministic, with the heuristic's explanation) — where <n> is the 1-based interception index. This is how DuckDB's own sqllogic corpus is run through LPTS.

Every new test must turn on lpts_check and exercise the feature with a bare query. This is the authoritative correctness check for LPTS.

Normalizing Input-Dialect SQL

lpts_normalize_query('query') — Table function returning the input-dialect SQL normalized to DuckDB SQL (honors lpts_input_dialect):

SELECT sql FROM lpts_normalize_query('SELECT `order` FROM events LIMIT 5, 10');

AST Debugging

PRAGMA print_ast('query') — Visualizes the AST tree structure:

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

Useful for debugging the LogicalPlanToAst phase. The box-rendered tree is produced by src/lpts_ast_renderer.cpp.

Dialect Configuration

-- Set dialect to PostgreSQL (default: 'duckdb')
SET lpts_dialect = 'postgres';

-- Convert with Postgres-compatible syntax
PRAGMA lpts('SELECT * FROM users');
-- Table references will be unqualified (no catalog.schema prefix)

The lpts_dialect setting is registered as a DuckDB extension option and read via ReadDialect() in src/lpts_extension.cpp. Valid values: 'duckdb', 'postgres'.

Supported Query Patterns

Pattern	Operators Used	Example
Simple scan	GET, PROJECTION	SELECT * FROM t
Column selection	GET, PROJECTION	SELECT a, b FROM t
Filter	GET, FILTER, PROJECTION	SELECT * FROM t WHERE x > 5
Grouped aggregate	GET, AGGREGATE, PROJECTION	SELECT a, SUM(b) FROM t GROUP BY a
HAVING	GET, AGGREGATE, FILTER	SELECT a, SUM(b) FROM t GROUP BY a HAVING SUM(b) > 10
Inner join	GET, JOIN, PROJECTION	SELECT ... FROM t1 JOIN t2 ON ...
UNION / UNION ALL	GET, UNION, PROJECTION	SELECT a FROM t1 UNION ALL SELECT a FROM t2
ORDER BY	GET, ORDER, PROJECTION	SELECT * FROM t ORDER BY a
LIMIT / OFFSET	GET, LIMIT, PROJECTION	SELECT * FROM t LIMIT 10 OFFSET 5
DISTINCT	GET, DISTINCT, PROJECTION	SELECT DISTINCT a FROM t
Scalar functions	GET, PROJECTION	SELECT upper(name) FROM t
INSERT	GET, INSERT	INSERT INTO t SELECT * FROM s

Limitations

• Subqueries in WHERE (correlated and uncorrelated) are partially supported
• Window functions are not yet supported
• FULL OUTER JOIN, CROSS JOIN, SEMI JOIN may hit NotImplementedException
• CTEs in the source query (WITH clauses) are expanded by DuckDB's planner before LPTS sees them — the output will not preserve the original CTE structure
• Very complex expressions (CASE WHEN, nested casts) may produce verbose output

Key Source Files

• src/lpts_extension.cpp — Extension entry point, registers all pragmas and settings
• src/lpts_pipeline.cpp — LogicalPlanToAst and AstToCteList implementations
• src/include/lpts_pipeline.hpp — SqlDialect enum and pipeline function declarations
• src/lpts_ast_renderer.cpp — Box-rendered AST tree printer for print_ast