planner: indexMergeJoin hint causes two identical warnings

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

tables are from https://github.com/pingcap/tidb/issues/15299

 explain SELECT /*+ INL_MERGE_JOIN(table2)*/   table2 . `pk` AS field1 FROM  H AS table1  RIGHT  JOIN Q AS table2 ON  table1 . `col_varchar_10_utf8_key` =  table2 . `col_varchar_1024_latin1_key`  WHERE table1 . `pk` = table2 . `col_int_key`  GROUP BY field1  ORDER BY field1   ;

2. What did you expect to see?

one warnings

3. What did you see instead?

mysql> explain SELECT /*+ INL_MERGE_JOIN(table2)*/   table2 . `pk` AS field1 FROM  H AS table1  RIGHT  JOIN Q AS table2 ON  table1 . `col_varchar_10_utf8_key` =  table2 . `col_varchar_1024_latin1_key`  WHERE table1 . `pk` = table2 . `col_int_key`  GROUP BY field1  ORDER BY field1   ;
+--------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows | task      | operator info                                                                                                                                                           |
+--------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_8                               | 6.24    | root      | hc.q.pk:asc                                                                                                                                                             |
| └─HashAgg_13                         | 6.24    | root      | group by:hc.q.pk, funcs:firstrow(hc.q.pk)->hc.q.pk                                                                                                                      |
|   └─IndexJoin_21                     | 6.24    | root      | inner join, inner:IndexLookUp_20, outer key:hc.h.col_varchar_10_utf8_key, inner key:hc.q.col_varchar_1024_latin1_key, other cond:eq(hc.h.pk, hc.q.col_int_key)          |
|     ├─IndexReader_50(Build)          | 5.00    | root      | index:IndexFullScan_49                                                                                                                                                  |
|     │ └─IndexFullScan_49             | 5.00    | cop[tikv] | table:table1, index:col_varchar_10_utf8_key, keep order:false, stats:pseudo                                                                                             |
|     └─IndexLookUp_20(Probe)          | 1.25    | root      |                                                                                                                                                                         |
|       ├─IndexRangeScan_17(Build)     | 1.25    | cop[tikv] | table:table2, index:col_varchar_1024_latin1_key, range: decided by [eq(hc.q.col_varchar_1024_latin1_key, hc.h.col_varchar_10_utf8_key)], keep order:false, stats:pseudo |
|       └─Selection_19(Probe)          | 1.25    | cop[tikv] | not(isnull(hc.q.col_int_key)), not(isnull(hc.q.col_varchar_1024_latin1_key))                                                                                            |
|         └─TableRowIDScan_18          | 1.25    | cop[tikv] | table:table2, keep order:false, stats:pseudo                                                                                                                            |
+--------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_MERGE_JOIN(table2) */ is inapplicable |
| Warning | 1815 | Optimizer Hint /*+ INL_MERGE_JOIN(table2) */ is inapplicable |
+---------+------+--------------------------------------------------------------+
2 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

| 5.7.25-TiDB-v4.0.0-alpha-1811-g783c2de9f-dirty |

SIG slack channel

#sig-planner

Score

• 300

Mentor

• @lzmhhh123