Tidb: support common table expression (CTE)

[zz-jason]

Description

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

Related issues:

• https://github.com/pingcap/tidb/issues/6824 request to support recursive common table expression
• https://github.com/pingcap/tidb/issues/17916 request to support common table expression to run TPC-DS and other analytic workloads
• https://github.com/pingcap/parser/issues/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;

Describe alternatives you've considered:

N/A

Teachability, Documentation, Adoption, Migration Strategy:

N/A

Category

Feature, Performance

Score

4

It reduces the application refactor overhead, eliminates the application code complexity, and enlarges the TiDB usage scenarios.

Workload Estimation

45 Person/Work Day

Recommended Skills

• Golang
• Query Optimization and Execution in TiDB

[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