Usage

Definition

The WITH clause allows to define a table expression being valid within a same SELECT statement. The table expression is called "Common Table Expression"(CTE). The use case for CTE is similar to VIEW but it is more handy than VIEW. Unlike VIEW you do not need to define a CTE beforehand.

Examples

CTE allows to use recursive queries. To use a recursive query, you need to add the RECURSIVE keyword. Here is an example for WITH RECURSIVE clause usage. Table "department" represents the structure of an organization as an adjacency list.

To extract all departments under A, you can use the following recursive query:

WITH RECURSIVE subdepartment AS
(
-- non-recursive term
SELECT * FROM department WHERE name = 'A'
UNION ALL
-- recursive term
SELECT d.*
FROM
department AS d
JOIN
subdepartment AS sd
ON (d.parent_department = sd.id)
)
SELECT *
FROM subdepartment
ORDER BY name;

How Recursion Works

The meaning of the query above can be explained as follows.

Given three tables, intermediate table (IntermediateTable); work table (WorkTable); result table (ResultTable).

1) Initialize

Execute non recursive term (SELECT * FROM department WHERE name = 'A') and assign the result to ResultTable and WorkTable.

Next, assign the result of the query to IntermediateTable. Add IntermediateTable to ResultTable and WorkTable. Make IntermediateTable empty.

3) Check whether recursion is ended

If IntermediateTable is empty in 2), execution of recursion is ended. Return ResultTable.

Otherwise go to 2).

"subdepartment" is a CTE including a recursive expression since it refers to itself within its definition. In the above query first, the non-recursive term is evaluated. Then the recursive term is evaluated and the result is added to the previous result set over and over until there's no more data to process. Finally the last SELECT is executed and data is extracted from the result set.

Implementation

Parsing recursive queries

To represent CTE, new structure member "withClause" is added to SelectStmt structure which is used to store parsed information of a SELECT stament. The definition of withClause is as follows:

If RECURSIVE key word is not given, CTE is converted to a WithClause.subquery and WithClause.recursive is set to false.

If RECURSIVE key word is given, the raw parse tree stored in WithClause.subquery represents a subquery which is an UNION ALL of a non recursive term and a recursive term. Set operations other than UNION ALL between a non recursive term and a recursive term are not permitted. WithClause.subquery.subquery.larg represents "SELECT * FROM department WHERE name = 'A'" and WithClause.subquery.subquery.rarg represents "SELECT d.* FROM department AS d JOIN subdepartment AS sd ON d.parent_department = sd.id" in the example above.

Analyzing recursive queries

To analyze CTEs, we add following new members "p_ctenamespace", "p_recursive_namespace", and "p_in_with_clause" to ParseState structure.

p_ctenamespace is a namespace for non recursive CTEs and a list of RangeSubselects. Here is an excerpt from comments of ParseState:

* [1] Note that p_ctenamespace is a namespace for "relations" but distinct
* from p_relnamespace. p_ctenamespace is a list of relations that can be
* referred to in a FROM or JOIN clause (in addition to normal tables and
* views). p_relnamespace is the list of relations which already have been
* listed in such clauses and therefore can be referred to in qualified
* variable references. Also, note that p_ctenamespace is a list of
* RangeSubselects, not a list of range table entries.

p_recursive_namespace is a namespace for recursive CTEs and is a list of RangeRecursive. RangeRecursive is a newly introduced structure:

Note that SQL Server and Firebird do not allow mutual recursive query either. Oracle does not support WITH RECURSIVE, but has its own CONNECT BY syntax.

Tables defined in the WITH RECURSIVE clause are identified as RTE_RECURSIVE out of RTEKind in the RangeTblEntry in the parse tree nodes. A name space "p_recursive_namespace", whose structure type is RangeRecursive, is added to in ParseState structure.

non_recursive_term keeps the result of analyzing on non recursive term. Using this, the type of recursive query is inferenced.

Rewriter

The changes applied to the rewriter is small. fireRIRrules() recursively expand subqueries. We just do the same thing if it's a recursive query.

Generating a plan

New plan nodes "Recursion" and "Recursive scan" are added. Recursion plan is the top level plan for execution of WITH RECURSIVE query. In the example below, Recursion plan represents the execution plan for "SELECT * FROM subdepartment" part. Recursion plan always has "Append" subplan which represents the execution plan for "UNION ALL" for non recursive part and recursive part. Plan for non recursive part is nothing special and is an ordinary plan generated according to the non recursive query part. Recursive scan represents the plan for recursive part.

Executor

To execute plans for CTEs, we add new members "es_tuplestorestate", "es_rscan_tupledesc" and "es_disallow_tuplestore". es_tuplestorestate is used to remember TupleStoreState of the working table(WT). es_rscan_tupledesc is used to remeber the type information of the tuples returned by the CTEs. es_disallow_tuplestore is not actually used.

To manage recursive query execution state following structures are added.

Initializing Recursion Plan

This is done by ExecInitRecursion(). The working table (WT) is created and its pointer is stored in RecursionState.working_table. The intermediate table (IT) is created and its pointer is stored in RecursionState.intermediate_tuplestorestate.

While the initialization WT created above must be used and pointer to WT is stored in es_tuplestorestate in the master executor state (Estate).

Type info for scan is taken from the non recursive query (saved in RecursionState.subquery) and stored in RecursionState.ss and Estate/es_rscan_tupledesc.

Executing Recursion Plan

Recursion Plan is executed by ExecRecursion(). The work horse is RecursionNext(). First it execute a plan for non recursive term and the result is stored in WT. Then execute a plan for recursive term, which is a subplan of the recursion plan. If the plan returns one or more rows, they are store in IT. IT and WT are swapped and recreate IT. If no row is returned, the recursion is ended and ExecRecursion() returns NULL.

Initializing RecursiveScan plan

This is very much same as the initialization of RecursionScan except that type info for scan is taken from Estate.es_rscan_tupledesc which is initialized by ExecInitRecursion().

Executing RecursiveScan plan

Recursive scan is executed by ExecRecursiveScan(). The work horse is
RecursivescanNext(). It just returns tuples store in WT. Work table is
stored in Estate structure.

Miscellaneous changes in Executor

Hash join plan does not always recreate hash until hash join ended. If hash join has RecursiveScan as its subplan, the hash needs to recreate. To solve the problem, "has_recursivescan" is added to PlanState structure, which is true if upper plan has RecursiveScan as a subplan and hash is recreated.

exec_append_initialize_next() is executed while processing recursion. The function is now a global one.

Limitations

SEARCH and CYCLE clauses are not implemented.

Mutual recursion is not allowed.

Only the last SELECT of UNION ALL can include the recursion name.

Cost of Recursion and RecursiveScan plan are always 0.

More fun with CTEs

CTEs are being used more and more for SQL expressions. A CTE bounty page has been started to encourage their use. There are also a few snippets that rely on them