Cascades Planner: Wrong query plan produced with `CTEFullScan`
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
This issue was discovered while refactoring [TestMPPSharedCTEScan](https://github.com/pingcap/tidb/blob/v9.0.0-beta.2.pre/pkg/planner/core/casetest/enforcempp/enforce_mpp_test.go#L561).
After the logicalOptimize stage, TiDB will initialize the memo with a logical plan, converting the logical plan tree into a group tree. However, the child of the CTE will be set ([DeriveStats](https://github1s.com/pingcap/tidb/blob/v9.0.0-beta.2.pre/pkg/planner/core/operator/logicalop/logical_cte.go#L202)) after the (*Memo).Init.
This causes TiDB to be unable to properly set the input of GE. Ultimately, TiDB fails to generate tasks for the children of the CTE ([getGEAndLogicalCTE](https://github1s.com/pingcap/tidb/blob/master/pkg/planner/core/find_best_task.go#L3499-L3504)).
### 1. Minimal reproduce step (Required)
### 2. What did you expect to see? (Required)
```
mysql> explain format = 'brief' with c1 as (select * from t) select * from c1, c1 c2 where c1.a=c2.b;
+-------------------------------------+----------+--------------+---------------+--------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------------+----------+--------------+---------------+--------------------------------------------------+
| TableReader | 9999.99 | root | | MppVersion: 3, data:ExchangeSender |
| └─ExchangeSender | 9999.99 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Sequence | 9999.99 | mpp[tiflash] | | Sequence Node |
| ├─CTE_0 | 9999.99 | mpp[tiflash] | | Non-Recursive CTE Storage |
| │ └─Selection | 9999.99 | mpp[tiflash] | | or(not(isnull(test.t.a)), not(isnull(test.t.b))) |
| │ └─TableFullScan | 10000.00 | mpp[tiflash] | table:t | keep order:false, stats:pseudo |
| └─HashJoin | 9999.99 | mpp[tiflash] | | inner join, equal:[eq(test.t.a, test.t.b)] |
| ├─ExchangeReceiver(Build) | 7999.99 | mpp[tiflash] | | |
| │ └─ExchangeSender | 7999.99 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Selection | 7999.99 | mpp[tiflash] | | not(isnull(test.t.a)) |
| │ └─CTEFullScan | 9999.99 | mpp[tiflash] | CTE:c1 | data:CTE_0 |
| └─Selection(Probe) | 7999.99 | mpp[tiflash] | | not(isnull(test.t.b)) |
| └─CTEFullScan | 9999.99 | mpp[tiflash] | CTE:c1 AS c2 | data:CTE_0 |
+-------------------------------------+----------+--------------+---------------+--------------------------------------------------+
13 rows in set (2 min 42.87 sec)
```
### 3. What did you see instead (Required)
```
mysql> set session tidb_enable_cascades_planner=on;
Query OK, 0 rows affected (1.87 sec)
mysql> explain format = 'brief' with c1 as (select * from t) select * from c1, c1 c2 where c1.a=c2.b;
+-------------------------------------+---------+--------------+---------------+--------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------------+---------+--------------+---------------+--------------------------------------------+
| TableReader | 9999.99 | root | | MppVersion: 3, data:ExchangeSender |
| └─ExchangeSender | 9999.99 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Sequence | 9999.99 | mpp[tiflash] | | Sequence Node |
| ├─CTEFullScan | 9999.99 | mpp[tiflash] | CTE:c1 | data:CTE_0 |
| └─HashJoin | 9999.99 | mpp[tiflash] | | inner join, equal:[eq(test.t.a, test.t.b)] |
| ├─ExchangeReceiver(Build) | 7999.99 | mpp[tiflash] | | |
| │ └─ExchangeSender | 7999.99 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Selection | 7999.99 | mpp[tiflash] | | not(isnull(test.t.a)) |
| │ └─CTEFullScan | 9999.99 | mpp[tiflash] | CTE:c1 | data:CTE_0 |
| └─Selection(Probe) | 7999.99 | mpp[tiflash] | | not(isnull(test.t.b)) |
| └─CTEFullScan | 9999.99 | mpp[tiflash] | CTE:c1 AS c2 | data:CTE_0 |
+-------------------------------------+---------+--------------+---------------+--------------------------------------------+
11 rows in set (6 min 9.88 sec)
```
### 4. What is your TiDB version? (Required)
Contributor guide
Assessment
This issue has not been assessed yet.