Support CTEs (WITH statement)

[tlightsky]

Feature Request

any plan to support with clause from SQL:1999 and supported by MySQL 8.0

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-syntax

[morgo]

@tlightsky Thank you for the feature request. CTEs are currently on the roadmap.

[tlightsky]

@morgo thanks for reply
need this feature recently,
maybe i can make a pr when i finish it,
is there any hint that i can add it quickly?
(maybe just point out where i should make changes)

[tlightsky]

i have do some work about adding CTEs (WITH statement),
need some help to fix failed tests

[zz-jason]

@tlightsky Common Table Expression is a huge feature, we need a detailed discuss and design document about it.

[zz-jason]

I've created a github project to trace the progress: https://github.com/pingcap/tidb/projects/21

[tlightsky]

@zz-jason
thanks for response,

Currently, my plan is to parse it to ast tree(without recursive),
then expand those CTEs to form a SQL without WITH Statement,
so i can use WITH Statement in platforms like MySQL 5.7, Clickhouse

Also noticed OceanBase support non-recursive CTEs by only expanding them too,
would be nice to have CTEs support in TiDB even only just expand those subqueries

[kyleconroy]

Is this still on the roadmap? I don't need CTE support in TiDB, just in the parser.

[kennytm]

yes, compatibility with mysql 8 is always desirable.

(you may also want to follow pingcap/tidb#17472 for the overall roadmap of CTE support in TiDB, which drives the parser implementation.)

[zz-jason]

Is this still on the roadmap? I don't need CTE support in TiDB, just in the parser.

@kyleconroy Oh, seems you are using this parser as a library in a project. May I know your use scenarios?

[wjhuang2016]

Supported by #1207
/cc @tlightsky @kyleconroy