support common table expression (CTE)

[zz-jason]

Description

Is your feature request related to a problem? Please describe:

Related issues:

• support recursive common table expression (CTE) #6824 request to support recursive common table expression
• Provide 'WITH AS SELECT... ' statement support #17916 request to support common table expression to run TPC-DS and other analytic workloads
• Support CTEs (WITH statement) parser#289 request to support common table expression to be compatible with MySQL 8.0

Describe the feature you'd like:

Support the common table expression feature like MySQL 13.2.15 WITH (Common Table Expressions):

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.

There are two kinds of CTEs:

• non-recursive CTE
• recursive CTE

A typical query example of non-recursive CTE:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

A typical query example of recursive CTE:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

[pingyu]

Let me try...

[pingyu]

Find these:

• Implementing Common Table Expressions for MariaDB

• MDEV-8789 Implement non-recursive common table expressions

• MDEV-9864 - Implement recursive common table expressions

[tisonkun]

Here is the link to weekly report of CTE development: https://docs.google.com/document/d/1b-nL0c2akLL03ZhXXcQT6tj_tqQMmfAE8oKSfcaaDqE

[morgo]

@wjhuang2016 should we close this issue now since CTEs are in master?

[tisonkun]

@wjhuang2016 should we close this issue now since CTEs are in master?

A feature implemented without design doc #24147 merged 😂

[wjhuang2016]

The design doc is merged. So I close it now.