Replace random CTE identifier in execution plan with actual name

[songrijie]

Enhancement

Current behavior

In execution plan, CTE is marked with random names like CTE_x in id column. In a few complex scenarios, it's not easy to map the identifier to SQL block. Here is an example from official doc.

CTE_0, CTE_1 and CTE_2 are generated by planner.

mysql> explain WITH books_authored_by_rm AS (
    ->     SELECT *
    ->     FROM books b
    ->     LEFT JOIN book_authors ba ON b.id = ba.book_id
    ->     WHERE author_id = 2299112019
    -> ), books_with_average_ratings AS (
    ->     SELECT
    ->         b.id AS book_id,
    ->         AVG(r.score) AS average_rating
    ->     FROM books_authored_by_rm b
    ->     LEFT JOIN ratings r ON b.id = r.book_id
    ->     GROUP BY b.id
    -> ), books_with_orders AS (
    ->     SELECT
    ->         b.id AS book_id,
    ->         COUNT(*) AS orders
    ->     FROM books_authored_by_rm b
    ->     LEFT JOIN orders o ON b.id = o.book_id
    ->     GROUP BY b.id
    -> )
    -> SELECT
    ->     b.id AS `book_id`,
    ->     b.title AS `book_title`,
    ->     br.average_rating AS `average_rating`,
    ->     bo.orders AS `orders`
    -> FROM
    ->     books_authored_by_rm b
    ->     LEFT JOIN books_with_average_ratings br ON b.id = br.book_id
    ->     LEFT JOIN books_with_orders bo ON b.id = bo.book_id
    -> ;
+-------------------------------------+----------+-----------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows  | task      | access object                              | operator info                                                                                                                                                                                         |
+-------------------------------------+----------+-----------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_109                        | 1.48     | root      |                                            | left outer join, equal:[eq(bookshop.books.id, bookshop.books.id)]                                                                                                                                     |
| ├─Selection_117(Build)              | 0.95     | root      |                                            | not(isnull(bookshop.books.id))                                                                                                                                                                        |
| │ └─CTEFullScan_118                 | 1.18     | root      | CTE:bo                                     | data:CTE_2                                                                                                                                                                                            |
| └─HashJoin_111(Probe)               | 1.48     | root      |                                            | left outer join, equal:[eq(bookshop.books.id, bookshop.books.id)]                                                                                                                                     |
|   ├─Selection_114(Build)            | 0.95     | root      |                                            | not(isnull(bookshop.books.id))                                                                                                                                                                        |
|   │ └─CTEFullScan_115               | 1.18     | root      | CTE:br                                     | data:CTE_1                                                                                                                                                                                            |
|   └─CTEFullScan_113(Probe)          | 1.48     | root      | CTE:b                                      | data:CTE_0                                                                                                                                                                                            |
| CTE_2                               | 1.18     | root      |                                            | Non-Recursive CTE                                                                                                                                                                                     |
| └─Projection_86(Seed Part)          | 1.18     | root      |                                            | bookshop.books.id, Column#35                                                                                                                                                                          |
|   └─HashAgg_87                      | 1.18     | root      |                                            | group by:bookshop.books.id, funcs:count(1)->Column#35, funcs:firstrow(bookshop.books.id)->bookshop.books.id                                                                                           |
|     └─IndexHashJoin_92              | 17.60    | root      |                                            | left outer join, inner:IndexReader_89, outer key:bookshop.books.id, inner key:bookshop.orders.book_id, equal cond:eq(bookshop.books.id, bookshop.orders.book_id)                                      |
|       ├─Selection_99(Build)         | 1.18     | root      |                                            | not(isnull(bookshop.books.id))                                                                                                                                                                        |
|       │ └─CTEFullScan_100           | 1.48     | root      | CTE:b                                      | data:CTE_0                                                                                                                                                                                            |
|       └─IndexReader_89(Probe)       | 14.88    | root      |                                            | index:IndexRangeScan_88                                                                                                                                                                               |
|         └─IndexRangeScan_88         | 14.88    | cop[tikv] | table:o, index:orders_book_id_idx(book_id) | range: decided by [eq(bookshop.orders.book_id, bookshop.books.id)], keep order:false                                                                                                                  |
| CTE_1                               | 1.18     | root      |                                            | Non-Recursive CTE                                                                                                                                                                                     |
| └─Projection_65(Seed Part)          | 1.18     | root      |                                            | bookshop.books.id, Column#21                                                                                                                                                                          |
|   └─HashAgg_66                      | 1.18     | root      |                                            | group by:Column#50, funcs:avg(Column#48)->Column#21, funcs:firstrow(Column#49)->bookshop.books.id                                                                                                     |
|     └─Projection_82                 | 17.60    | root      |                                            | cast(bookshop.ratings.score, decimal(8,4) BINARY)->Column#48, bookshop.books.id, bookshop.books.id                                                                                                    |
|       └─IndexJoin_71                | 17.60    | root      |                                            | left outer join, inner:TableReader_68, outer key:bookshop.books.id, inner key:bookshop.ratings.book_id, equal cond:eq(bookshop.books.id, bookshop.ratings.book_id)                                    |
|         ├─Selection_78(Build)       | 1.18     | root      |                                            | not(isnull(bookshop.books.id))                                                                                                                                                                        |
|         │ └─CTEFullScan_79          | 1.48     | root      | CTE:b                                      | data:CTE_0                                                                                                                                                                                            |
|         └─TableReader_68(Probe)     | 1.00     | root      |                                            | data:TableRangeScan_67                                                                                                                                                                                |
|           └─TableRangeScan_67       | 1.00     | cop[tikv] | table:r                                    | range: decided by [bookshop.books.id], keep order:false                                                                                                                                               |
| CTE_0                               | 1.48     | root      |                                            | Non-Recursive CTE                                                                                                                                                                                     |
| └─Projection_28(Seed Part)          | 1.48     | root      |                                            | bookshop.books.id, bookshop.books.title, bookshop.books.type, bookshop.books.published_at, bookshop.books.stock, bookshop.books.price, bookshop.book_authors.book_id, bookshop.book_authors.author_id |
|   └─IndexJoin_34                    | 1.48     | root      |                                            | inner join, inner:TableReader_31, outer key:bookshop.book_authors.book_id, inner key:bookshop.books.id, equal cond:eq(bookshop.book_authors.book_id, bookshop.books.id)                               |
|     ├─TableReader_59(Build)         | 1.48     | root      |                                            | data:Selection_58                                                                                                                                                                                     |
|     │ └─Selection_58                | 1.48     | cop[tikv] |                                            | eq(bookshop.book_authors.author_id, 2299112019)                                                                                                                                                       |
|     │   └─TableFullScan_57          | 20000.00 | cop[tikv] | table:ba                                   | keep order:false                                                                                                                                                                                      |
|     └─TableReader_31(Probe)         | 1.00     | root      |                                            | data:TableRangeScan_30                                                                                                                                                                                |
|       └─TableRangeScan_30           | 1.00     | cop[tikv] | table:b                                    | range: decided by [bookshop.book_authors.book_id], keep order:false                                                                                                                                   |
+-------------------------------------+----------+-----------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Expected behavior

It's expected all CTE_x identifiers are replaced with CTE_<actual name in upper case> in both id and operator info. In above example:

• CTE_0 -> CTE_ BOOKS_AUTHORED_BY_RM
• CTE_1 -> CTE_BOOKS_WITH_AVERAGE_RATINGS
• CTE_2 -> CTE_BOOKS_WITH_ORDERS

[songrijie]

It's initially from https://asktug.com/t/topic/694310

[Ranxy]

/assign

[xiongjiwei]

it can be duplicated if it is a nested CTE. i.e.

mysql> desc with cte1 as (with cte1 as (select 1) select * from cte1) select * from cte1;
+---------------------------------+---------+------+---------------+-------------------+
| id                              | estRows | task | access object | operator info     |
+---------------------------------+---------+------+---------------+-------------------+
| CTEFullScan_12                  | 1.00    | root | CTE:cte1      | data:CTE_0        |
| CTE_0                           | 1.00    | root |               | Non-Recursive CTE |
| └─CTEFullScan_10(Seed Part)     | 1.00    | root | CTE:cte1      | data:CTE_1        |
| CTE_1                           | 1.00    | root |               | Non-Recursive CTE |
| └─Projection_7(Seed Part)       | 1.00    | root |               | 1->Column#1       |
|   └─TableDual_8                 | 1.00    | root |               | rows:1            |
+---------------------------------+---------+------+---------------+-------------------+
6 rows in set (0.00 sec)

[Ranxy]

So it is should be changed to CTE_<actual name>_IDForStorage?

[Ranxy]

And this change seems to have broken a lot of tests, I don't know if I should continue. :(

[xiongjiwei]

I prefer to add the real name to access object, for now only the alias is shown. it can be CTE: books_with_orders as bo

[xiongjiwei]

And this change seems to have broken a lot of tests, I don't know if I should continue. :(

check_dev and check_dev_2 can be update:

• build tidb
• go to cmd/explaintest
• run ./run-tests.sh -s $TIDB_BINARY -r all

[xiongjiwei]

mysql-test is internal, we will update it for you once your pr is ready to merge

[Ranxy]

Okay, I'll try to update

[songrijie]

it's better we have both alias and actual tablename in access object