pingcap / pingcap/tidb

the inner side (hashjoin here) of apply should make the most of the correlated column to choose index path

Open
#32,620 0 comments 0 reactions 0 assignees View on GitHub
affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 may-affects-4.0 sig/planner type/enhancement
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)

```
CREATE TABLE `tc` (
`id` varchar(32) NOT NULL,
`flag` varchar(4) DEFAULT NULL,
`code` varchar(5) NOT NULL,
`status` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */);

CREATE TABLE `tcs` (
`id` varchar(32) NOT NULL,
`oid` varchar(32) NOT NULL,
`status` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
KEY `idx_oid` (`oid`)
);

explain SELECT o.code FROM tc o WHERE 0
OR EXISTS (
SELECT
1
FROM
tc t1
WHERE
t1.flag = 'C'
AND t1.status = 'Y'
AND t1.id = o.id
AND EXISTS (
SELECT
1
FROM
tcs r
WHERE
r.oid = o.id
AND r.status <> 'Y'
)
);
```

### 2. What did you expect to see? (Required)
```
+----+--------------------+-------+------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 130 | haha.o.id | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | r | NULL | ref | idx_oid | idx_oid | 130 | haha.o.id | 1 | 100.00 | Using where; FirstMatch(t1) |
+----+--------------------+-------+------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------------------+
3 rows in set, 3 warnings (0.01 sec)
```

### 3. What did you see instead (Required)
```
+------------------------------------------+----------+-----------+---------------------------------------+-----------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+----------+-----------+---------------------------------------+-----------------------------------------------------------------------------------------------------------+
| Projection_12 | 8000.00 | root | | haha.tcs_order.code |
| └─Selection_13 | 8000.00 | root | | or(0, Column#17) |
| └─Apply_15 | 10000.00 | root | | left outer semi join, equal:[eq(haha.tcs_order.id, haha.tcs_order.id)] |
| ├─TableReader_17(Build) | 10000.00 | root | | data:TableFullScan_16 |
| │ └─TableFullScan_16 | 10000.00 | cop[tikv] | table:o | keep order:false, stats:pseudo |
| └─HashJoin_18(Probe) | 0.01 | root | | CARTESIAN semi join |
| ├─IndexLookUp_28(Build) | 6.66 | root | | |
| │ ├─IndexRangeScan_25(Build) | 10.00 | cop[tikv] | table:r, index:idx_oid(oid) | range: decided by [eq(haha.tcs_refund_order.oid, haha.tcs_order.id)], keep order:false, stats:pseudo |
| │ └─Selection_27(Probe) | 6.66 | cop[tikv] | | ne(haha.tcs_refund_order.status, "Y") |
| │ └─TableRowIDScan_26 | 10.00 | cop[tikv] | table:r | keep order:false, stats:pseudo |
| └─TableReader_21(Probe) | 0.01 | root | | data:Selection_20 |
| └─Selection_20 | 0.01 | cop[tikv] | | eq(haha.tcs_order.status, "Y"), eq(haha.tcs_order.flag, "C") |
| └─TableFullScan_19 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------------------+----------+-----------+---------------------------------------+-----------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
```

As you see the t1 doesn't take use of `t1.id = o.id` and choose the index scan, while mysql does

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

master and all that

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.