planner: indexMergeJoin hint causes two identical warnings

[fzhedu]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

tables are from #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

[asiafrank]

tidb/planner/core/find_best_task.go

Lines 310 to 338 in ef57bdb

	plansFitsProp, hintWorksWithProp = p.self.exhaustPhysicalPlans(newProp)
	if !hintWorksWithProp && !newProp.IsEmpty() {
	// If there is a hint in the plan and the hint cannot satisfy the property,
	// we enforce this property and try to generate the PhysicalPlan again to
	// make sure the hint can work.
	newProp.Enforced = true
	}
	if newProp.Enforced {
	// Then, we use the empty property to get physicalPlans and
	// try to get the task with an enforced sort.
	newProp.SortItems = []property.SortItem{}
	newProp.ExpectedCnt = math.MaxFloat64
	var hintCanWork bool
	plansNeedEnforce, hintCanWork = p.self.exhaustPhysicalPlans(newProp)
	if hintCanWork && !hintWorksWithProp {
	// If the hint can work with the empty property, but cannot work with
	// the required property, we give up `plansFitProp` to make sure the hint
	// can work.
	plansFitsProp = nil
	}
	if !hintCanWork && !hintWorksWithProp && !prop.Enforced {
	// If the original property is not enforced and hint cannot
	// work anyway, we give up `plansNeedEnforce` for efficiency,
	plansNeedEnforce = nil
	}
	newProp.SortItems = prop.SortItems
	newProp.ExpectedCnt = prop.ExpectedCnt
	}

the second warning is appended when newProp.Enforced = true. it will try exhaustPhysicalPlans again.

i dont known how to avoid this warning.

i need some advice. @lzmhhh123

[lzmhhh123]

I think in this logic, the first time to exhaustPhysicalPlans should not append warning.

[asiafrank]

you mean, first time to exhaustPhysicalPlans the prop should not be empty?