ON CONFLICT (columns) DO NOTHING ignores conflicts on non-target unique indexes
- 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
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