pingcap / pingcap/tidb

`inl_join` hint shows warning when `tidb_opt_advanced_join_hint=OFF` but silently fails when `ON`

Open
#65,838 2 comments 0 reactions 0 assignees View on GitHub
affects-8.5 contribution report/customer severity/major sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.