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
Remarks
-
WITH
clauses are supported as part ofSELECT
,INSERT
,UPDATE
,DELETE
, andREPLACE
statements. -
CTEs containing
UNION/UNION ALL
clauses are not materialized.For more information about materializing CTEs, see the WITH (Common Table Expressions) section below. -
SingleStore supports only non-recursive CTEs.
See recursive tree or graph expansion in a stored procedure or client application. -
By default, SingleStore will always attempt to inline CTEs into the parent query; however, you can cache CTEs through materialization to improve the performance of certain complex queries.
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
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;
Materializing Common Table Expressions
Warning
The materialize_
variable is experimental and should not be enabled in production.
By using the variable materialize_
, common table expressions (CTEs) are materialized when queries with CTEs are run.
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
: .A suite of optimizations are enabled. Currently, only the Predicate Pushdown optimization is available. -
OFF
: This is the default value.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. -
Non-recursive CTEs are not materialized if they contain a
UNION/UNION ALL
clause in any part of the query statement.
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