matrixorigin / matrixorigin/matrixone
[Bug]: INSERT IGNORE maps AUTO_INCREMENT gaps to accepted rows differently from MySQL
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Summary
In a multi-row `INSERT IGNORE`, MatrixOne assigns AUTO_INCREMENT candidates by original input position before duplicate rows are removed. MySQL assigns consecutive IDs to the accepted rows within the statement's reserved range and consumes the unused reservation afterward.
Consequently, a successful row receives a different generated primary key depending on where an ignored row appeared in the input list, even though MatrixOne and MySQL agree on the inserted payload rows and the next statement's high watermark.
## Environment
- MatrixOne official `main`: `e7bb0572235ec4bac81eb098eb0ad8900f0065ab`
- Clean local single-CN build and isolated data directory
- MySQL 8.3 comparison instance
- Default `auto_increment_increment=1`, `auto_increment_offset=1`
## Reproduction
```sql
CREATE DATABASE insert_ignore_auto_id;
USE insert_ignore_auto_id;
CREATE TABLE t (
id INT AUTO_INCREMENT PRIMARY KEY,
u INT UNIQUE
);
INSERT INTO t(u) VALUES (10); -- id=1
INSERT IGNORE INTO t(u) VALUES (20),(10),(30);
SELECT * FROM t ORDER BY id;
INSERT INTO t(u) VALUES (99);
SELECT * FROM t ORDER BY id;
```
MatrixOne assigns:
```text
1 | 10
2 | 20
4 | 30
5 | 99
```
MySQL assigns:
```text
1 | 10
2 | 20
3 | 30
5 | 99
```
The difference follows the ignored row's position:
```text
Input values MatrixOne accepted IDs MySQL accepted IDs next ID in both
(10),(20),(30) 3,4 2,3 5
(20),(10),(30) 2,4 2,3 5
(20),(30),(10) 2,3 2,3 5
(10),(20),(10),(30) 3,5 2,3 6
```
In every row above, `u=10` already exists before the multi-row statement.
## Expected behavior
For MySQL-compatible `INSERT IGNORE`, accepted rows in one multi-row statement should receive the same generated IDs as MySQL. Ignored rows may consume reserved sequence space, as shown by the next statement starting at 5 or 6, but they should not shift the ID attached to a later accepted row inside the same statement.
The current behavior can break replication checks and application-side generated-key mapping even when the non-key payload and final sequence high watermark otherwise agree.
## Code observation
AUTO_INCREMENT generation occurs in the pre-insert path before the duplicate-ignore filtering path shrinks the candidate batch. The remaining rows retain candidates associated with original positions, which matches the observed positional gaps.
Contributor guide
Assessment
This issue has not been assessed yet.