data after tiflash pass through can not be shuffle again to facilitate upper hash join
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
mysql> explain select * from (select row_number() over( order by c2) as x from t) t2 join t t3 on t2.x = t3.c1;
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
| TableReader_62 | 2.00 | root | | MppVersion: 3, data:ExchangeSender_61 |
| └─ExchangeSender_61 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_60 | 2.00 | mpp[tiflash] | | Column#5, test.t.c1, test.t.c2 |
| └─Projection_57 | 2.00 | mpp[tiflash] | | test.t.c1, test.t.c2, Column#5 |
| └─HashJoin_56 | 2.00 | mpp[tiflash] | | inner join, equal:[eq(test.t.c1, Column#5)] |
| ├─ExchangeReceiver_35(Build) | 2.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_34 | 2.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Selection_33 | 2.00 | mpp[tiflash] | | not(isnull(test.t.c1)) |
| │ └─TableFullScan_32 | 2.00 | mpp[tiflash] | table:t3 | keep order:false, stats:pseudo |
| └─Window_36(Probe) | 2.00 | mpp[tiflash] | | row_number()->Column#5 over(order by test.t.c2 rows between current row and current row) |
| └─Sort_40 | 2.00 | mpp[tiflash] | | test.t.c2 |
| └─ExchangeReceiver_39 | 2.00 | mpp[tiflash] | | |
| └─ExchangeSender_38 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough, Compression: FAST |
| └─TableFullScan_37 | 2.00 | mpp[tiflash] | table:t | keep order:false, stats:pseudo |
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
14 rows in set (0.002 sec)
mysql> explain select /*+ SHUFFLE_JOIN(t2, t3) */ * from (select row_number() over( order by c2) as x from t) t2 join t t3 on t2.x = t3.c1;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query
```
since window operator has already been pushed down to tiflash, while its passThrough attribute can not be shuffle again to facilitate upper layer shuffle join again. It's same for agg case below:
```
mysql> explain select * from (select count(*) as y from (select row_number() over( order by c2) as x from t) t0) t2 join t t3 on t2.y = t3.c1;
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
| Projection_17 | 1.25 | root | | Column#7, test.t.c1, test.t.c2 |
| └─HashJoin_32 | 1.25 | root | | inner join, equal:[eq(test.t.c1, Column#7)] |
| ├─StreamAgg_66(Build) | 1.00 | root | | funcs:count(1)->Column#7 |
| │ └─TableReader_81 | 2.00 | root | | MppVersion: 3, data:ExchangeSender_80 |
| │ └─ExchangeSender_80 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─Window_78 | 2.00 | mpp[tiflash] | | row_number()->Column#5 over(order by test.t.c2 rows between current row and current row) |
| │ └─Sort_43 | 2.00 | mpp[tiflash] | | test.t.c2 |
| │ └─ExchangeReceiver_42 | 2.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_41 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough, Compression: FAST |
| │ └─TableFullScan_40 | 2.00 | mpp[tiflash] | table:t | keep order:false, stats:pseudo |
| └─TableReader_50(Probe) | 2.00 | root | | MppVersion: 3, data:ExchangeSender_49 |
| └─ExchangeSender_49 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Selection_48 | 2.00 | mpp[tiflash] | | not(isnull(test.t.c1)) |
| └─TableFullScan_47 | 2.00 | mpp[tiflash] | table:t3 | keep order:false, stats:pseudo |
+------------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------+
14 rows in set (0.001 sec)
mysql> explain select /*+ SHUFFLE_JOIN(t2, t3) */ * from (select count(*) as y from (select row_number() over( order by c2) as x from t) t0) t2 join t t3 on t2.y = t3.c1;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query
```
Contributor guide
Assessment
This issue has not been assessed yet.