dolthub / dolthub/doltgresql

ON CONFLICT (columns) DO NOTHING ignores conflicts on non-target unique indexes

Open
#3,192 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

DoltgreSQL currently discards the conflict target from a targeted
`ON CONFLICT (columns) DO NOTHING` clause and lowers the statement to
unrestricted `INSERT IGNORE`.

As a result, a conflict on an unrelated unique index is incorrectly ignored.

Reproduction:

```sql
CREATE TABLE t (
id INT PRIMARY KEY,
email TEXT UNIQUE
);

INSERT INTO t VALUES (1, 'a');

INSERT INTO t VALUES (2, 'a')
ON CONFLICT (id) DO NOTHING;
```

PostgreSQL raises SQLSTATE `23505` because the conflict occurs on the
`email` unique constraint, not the selected `id` arbiter.

DoltgreSQL currently suppresses the conflict and reports zero inserted rows.

The conflict target should be preserved through planning and execution so
that `DO NOTHING` suppresses only conflicts from the selected unique index
or constraint.

Coverage should include:

- A conflict on the selected primary-key arbiter.
- A conflict on a non-selected secondary unique index.
- Multiple candidate unique indexes.
- `ON CONFLICT ON CONSTRAINT ... DO NOTHING`.
- Untargeted `ON CONFLICT DO NOTHING`, which should continue to accept any
applicable uniqueness conflict.

Contributor guide

Open the contributing guide

Research direction

Trace the targeted ON CONFLICT path through planning and execution, focusing on where the conflict target is discarded and INSERT IGNORE is selected. Add regression coverage for selected and non-selected unique indexes, multiple candidate indexes, named constraints, and untargeted DO NOTHING; done means only the intended conflicts are suppressed.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.