pingcap / pingcap/tidb

Ignore primary key failed in the clustered table

Open
#42,023 1 comment 0 reactions 1 assignee Claimed by @time-and-fate View on GitHub
affects-7.0 affects-7.1 affects-7.5 may-affects-4.0 may-affects-5.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.1 may-affects-6.5 severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report
Ignore primary key failed in the clustered table
### 1. Minimal reproduce step (Required)
```
CREATE TABLE `test` (
`id1` varbinary(16) NOT NULL,
`id2` varbinary(16) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`id4` datetime NOT NULL,
`id5` datetime NOT NULL,
`id6` text DEFAULT NULL,
`id7` text DEFAULT NULL,
`id8` varchar(255) DEFAULT 'pending' COMMENT '同步状态',
`id9` int(11) NOT NULL,
PRIMARY KEY (`id9`,`id1`) CLUSTERED,
UNIQUE KEY `uk_pi_id` (`id1`),
KEY `index_id2` (`id2`),
KEY `index_id4` (`id4`),
KEY `idx_id2_id3` (`id2`,`id3`),
KEY `idx_id8_id4` (`id8`,`id4`),
KEY `idx_id9_id2_id3` (`id9`,`id2`,`id3`));
```

### 2. What did you expect to see? (Required)
```
MySQL [test]> EXPLAIN SELECT a.* FROM test a ignore index(PRIMARY), ( SELECT id9, id2, min(id3) AS id3 FROM test WHERE id9 = 1 AND id2 IN ('1') GROUP BY id9, id2 ) b WHERE a.id9 = b.id9 AND a.id2 = b.id2 AND a.id3 = b.id3;
+----------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexHashJoin_20 | 7984.01 | root | | inner join, inner:IndexLookUp_17, outer key:test1.test.id9, test1.test.id2, Column#19, inner key:test1.test.id9, test1.test.id2, test1.test.id3, equal cond:eq(Column#19, test1.test.id3), eq(test1.test.id2, test1.test.id2), eq(test1.test.id9, test1.test.id9) |
| ├─Selection_29(Build) | 0.80 | root | | not(isnull(Column#19)) |
| │ └─StreamAgg_34 | 1.00 | root | | group by:test1.test.id2, test1.test.id9, funcs:min(test1.test.id3)->Column#19, funcs:firstrow(test1.test.id2)->test1.test.id2, funcs:firstrow(test1.test.id9)->test1.test.id9 |
| │ └─IndexReader_44 | 0.10 | root | | index:IndexRangeScan_43 |
| │ └─IndexRangeScan_43 | 0.10 | cop[tikv] | table:test, index:idx_id9_id2_id3(id9, id2, id3) | range:[1 0x31,1 0x31], keep order:true, stats:pseudo |
| └─IndexLookUp_17(Probe) | 7984.01 | root | | |
| ├─Selection_16(Build) | 7984.01 | cop[tikv] | | not(isnull(test1.test.id2)), not(isnull(test1.test.id3)) |
| │ └─IndexRangeScan_14 | 8000.00 | cop[tikv] | table:a, index:idx_id9_id2_id3(id9, id2, id3) | range: decided by [eq(test1.test.id9, test1.test.id9) eq(test1.test.id2, test1.test.id2) eq(test1.test.id3, Column#19)], keep order:false, stats:pseudo |
| └─TableRowIDScan_15(Probe) | 7984.01 | cop[tikv] | table:a | keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)9 rows in set (0.00 sec)
```
### 3. What did you see instead (Required)
```
MySQL [test]> EXPLAIN SELECT a.* FROM test a ignore index(PRIMARY), ( SELECT id9, id2, min(id3) AS id3 FROM test WHERE id9 = 1 AND id2 IN ('1') GROUP BY id9, id2 ) b WHERE a.id9 = b.id9 AND a.id2 = b.id2 AND a.id3 = b.id3;
+---------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_20 | 7984.01 | root | | inner join, inner:TableReader_16, outer key:test.test.id9, inner key:test.test.id9, equal cond:eq(Column#19, test.test.id3), eq(test.test.id2, test.test.id2), eq(test.test.id9, test.test.id9) |
| ├─Selection_26(Build) | 0.80 | root | | not(isnull(Column#19)) |
| │ └─StreamAgg_31 | 1.00 | root | | group by:test.test.id2, test.test.id9, funcs:min(test.test.id3)->Column#19, funcs:firstrow(test.test.id2)->test.test.id2, funcs:firstrow(test.test.id9)->test.test.id9 |
| │ └─IndexReader_41 | 0.10 | root | | index:IndexRangeScan_40 |
| │ └─IndexRangeScan_40 | 0.10 | cop[tikv] | table:test, index:idx_id9_id2_id3(id9, id2, id3) | range:[1 0x31,1 0x31], keep order:true, stats:pseudo |
| └─TableReader_16(Probe) | 0.80 | root | | data:Selection_15 |
| └─Selection_15 | 0.80 | cop[tikv] | | not(isnull(test.test.id2)), not(isnull(test.test.id3)) |
| └─TableRangeScan_14 | 0.80 | cop[tikv] | table:a | range: decided by [eq(test.test.id9, test.test.id9)], keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

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

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.