Provide 'WITH AS SELECT... ' statement support

[xyxiaoyou]

Feature Request

Is your feature request related to a problem? Please describe:
When I was doing tPC-DS testing, I found that 'with as select...'Syntax is not supported, so many complex analytic SQL cannot be executed.

Describe the feature you'd like:
Provide 'with as select...‘ SQL statement support for creating temporary tables.

Describe alternatives you've considered:
It is possible to replace this statement with a subquery, but there are query performance issues when the same temporary table is used in large Numbers.

Teachability, Documentation, Adoption, Migration Strategy:
For example, the following statements should be supported.
with frequent_ss_items as (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999 + 1,1999 + 2,1999 + 3) group by substr(i_item_desc,1,30),i_item_sk,d_date having count(*) >4), max_store_sales as (select max(csales) tpcds_cmax from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk)), best_ss_customer as (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select * from max_store_sales)) select c_last_name,c_first_name,sales from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales from catalog_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and cs_sold_date_sk = d_date_sk and cs_item_sk in (select item_sk from frequent_ss_items) and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) and cs_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name union all select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales from web_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer) and ws_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name) order by c_last_name,c_first_name,sales limit 100;

[wwar]

This syntax is known as a CTE / Common Table Expression, and available in MySQL 8.0+

It does not necessarily use a temporary table, since in many cases the optimizer can rewrite the query. But there is some overlap in use cases with temporary tables (CTEs are usually a better fit).

See:

#17472
#6824
pingcap/parser#289

[zz-jason]

I'm going to close this issue because it's replicated with #17472. We can discuss and track the progress in that issue.