hash_join_[build|probe] hint sometimes needs shuffle_join hint to be effective when it should be unnecessary for mpp plan

[time-and-fate]

Enhancement

create table t(a int, b int, c int);
create table t1(a int, b int, c int);
alter table t set tiflash replica 1;
alter table t1 set tiflash replica 1;
-- wait a moment

explain select * from t left join t1 on t.a = t1.a where t.b > 1; -- note the left join, which limits the choice of broadcast join
explain select /*+ hash_join_probe(t1) */ * from t left join t1 on t.a = t1.a where t.b > 1;
explain select /*+ shuffle_join(t1), hash_join_probe(t1) */ * from t left join t1 on t.a = t1.a where t.b > 1;

Ideally, the last two SQLs should have the same plan. hash_join_probe(t1) should already be enough since there's no ambiguity.
But actually, we need shuffle_join(t1) to enforce t1 to be the probe side.

> explain select * from t left join t1 on t.a = t1.a where t.b > 1;
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
| id                                   | estRows  | task         | access object | operator info                                            |
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
| TableReader_36                       | 4166.67  | root         |               | MppVersion: 2, data:ExchangeSender_35                    |
| └─ExchangeSender_35                  | 4166.67  | mpp[tiflash] |               | ExchangeType: PassThrough                                |
|   └─HashJoin_34                      | 4166.67  | mpp[tiflash] |               | left outer join, equal:[eq(test.t.a, test.t1.a)]         |
|     ├─ExchangeReceiver_17(Build)     | 9990.00  | mpp[tiflash] |               |                                                          |
|     │ └─ExchangeSender_16            | 9990.00  | mpp[tiflash] |               | ExchangeType: Broadcast, Compression: FAST               |
|     │   └─Selection_15               | 9990.00  | mpp[tiflash] |               | not(isnull(test.t1.a))                                   |
|     │     └─TableFullScan_14         | 10000.00 | mpp[tiflash] | table:t1      | pushed down filter:empty, keep order:false, stats:pseudo |
|     └─Selection_13(Probe)            | 3333.33  | mpp[tiflash] |               | gt(test.t.b, 1)                                          |
|       └─TableFullScan_12             | 10000.00 | mpp[tiflash] | table:t       | pushed down filter:empty, keep order:false, stats:pseudo |
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
9 rows in set (0.002 sec)

> explain select /*+ hash_join_probe(t1) */ * from t left join t1 on t.a = t1.a where t.b > 1;
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
| id                                   | estRows  | task         | access object | operator info                                            |
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
| TableReader_33                       | 4166.67  | root         |               | MppVersion: 2, data:ExchangeSender_32                    |
| └─ExchangeSender_32                  | 4166.67  | mpp[tiflash] |               | ExchangeType: PassThrough                                |
|   └─HashJoin_25                      | 4166.67  | mpp[tiflash] |               | left outer join, equal:[eq(test.t.a, test.t1.a)]         |
|     ├─ExchangeReceiver_31(Build)     | 9990.00  | mpp[tiflash] |               |                                                          |
|     │ └─ExchangeSender_30            | 9990.00  | mpp[tiflash] |               | ExchangeType: Broadcast, Compression: FAST               |
|     │   └─Selection_29               | 9990.00  | mpp[tiflash] |               | not(isnull(test.t1.a))                                   |
|     │     └─TableFullScan_28         | 10000.00 | mpp[tiflash] | table:t1      | pushed down filter:empty, keep order:false, stats:pseudo |
|     └─Selection_27(Probe)            | 3333.33  | mpp[tiflash] |               | gt(test.t.b, 1)                                          |
|       └─TableFullScan_26             | 10000.00 | mpp[tiflash] | table:t       | pushed down filter:empty, keep order:false, stats:pseudo |
+--------------------------------------+----------+--------------+---------------+----------------------------------------------------------+
9 rows in set, 2 warnings (0.001 sec)

> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                 |
+---------+------+---------------------------------------------------------------------------------------------------------+
| Warning | 1815 | Some HASH_JOIN_BUILD and HASH_JOIN_PROBE hints cannot be utilized for MPP joins, please check the hints |
| Warning | 1815 | Some HASH_JOIN_BUILD and HASH_JOIN_PROBE hints cannot be utilized for MPP joins, please check the hints |
+---------+------+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

> explain select /*+ shuffle_join(t1), hash_join_probe(t1) */ * from t left join t1 on t.a = t1.a where t.b > 1;
+--------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task         | access object | operator info                                                                                                 |
+--------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------+
| TableReader_22                       | 4166.67  | root         |               | MppVersion: 2, data:ExchangeSender_21                                                                         |
| └─ExchangeSender_21                  | 4166.67  | mpp[tiflash] |               | ExchangeType: PassThrough                                                                                     |
|   └─HashJoin_20                      | 4166.67  | mpp[tiflash] |               | left outer join, equal:[eq(test.t.a, test.t1.a)], stream_count: 4                                             |
|     ├─ExchangeReceiver_13(Build)     | 3333.33  | mpp[tiflash] |               | stream_count: 4                                                                                               |
|     │ └─ExchangeSender_12            | 3333.33  | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t.a, collate: binary], stream_count: 4 |
|     │   └─Selection_11               | 3333.33  | mpp[tiflash] |               | gt(test.t.b, 1)                                                                                               |
|     │     └─TableFullScan_10         | 10000.00 | mpp[tiflash] | table:t       | pushed down filter:empty, keep order:false, stats:pseudo                                                      |
|     └─ExchangeReceiver_17(Probe)     | 9990.00  | mpp[tiflash] |               |                                                                                                               |
|       └─ExchangeSender_16            | 9990.00  | mpp[tiflash] |               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t1.a, collate: binary]                 |
|         └─Selection_15               | 9990.00  | mpp[tiflash] |               | not(isnull(test.t1.a))                                                                                        |
|           └─TableFullScan_14         | 10000.00 | mpp[tiflash] | table:t1      | pushed down filter:empty, keep order:false, stats:pseudo                                                      |
+--------------------------------------+----------+--------------+---------------+---------------------------------------------------------------------------------------------------------------+
11 rows in set (0.018 sec)

[ti-chi-bot]

[FORMAT CHECKER NOTIFICATION]

🫱 ${\color{gold}\Huge{\textsf{Please use english to create or update issue.}}}$