explain-lpts

LPTS Pipeline: Comprehensive Reference

1. Overview

LPTS (Logical Plan To SQL) converts DuckDB's internal logical plan for a SQL query into an equivalent SQL string made of CTEs (Common Table Expressions). Each CTE corresponds to one operator from the plan.

The pipeline has three phases:

Logical Plan  →  AST  →  CTE List  →  SQL String

Phase 1 (LogicalPlanToAst) walks DuckDB's LogicalOperator tree and builds a dialect-agnostic AST. Phase 2 (AstToCteList) flattens the AST into an ordered list of CteNode objects. Phase 3 (CteList::ToQuery) serializes the flat list into SQL.

2. Phase 1: LogicalPlanToAst

File: src/lpts_pipeline.cpp Signature: unique_ptr<AstNode> LogicalPlanToAst(ClientContext &context, unique_ptr<LogicalOperator> &plan)

Walks the LogicalOperator tree recursively. For each node:

1. Recursively process children first (bottom-up)
2. Extract operator-specific data (table name, column bindings, expressions, join type, etc.)
3. Create the corresponding AstNode and attach children

2.1 Column Binding Map

The key bookkeeping challenge is tracking ColumnBinding → CTE column name across operators. A ColumnBinding is a (table_index, column_index) pair identifying a column in the plan.

Each AstGetNode (table scan) establishes the initial mapping: physical column names are prefixed with t<table_index>_ to create CTE-scoped names. Downstream operators (filter, project, aggregate) use ExpressionToAliasedString() to resolve ColumnBinding references into CTE column names.

2.2 Operator Extraction Logic

The reference implementation is CreateCteNode() in src/logical_plan_to_sql.cpp. For each operator type, the extraction pattern is:

LOGICAL_GET (table scan):

• Extract catalog, schema, table name from LogicalGet
• Extract column names from get.names + get.column_ids
• Extract table filters from get.table_filters (pushdown filters)
• Generate CTE column names: t<table_index>_<column_name>

LOGICAL_FILTER (WHERE clause):

• Extract filter expressions from LogicalFilter::expressions
• Convert each expression to a SQL string using ExpressionToAliasedString()
• Passthrough: child CTE column names are inherited

LOGICAL_PROJECTION (column selection):

• Extract projected expressions from LogicalProjection::expressions
• Each expression becomes a column in the SELECT list
• Generate new CTE column names: t<table_index>_<name>

LOGICAL_AGGREGATE_AND_GROUP_BY:

• Extract group-by columns from LogicalAggregate::groups
• Extract aggregate expressions from LogicalAggregate::expressions
• Generate CTE column names for both groups and aggregates

LOGICAL_COMPARISON_JOIN:

• Extract join type from LogicalComparisonJoin::join_type
• Extract join conditions from LogicalComparisonJoin::conditions
• Combine left and right child CTE column names

LOGICAL_UNION:

• Extract is_union_all flag
• CTE column names derived from the left-hand side

LOGICAL_INSERT:

• Extract target table name
• Extract OnConflictAction (NOTHING, REPLACE, etc.)

LOGICAL_ORDER_BY:

• Extract BoundOrderByNode entries (expression + order type)
• Passthrough child CTE column names

LOGICAL_LIMIT:

• Extract limit and offset expressions (may be constants or bound expressions)
• Passthrough child CTE column names

LOGICAL_DISTINCT:

• Passthrough child CTE column names

2.3 Expression Resolution

ExpressionToAliasedString() (in src/logical_plan_to_sql.cpp) converts DuckDB bound expressions into SQL strings:

Expression Type	Example Output
BoundColumnRefExpression	t0_age (resolved via column binding map)
BoundConstantExpression	CAST(25 AS INTEGER)
BoundComparisonExpression	(t0_age) > (CAST(25 AS INTEGER))
BoundFunctionExpression	sum(t0_amount)
BoundCastExpression	CAST(t0_val AS VARCHAR)
BoundReferenceExpression	Used in aggregates to reference group-by columns

3. AST Node Hierarchy

File: src/include/lpts_ast.hpp

AstNode (abstract base, has vector<unique_ptr<AstNode>> children)
├── AstGetNode        — table scan
│     Fields: catalog, schema, table_name, table_index, column_names,
│             cte_column_names, table_filters
├── AstFilterNode     — WHERE clause
│     Fields: conditions (vector<string>)
├── AstProjectNode    — column selection
│     Fields: expressions, cte_column_names, table_index
├── AstAggregateNode  — GROUP BY + aggregates
│     Fields: group_by_columns, aggregate_expressions, cte_column_names
├── AstJoinNode       — JOIN
│     Fields: join_type, conditions, cte_column_names
├── AstUnionNode      — UNION / UNION ALL
│     Fields: is_union_all, cte_column_names
├── AstInsertNode     — INSERT INTO
│     Fields: target_table, action_type
├── AstOrderNode      — ORDER BY
│     Fields: order_items, cte_column_names
├── AstLimitNode      — LIMIT / OFFSET
│     Fields: limit_str, offset_str, cte_column_names
└── AstDistinctNode   — SELECT DISTINCT
      Fields: cte_column_names

Each node has:

• children vector — tree structure (mirrors logical plan)
• ToString(int indent) — pretty-print for debugging
• NodeType() — returns type name string
• GetExtraInfo() — key-value pairs for box-rendered tree printer

4. Phase 2: AstToCteList

File: src/lpts_pipeline.cpp Signature: unique_ptr<CteList> AstToCteList(const AstNode &root, SqlDialect dialect)

Walks the AST in post-order (children first, then parent). For each AstNode:

1. Recursively process children
2. Create the corresponding CteNode with an incremental index
3. The root node produces either a FinalReadNode (SELECT) or InsertNode (INSERT)

4.1 CTE Naming Convention

CTEs are named by their operator type and index:

• scan_0, scan_3 — GetNode
• filter_1 — FilterNode
• projection_2 — ProjectNode
• aggregate_4 — AggregateNode
• join_inner_5 — JoinNode (inner)
• union_6 — UnionNode
• order_7 — OrderNode
• limit_8 — LimitNode
• distinct_9 — DistinctNode

4.2 CTE Node Hierarchy

File: src/include/logical_plan_to_sql.hpp

CteBaseNode (base)
├── RootNode (virtual) — the final statement
│   ├── FinalReadNode — closing SELECT that renames CTE columns back to original names
│   └── InsertNode    — INSERT INTO ... SELECT * FROM <last_cte>
└── CteNode (virtual) — one CTE in the WITH clause
    ├── GetNode        — SELECT col1, col2 FROM table
    ├── FilterNode     — SELECT * FROM <child> WHERE ...
    ├── ProjectNode    — SELECT expr1, expr2 FROM <child>
    ├── AggregateNode  — SELECT group_cols, agg_exprs FROM <child> GROUP BY ...
    ├── JoinNode       — SELECT * FROM <left> JOIN <right> ON ...
    └── UnionNode      — SELECT * FROM <left> UNION [ALL] SELECT * FROM <right>

Each CteNode has:

• ToQuery() — produces the SQL fragment inside AS (...)
• ToCteQuery() — wraps with the CTE name and column list
• cte_name — the name used in the WITH clause
• child_cte_name — reference to the input CTE

5. Phase 3: CTE List to SQL

CteList::ToQuery(true) serializes all CTEs into a single SQL string:

WITH scan_0(t0_age, t0_name) AS (SELECT age, name FROM memory.main.users),
filter_1 AS (SELECT * FROM scan_0 WHERE (t0_age) > (CAST(25 AS INTEGER))),
projection_2(t1_name) AS (SELECT t0_name FROM filter_1)
SELECT t1_name AS name FROM projection_2;

The FinalReadNode at the end renames CTE-internal column names back to the original user-facing column names (e.g., t1_name AS name).

6. Dialect Support

Enum: SqlDialect in src/include/lpts_pipeline.hpp Values: DUCKDB (default), POSTGRES

Dialect differences handled in AstToCteList:

Aspect	DuckDB	Postgres
Table references	memory.main.users (fully-qualified)	users (unqualified)
Date parsing	strptime(col, '%Y-%m-%d')	to_date(col, 'YYYY-MM-DD')
Boolean literals	true	TRUE

Set via: SET lpts_dialect = 'postgres';

7. Key Source Files

File	Purpose
src/lpts_pipeline.cpp	LogicalPlanToAst and AstToCteList implementations
src/logical_plan_to_sql.cpp	Reference implementation: CreateCteNode() is the canonical operator extraction logic
src/include/lpts_ast.hpp	AST node class hierarchy
src/lpts_ast.cpp	AST ToString() implementations
src/lpts_ast_renderer.cpp	Box-rendered ASCII tree printer for AST debugging
src/include/lpts_pipeline.hpp	Pipeline function declarations + SqlDialect enum
src/include/logical_plan_to_sql.hpp	CTE node class hierarchy
src/lpts_helpers.cpp	Utility functions (VecToSeparatedList, EscapeSingleQuotes, etc.)
src/include/lpts_debug.hpp	Debug flag and LPTS_DEBUG_PRINT macro