pingcap / pingcap/tidb

v7.1.5 Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ TIDB_INLJ(t1) */ is inapplicable

Open
#57,913 0 comments 0 reactions 1 assignee Claimed by @qw4990 View on GitHub
sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
create table t1(a int primary key, b int);

create table t2(a int, b int, index ia(a));

create table t3(a int, b int, index ia(a));

explain select /*+ inl_join(t1) */ * from t1 where exists ( select /*+ semi_join_rewrite() */ * from t2 where t1.a = t2.a) and exists ( select /*+ semi_join_rewrite() */ * from t3 where t1.a=t3.a);
```

result:

```sql
(root@127.0.0.1) [test]>explain select /*+ inl_join(t1) */ * from t1 where exists ( select /*+ semi_join_rewrite() */ * from t2 where t1.a = t2.a) and exists ( select /*+ semi_join_rewrite() */ * from t3 where t1.a=t3.a);
+----------------------------------+---------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+
| HashJoin_20 | 9980.01 | root | | inner join, equal:[eq(test.t1.a, test.t3.a)] |
| ├─StreamAgg_76(Build) | 7992.00 | root | | group by:test.t3.a, funcs:firstrow(test.t3.a)->test.t3.a |
| │ └─IndexReader_77 | 7992.00 | root | | index:StreamAgg_69 |
| │ └─StreamAgg_69 | 7992.00 | cop[tikv] | | group by:test.t3.a, |
| │ └─IndexFullScan_51 | 9990.00 | cop[tikv] | table:t3, index:ia(a) | keep order:true, stats:pseudo |
| └─IndexJoin_61(Probe) | 9990.00 | root | | inner join, inner:TableReader_58, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a) |
| ├─StreamAgg_44(Build) | 7992.00 | root | | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a |
| │ └─IndexReader_45 | 7992.00 | root | | index:StreamAgg_36 |
| │ └─StreamAgg_36 | 7992.00 | cop[tikv] | | group by:test.t2.a, |
| │ └─IndexFullScan_43 | 9990.00 | cop[tikv] | table:t2, index:ia(a) | keep order:true, stats:pseudo |
| └─TableReader_58(Probe) | 7992.00 | root | | data:TableRangeScan_57 |
| └─TableRangeScan_57 | 7992.00 | cop[tikv] | table:t1 | range: decided by [test.t2.a], keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)

(root@127.0.0.1) [test]>show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ TIDB_INLJ(t1) */ is inapplicable |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 2. What did you expect to see? (Required)

### 3. What did you see instead (Required)

### 4. What is your TiDB version? (Required)

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.