planner: leading hint cannot take effect in UNION ALL statements #50067
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
explain
select * from t1, t2, t3
union all
select /*+ leading(t3, t2) */ * from t1, t2, t3
union all
select * from t1, t2, t3;
2. What did you expect to see? (Required)
The leading hint should take effect.
3. What did you see instead (Required)
mysql> explain
-> select * from t1, t2, t3
-> union all
-> select /*+ leading(t3, t2) */ * from t1, t2, t3
-> union all
-> select * from t1, t2, t3;
+-----------------------------------+------------------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------------+------------------+-----------+---------------+--------------------------------+
| Union_29 | 3000000000000.00 | root | | |
| ├─HashJoin_31 | 1000000000000.00 | root | | CARTESIAN inner join |
| │ ├─TableReader_40(Build) | 10000.00 | root | | data:TableFullScan_39 |
| │ │ └─TableFullScan_39 | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
| │ └─HashJoin_33(Probe) | 100000000.00 | root | | CARTESIAN inner join |
| │ ├─TableReader_38(Build) | 10000.00 | root | | data:TableFullScan_37 |
| │ │ └─TableFullScan_37 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| │ └─TableReader_36(Probe) | 10000.00 | root | | data:TableFullScan_35 |
| │ └─TableFullScan_35 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| ├─HashJoin_42 | 1000000000000.00 | root | | CARTESIAN inner join |
| │ ├─TableReader_51(Build) | 10000.00 | root | | data:TableFullScan_50 |
| │ │ └─TableFullScan_50 | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
| │ └─HashJoin_44(Probe) | 100000000.00 | root | | CARTESIAN inner join |
| │ ├─TableReader_49(Build) | 10000.00 | root | | data:TableFullScan_48 |
| │ │ └─TableFullScan_48 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| │ └─TableReader_47(Probe) | 10000.00 | root | | data:TableFullScan_46 |
| │ └─TableFullScan_46 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─HashJoin_53 | 1000000000000.00 | root | | CARTESIAN inner join |
| ├─TableReader_62(Build) | 10000.00 | root | | data:TableFullScan_61 |
| │ └─TableFullScan_61 | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
| └─HashJoin_55(Probe) | 100000000.00 | root | | CARTESIAN inner join |
| ├─TableReader_60(Build) | 10000.00 | root | | data:TableFullScan_59 |
| │ └─TableFullScan_59 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_58(Probe) | 10000.00 | root | | data:TableFullScan_57 |
| └─TableFullScan_57 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-----------------------------------+------------------+-----------+---------------+--------------------------------+
25 rows in set (0.00 sec)
4. What is your TiDB version? (Required)
Master