pingcap / pingcap/tidb

Cascades Planner: Wrong query plan produced with `CTEFullScan`

Open
#62,700 1 comment 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
severity/moderate sig/planner type/bug
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)).

Image

### 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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.