/*+inl_merge_join(t2)*/ hint not work

[jingshanglu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` ( `a` bigint(20) NOT NULL, `b` tinyint(1) DEFAULT NULL, `c` datetime DEFAULT NULL, `d` int(10) unsigned DEFAULT NULL, `e` varchar(20) DEFAULT NULL, `f` double DEFAULT NULL, `g` decimal(30,5) DEFAULT NULL, `h` float DEFAULT NULL, `i` date DEFAULT NULL, `j` timestamp NULL DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), KEY `c` (`c`,`d`,`e`), KEY `f` (`f`), KEY `g` (`g`,`h`), KEY `g_2` (`g`), UNIQUE KEY `g_3` (`g`), KEY `i` (`i`) );
insert into t select 0, -128, "2020-01-02 12:00:0", 2020, "xxxxx", 2020, 4397.260860, 1264.043753, "2016-12-15 03:43:33.727051", "2004-05-14 22:03:00.695988";
insert into t select 1, -127, "2020-01-02 12:00:1", 2021, "xxxxx", 2021, 4091.369690, 3085.079355, "1986-05-06 12:55:20.520991", "1985-08-27 15:00:27.162993";
insert into t select 2, -126, "2020-01-02 12:00:2", 2022, "xxxxx", 2022, 9812.429830, -287.537688, "1921-06-17 09:13:02.536615", "1999-05-14 11:42:42.512601";
analyze table t;
explain select /*+inl_hash_join(t2)*/ t1.a, t2.a from t t1 left join t t2 on t1.c=t2.c;
select /*+inl_hash_join(t2)*/ t1.a, t2.a from t t1 left join t t2 on t1.c=t2.c;
explain select /*+inl_merge_join(t2)*/ t1.a, t2.a from t t1 left join t t2 on t1.c=t2.c;

2. What did you expect to see? (Required)

the root plan not should be IndexMergeJoin

3. What did you see instead (Required)

mysql> explain select /*+inl_hash_join(t2)*/ t1.a, t2.a from t t1 left join t t2 on t1.c=t2.c;
+---------------------------------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------+
| id                              | estRows | task      | access object              | operator info                                                                                                    |
+---------------------------------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------+
| IndexHashJoin_13                | 3.00    | root      |                            | left outer join, inner:IndexLookUp_10, outer key:test.t.c, inner key:test.t.c, equal cond:eq(test.t.c, test.t.c) |
| ├─TableReader_15(Build)         | 3.00    | root      |                            | data:TableFullScan_14                                                                                            |
| │ └─TableFullScan_14            | 3.00    | cop[tikv] | table:t1                   | keep order:false                                                                                                 |
| └─IndexLookUp_10(Probe)         | 1.00    | root      |                            |                                                                                                                  |
|   ├─Selection_9(Build)          | 1.00    | cop[tikv] |                            | not(isnull(test.t.c))                                                                                            |
|   │ └─IndexRangeScan_7          | 1.00    | cop[tikv] | table:t2, index:c(c, d, e) | range: decided by [eq(test.t.c, test.t.c)], keep order:false                                                     |
|   └─TableRowIDScan_8(Probe)     | 1.00    | cop[tikv] | table:t2                   | keep order:false                                                                                                 |
+---------------------------------+---------+-----------+----------------------------+------------------------------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.14
Edition: Community
Git Commit Hash: 4f919b07f8f013e48521894c70cb69004f43c622
Git Branch: heads/refs/tags/v4.0.14
UTC Build Time: 2021-07-26 10:57:28
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[jingshanglu]

version 5.0.4 has fixed it,maybe these fix should cherry to v4.0

[time-and-fate]

This is caused by #20599 on release-4.0.

[time-and-fate]

Seems release-4.0 has index merge join disabled.