WITH (Common Table Expressions)
On this page
Specifies a temporary named result set, referred to as a Common Table Expression (CTE).
Syntax
Non-recursive (standard) CTE:
WITH cte [, ...]cte:expression_name[(column_name, ...)]ASselect_statement
Recursive CTE:
WITH RECURSIVE cte [, ...]cte:expression_name [(column_name, ...)] AS (<base_case>UNION ALL<recursive_case>) select_statement;
Remarks
-
WITH
clauses are supported as part ofSELECT
,INSERT
,UPDATE
,DELETE
, andREPLACE
statements for non-recursive CTEs. -
Recursive CTEs must have the
materialize_
engine variable enabled to work.ctes If this engine variable is not enabled, an error is generated when attempting to execute a recursive CTE. ERROR 2730 UNKNOWN_ERR_CODE: Unsupported recursive common table expression query shape:the engine variable 'materialize_ctes' is currently set to OFF.In order to run the recursive common table expression, you need to set 'materialize_ctes' to 'AUTO' or 'ALL'.See the Materializing Common Table Expressions documentation for additional information. -
WITH RECURSIVE
clauses defining recursive CTEs are only supported forSELECT
statements for both the base case and the recursive case. -
SingleStore supports materializing CTEs without recomputing them when the query contains
UNION/UNION ALL
and otherSET
operations.To enable the feature, set the engine variable allow_
tomaterialize_ cte_ with_ union TRUE
.The default value for this engine variable is FALSE
for version 8.1. 19 and earlier. For more information about materializing CTEs, see the WITH (Common Table Expressions) section below. -
By default, SingleStore Helios attempts to materialize CTEs.
See Materializing Common Table Expressions for more information. -
If a CTE defined in the
WITH
clause is not referenced in theSELECT
statement, it does not affect the execution of the query. -
The engine variable
internal_
defines the maximum number of common table expressions (CTEs) that can be nested.max_ cte_ depth The default value of this variable is 128
. -
The engine variable
max_
sets the maximum number of times the recursive case is executed.recursive_ cte_ iterations
Non-recursive vs recursive CTEs
A non-recursive standard CTE does not reference itself within the statement it is executed in.
A recursive CTE references itself.
A common example of hierarchical data is an organization chart where employees are listed under their direct managers.
This shows, in a graphical form, that a recursive CTE can retrieve from the database by recursively expanding the result set starting at the top with Maggie.
A recursive CTE contains two SELECT
statements joined by a UNION ALL
clause.SELECT
statement is the base case which is executed once.SELECT
statement is the recursive case where the CTE references itself, expanding repeatedly until the termination condition is met.
The recursive CTE will use the data types from the columns in the base case if the columns in the recursive case have different data types.
Limitations
There are several limitations on which operations are allowed in the SELECT statements for the recursive case of recursive CTEs.
-
The base case in a recursive CTE must be a sharded table.
-
SELECT DISTINCT
-
EXCEPT/MINUS
-
LEFT [OUTER] JOIN
-
RIGHT [OUTER] JOIN
-
INTERSECT
-
UNION
-
GROUP BY
-
ORDER BY
-
LIMIT
-
Aggregate functions like
COUNT
,MAX
, etc. -
Window functions like
RANK
,DENSE RANK
, etc.
Troubleshooting timed-out recursive CTEs
If the recursive CTE takes an inordinate amount of time to execute or times out, there can be an issue with the data’s structure or with the creation of the CTE itself.
Your data structure may not be truly hierarchical.
Your recursive CTE query can be incorrectly coded and create an infinite loop.
Both the query itself and the data must allow the recursive case to terminate.
Known issue with recursive CTEs
If a recursive CTE results in the error "'Table'.materialize_
setting may be disabled.materialize_
setting was not enabled by default.
Examples
Non-recursive CTEs:
WITH myCTE AS(select col from myTable)select col from myCTE;
WITH myCTE (colAlias) AS(select col from myTable)select colAlias from myCTE;
WITH orderCTE AS(select o_orderkey from orders),lineitemCTE AS(select l_orderkey from lineitem)select count(*) from orderCTE join lineitemCTE on o_orderkey = l_orderkey;
WITH foo AS(WITH bar AS(SELECT * FROM t) SELECT * FROM bar)SELECT * FROM foo;
WITH EmpSal(averageSal) AS(SELECT AVG(Salary) FROM Employee)SELECT EmpID, Name, Salary FROM Employee, EmpSalWHERE Employee.Salary > EmpSal.averageSal;
WITH ObjectCTE (Name, Id, Date) AS(SELECT objname, objectid, invoicedate FROM Inventory)INSERT INTO Itemlist(Name, objectid, createdDate)SELECT Name, Id, Date FROM ObjectCTE;SELECT * FROM Itemlist;
Recursive CTEs:
Using the organization chart described above, this example illustrates how recursive CTEs simplify the retrieval of hierarchical data.
CREATE TABLE cte_emp(id INT, mgr_id INT, name VARCHAR(30));INSERT INTO cte_emp VALUES(1, NULL, "Maggie"), (2, 1, "John"), (3, 1, "Felix"),(4, 2, "Joan"), (5, 2, "Richard"), (6, 3, "Andy"), (7, 3, "Jill");
WITH RECURSIVE org_chart (emp_id, mgr_id, name, level) AS (SELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, 0 AS levelFROM cte_empWHERE cte_emp.mgr_id is NULLUNION ALLSELECT cte_emp.id, cte_emp.mgr_id, cte_emp.name, level+1FROM cte_empINNER JOIN org_chartON cte_emp.mgr_id = org_chart.emp_id) SELECT * FROM org_chart;
+------+--------+---------+-------+
| id | mgr_id | name | level |
+------+--------+---------+-------+
| 1 | NULL | Maggie | 0 |
| 3 | 1 | Felix | 1 |
| 2 | 1 | John | 1 |
| 5 | 2 | Richard | 2 |
| 4 | 2 | Joan | 2 |
| 7 | 3 | Jill | 2 |
| 6 | 3 | Andy | 2 |
+------+--------+---------+-------+
Another example is a graph of connected nodes.id
column is the node id and pr
is the parent node to the node in the id
column.
CREATE TABLE cte_g (id INT, pr INT);INSERT INTO cte_g VALUES (0, null), (1, 0), (2, 0),(3, 1), (4, 1), (5, 2), (6, 2);
WITH RECURSIVE depths AS (SELECT 0 depth, *FROM cte_gWHERE id = 0UNION ALLSELECT depth+1, cte_g.id, cte_g.prFROM cte_g, depthsWHERE cte_g = depths.id)SELECT depth, id FROM depths;
+-------+------+
| depth | id |
+-------+------+
| 0 | 0 |
| 1 | 1 |
| 1 | 2 |
| 2 | 5 |
| 2 | 3 |
| 2 | 4 |
| 2 | 6 |
+-------+------+
The following examples show the recursive CTE will use the data types from the columns in the base case if the columns in the recursive case have different data types.
CREATE TABLE cte_g (id INT, pr INT, SHARD KEY(id));INSERT INTO cte_g VALUES (0, null), (1, 0), (2, 0),(3, 1), (4, 1), (5, 2), (6, 2);
WITH RECURSIVE depths AS (SELECT 0 depth, id, prFROM cte_gWHERE pr IS NULLUNION ALLSELECT CAST((depth+1) AS DECIMAL(10, 2)), cte_g.id, cte_g.prFROM cte_g, depthsWHERE cte_g.pr = depths.id)SELECT depth, id FROM depths;
+-------+------+
| depth | id |
+-------+------+
| 0 | 0 |
| 1 | 2 |
| 2 | 5 |
| 2 | 4 |
| 1 | 1 |
| 2 | 3 |
| 2 | 6 |
+-------+------+
WITH RECURSIVE routes (id, path) AS (SELECT id, cast ( 1 as char(30) )FROM cte_gWHERE pr IS NULLUNION ALLSELECT t.id, CONCAT ( routes.path, '-->', t.id )FROM cte_gJOIN routesON routes.id = cte_g.pr)SELECT * FROM routes;
+----+-------------------------+
| id | cast ( 1 as char(30) ) |
+----+-------------------------+
| 1 | 1-->1 |
| 3 | 1-->1-->3 |
| 0 | 1 |
| 2 | 1-->2 |
| 5 | 1-->2-->5 |
| 4 | 1-->1-->4 |
+----+-------------------------+
Materializing Common Table Expressions
By using the variable materialize_
, common table expressions (CTEs) are materialized when queries with CTEs are run.
The materializing CTEs functionality is enabled by default.
Enable or disable this functionality by setting the materialize_
engine variable, or setting it per session via the MATERIALIZE_
session variable:
SET MATERIALIZE_CTES = { 'ALL' | 'AUTO' | 'OFF' };
The following settings are available:
-
ALL
: Each CTE that appears more than once in the query is materialized into an internal result table. -
AUTO
: This is the default value.A suite of optimizations are enabled. Currently, only the Predicate Pushdown optimization is available. -
OFF
: CTEs are inlined, which is similar to VIEW definitions.
If users can't or don't want to use engine variables, the materialize_
engine variable can be set on a per query basis by adding an OPTION
expression at the end of the query:
SELECT * FROM foo OPTION(materialize_ctes="OFF");SELECT * FROM foo OPTION(materialize_ctes="AUTO");SELECT * FROM foo OPTION(materialize_ctes="ALL");
Materialization can also be turned off per CTE:
WITH foo as (select with(materialize=off) * from titanic),bar as (select * from titanic)select * from foo, bar;
Remarks
-
Materialized CTEs appear in the
EXPLAIN
asm_
, wherefoo_ i foo
is the name of the CTE in the original query, andi
is an integer value generated during name normalization. -
The results table that is created from a materialized CTE is analogous to an intermediate results table that is created as part of a distributed join query.
-
Plans for queries with CTEs are cacheable regardless of the setting of the
materialize_
variable.ctes -
Some
SELECT
statements may not be materializable.For example, selects with limits, selects with aggregates and no group by, and selects without FROM
clauses cannot be materialized.All CTEs whose selects are non-materializable will get inlined with the parent query.
Predicate Pushdown
To avoid materializing unused rows, a rudimentary form of predicate pushdown in CTEs is supported.materialize_
session variable is set to AUTO
.
WITH foo AS (SELECT * FROM t)SELECT *FROM foo, foo as barWHERE foo.a = 1 AND bar.b = 2;
is transformed to:
WITH foo AS (SELECT * FROM t WHERE t.a = 1 OR t.b = 2)SELECT *FROM foo, foo as barWHERE foo.a = 1 AND bar.b = 2;
Unlike normal predicate pushdown, the original top-level predicates remain.
Last modified: February 15, 2024