WITH (Common Table Expressions)

Syntax

Non-recursive (standard) CTE:

SQL

WITH cte [, ...]

cte:
    expression_name[(column_name, ...)]
    AS
    select_statement

Remarks

WITH clauses are supported as part of SELECT, INSERT, UPDATE, DELETE, and REPLACE 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 attempts to materialize CTEs. See Materializing Common Table Expressions for more information.
If a CTE defined in the WITH clause is not referenced in the SELECT statement, it does not affect the execution of the query.
The engine variable internal_max_cte_depth defines the maximum number of common table expressions (CTEs) that can be nested. The default value of this variable is 128.
The engine variable max_recursive_cte_iterations sets the maximum number of times the recursive case is executed.

Examples

Non-recursive CTEs:

SQL

WITH myCTE AS
(select col from myTable)
select col from myCTE;

SQL

WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;

SQL

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;

SQL

WITH foo AS
(WITH bar AS
(SELECT * FROM t) SELECT * FROM bar)
SELECT * FROM foo;

SQL

WITH EmpSal(averageSal) AS
(SELECT AVG(Salary) FROM Employee)
SELECT EmpID, Name, Salary FROM Employee, EmpSal 
WHERE Employee.Salary > EmpSal.averageSal;

SQL

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

By using the variable materialize_ctes, common table expressions (CTEs) are materialized when queries with CTEs are run. The materialized results can then be referenced multiple times by the parent query. The CTE queries are cached, which results in optimized performance. This is particularly useful for running queries that contain an expensive CTE that produces a relatively small number of rows and is used two or more times.

The materializing CTEs functionality is enabled by default.

Enable or disable this functionality by setting the materialize_ctes engine variable, or setting it per session via the MATERIALIZE_CTES session variable:

SQL

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_ctes engine variable can be set on a per query basis by adding an OPTION expression at the end of the query:

SQL

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:

SQL

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 as m_foo_i, where foo is the name of the CTE in the original query, and i 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_ctes variable.
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. This optimization is enabled only when the materialize_ctes session variable is set to AUTO. An example of how a query is internally rewritten by SingleStore is shown below.

SQL

WITH foo AS (SELECT * FROM t)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

is transformed to:

SQL

WITH foo AS (SELECT * FROM t WHERE t.a = 1 OR t.b = 2)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

Unlike normal predicate pushdown, the original top-level predicates remain.

WITH (Common Table Expressions)

On this page

Syntax

Remarks

Examples

Materializing Common Table Expressions

Remarks

Predicate Pushdown

Was this article helpful?

On this page

Was this article helpful?