Open
Description
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 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+