pingcap / pingcap/tidb

Inl_join hints doesn't work when join operator including CTE after merge

Open
#35,318 1 comment 0 reactions 1 assignee Claimed by @dayicklp View on GitHub
severity/moderate 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)
First, add merge hint for master #34574
```
mysql> desc t1;
+-------+---------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | MUL | NULL | |
| c | int(11) | YES | MUL | NULL | |
+-------+---------+------+------+---------+-------+
3 rows in set (0.00 sec)

mysql> desc t2;
+-------+---------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | MUL | NULL | |
| c | int(11) | YES | MUL | NULL | |
+-------+---------+------+------+---------+-------+
3 rows in set (0.00 sec)

mysql> explain WITH cte1 AS (SELECT /*+ merge() */ * FROM t1) SELECT /*+ inl_join(cte1) */ * FROM cte1 join t2 on cte1.b = t2.b;
+------------------------------+----------+-----------+---------------+----------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+----------------------------------------------+
| HashJoin_41 | 12487.50 | root | | inner join, equal:[eq(test.t1.b, test.t2.b)] |
| ├─TableReader_59(Build) | 9990.00 | root | | data:Selection_58 |
| │ └─Selection_58 | 9990.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| │ └─TableFullScan_57 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_53(Probe) | 9990.00 | root | | data:Selection_52 |
| └─Selection_52 | 9990.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| └─TableFullScan_51 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+----------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (cte1) in optimizer hint /*+ INL_JOIN(cte1) */ or /*+ TIDB_INLJ(cte1) */. Maybe you can use the table alias name |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

```

### 2. What did you expect to see? (Required)
According to the cost, plan will choose the hint, just like index join hint for t2
```
mysql> explain WITH cte1 AS (SELECT /*+ merge() */ * FROM t1) SELECT /*+ inl_join(t2) */ * FROM cte1 join t2 on cte1.b = t2.b;
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| IndexJoin_16 | 12487.50 | root | | inner join, inner:IndexLookUp_15, outer key:test.t1.b, inner key:test.t2.b, equal cond:eq(test.t1.b, test.t2.b) |
| ├─TableReader_28(Build) | 9990.00 | root | | data:Selection_27 |
| │ └─Selection_27 | 9990.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| │ └─TableFullScan_26 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─IndexLookUp_15(Probe) | 1.25 | root | | |
| ├─Selection_14(Build) | 1.25 | cop[tikv] | | not(isnull(test.t2.b)) |
| │ └─IndexRangeScan_12 | 1.25 | cop[tikv] | table:t2, index:idx_b(b) | range: decided by [eq(test.t2.b, test.t1.b)], keep order:false, stats:pseudo |
| └─TableRowIDScan_13(Probe) | 1.25 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
```
### 3. What did you see instead (Required)
No match table names.
### 4. What is your TiDB version? (Required)
master, add #34574

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.