`inl_join` hint shows warning when `tidb_opt_advanced_join_hint=OFF` but silently fails when `ON`
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
**Bug Report**
### What did you do?
Executed a query with `/*+ inl_join(t1) */` hint on TiDB v8.5.5 with different settings of `tidb_opt_advanced_join_hint`:
```sql
-- Test 1: tidb_opt_advanced_join_hint=OFF
set tidb_opt_advanced_join_hint=OFF;
EXPLAIN SELECT
count(*)
FROM
(
SELECT /*+ inl_join(t1) */
t2.actualId,
t2.planNo,
t2.topId,
t2.insurantNo
FROM
(
SELECT *
FROM table_insurant
WHERE actualid IN (
SELECT actualid
FROM table_role_connection
WHERE 1 = 1
AND parentId = '3561703379345'
AND specid = '14535'
)
) t1,
(
SELECT *
FROM table_house
WHERE actualid IN (
SELECT actualid
FROM table_role_connection
WHERE 1 = 1
AND parentId = '3561703379345'
AND specid = '14550'
)
) t2
WHERE t1.serialNo = t2.insurantNo
AND t2.insurantNo = '2'
) s;
show warnings;
-- Test 2: tidb_opt_advanced_join_hint=ON
set tidb_opt_advanced_join_hint=ON;
-- Execute the same EXPLAIN query
```
### What did you expect to see?
The behavior should be consistent:
1. If the `inl_join(t1)` hint cannot be applied, it should **always show a warning** regardless of the `tidb_opt_advanced_join_hint` setting
2. If the hint can be applied, it should work correctly without warnings
### What did you see instead?
**When `tidb_opt_advanced_join_hint=OFF`:**
- Warning is shown correctly:
```
+---------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ TIDB_INLJ(t1) */ is inapplicable |
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ TIDB_INLJ(t1) */ is inapplicable |
+---------+------+--------------------------------------------------------------------------+
```
**When `tidb_opt_advanced_join_hint=ON`:**
- **No warning is shown**, but the hint still doesn't take effect on table `t1`
- The execution plan shows IndexJoin operations, but they are not applied to the hinted table `t1` as specified
### Why is this problematic?
This inconsistency is problematic because:
1. Users may think the hint is working correctly when `tidb_opt_advanced_join_hint=ON` (since no warning appears)
2. The actual execution plan does not respect the hint in either case
3. Silent failures make debugging difficult
### Expected behavior
The warning should be displayed in both cases when the hint cannot be applied, ensuring consistent and predictable behavior.
### TiDB version
v8.5.5
Contributor guide
Assessment
This issue has not been assessed yet.