matrixorigin / matrixorigin/matrixone
[Bug]: LOAD DATA IGNORE/REPLACE do not handle duplicate primary or unique keys
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Bug Report
### Description
`LOAD DATA ... IGNORE` and `LOAD DATA ... REPLACE` are accepted by the parser, but they do not handle duplicate `PRIMARY KEY` / `UNIQUE KEY` conflicts.
On latest `main`, `LOAD DATA` can import duplicate rows into tables that have PK/UK constraints. This happens for at least CSV, JSONLINE, and Parquet inputs.
This is different from normal `INSERT`, which correctly reports `Duplicate entry` for the same table definition and data.
### Version
Reproduced on latest `main`:
```sql
select git_version();
-- 3db8ca2cc
```
### Steps to Reproduce
Prepare a CSV file:
```bash
printf '1,a\n1,b\n' > /tmp/mo_load_csv_dup_pk.csv
printf '1,a\n2,b\n' > /tmp/mo_load_csv_pk_1_2.csv
printf '1,same\n2,same\n' > /tmp/mo_load_csv_dup_uk.csv
```
Run SQL:
```sql
drop database if exists load_data_duplicate_options;
create database load_data_duplicate_options;
use load_data_duplicate_options;
-- Control case: normal INSERT rejects duplicate PK.
create table direct_pk(id bigint primary key, name varchar(16));
insert into direct_pk values (1,'a'),(1,'b');
-- ERROR 1062: Duplicate entry '1' for key 'id'
-- LOAD DATA IGNORE: expected to skip duplicate key rows, but it imports both rows.
create table t_ignore(id bigint primary key, name varchar(16));
load data infile {'filepath'='/tmp/mo_load_csv_dup_pk.csv', 'format'='csv'}
ignore into table t_ignore fields terminated by ',';
select * from t_ignore order by id,name;
-- LOAD DATA REPLACE: expected to replace the conflicting row, but it imports both rows.
create table t_replace(id bigint primary key, name varchar(16));
load data infile {'filepath'='/tmp/mo_load_csv_dup_pk.csv', 'format'='csv'}
replace into table t_replace fields terminated by ',';
select * from t_replace order by id,name;
-- Existing row conflict + IGNORE.
create table t_ignore_existing(id bigint primary key, name varchar(16));
insert into t_ignore_existing values(1,'old');
load data infile {'filepath'='/tmp/mo_load_csv_pk_1_2.csv', 'format'='csv'}
ignore into table t_ignore_existing fields terminated by ',';
select * from t_ignore_existing order by id,name;
-- UNIQUE KEY conflict + IGNORE.
create table t_ignore_uk(id bigint primary key, name varchar(16), unique key uk_name(name));
load data infile {'filepath'='/tmp/mo_load_csv_dup_uk.csv', 'format'='csv'}
ignore into table t_ignore_uk fields terminated by ',';
select * from t_ignore_uk order by id,name;
```
### Actual Behavior
`LOAD DATA ... IGNORE` imports duplicate PK rows:
```text
1 a
1 b
```
`LOAD DATA ... REPLACE` also imports duplicate PK rows:
```text
1 a
1 b
```
When the target table already has `(1,'old')`, `LOAD DATA ... IGNORE` imports another row with `id = 1` instead of skipping it:
```text
1 a
1 old
2 b
```
For UNIQUE KEY conflicts, duplicate unique-key values are also inserted:
```text
1 same
2 same
```
I also reproduced the same duplicate constraint bypass with JSONLINE and Parquet `LOAD DATA` paths.
### Expected Behavior
`LOAD DATA ... IGNORE` should follow MySQL-compatible duplicate-key semantics:
- skip rows that conflict with existing PK/UK values;
- avoid inserting duplicate rows from the same input file.
`LOAD DATA ... REPLACE` should follow MySQL-compatible duplicate-key semantics:
- replace the existing conflicting row instead of keeping both rows;
- not leave duplicate PK/UK values in the target table.
If MatrixOne intentionally does not support duplicate-key handling for `LOAD DATA`, the syntax should probably be rejected or documented as unsupported. Currently the syntax is accepted but silently produces data that violates PK/UK constraints.
### Additional Notes
This does not appear to be Parquet-specific. It reproduces with at least:
- `format='csv'`
- `format='jsonline'`
- `format='parquet'`
Contributor guide
Assessment
This issue has not been assessed yet.