planner: leading hint cannot take effect in UNION ALL statements

[qw4990]

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

[qw4990]

@hawkingrei Could you take a look at this issue?

[hawkingrei]

@hawkingrei Could you take a look at this issue?

OK, I will take a look.

[seiya-annie]

/found customer