matrixorigin / matrixorigin/matrixone

[Bug]: LOAD DATA IGNORE/REPLACE do not handle duplicate primary or unique keys

Open
#25,470 4 comments 0 reactions 1 assignee Claimed by @aptend View on GitHub
deferred kind/bug needs-triage
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

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.