matrixorigin / matrixorigin/matrixone

[Bug]: INSERT IGNORE maps AUTO_INCREMENT gaps to accepted rows differently from MySQL

Open
#28,238 2 comments 0 reactions 1 assignee Claimed by @ck89119 View on GitHub
kind/bug severity/s0
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

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.