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:
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.
DoltgreSQL currently discards the conflict target from a targeted
ON CONFLICT (columns) DO NOTHINGclause and lowers the statement tounrestricted
INSERT IGNORE.As a result, a conflict on an unrelated unique index is incorrectly ignored.
Reproduction:
PostgreSQL raises SQLSTATE
23505because the conflict occurs on theemailunique constraint, not the selectedidarbiter.DoltgreSQL currently suppresses the conflict and reports zero inserted rows.
The conflict target should be preserved through planning and execution so
that
DO NOTHINGsuppresses only conflicts from the selected unique indexor constraint.
Coverage should include:
ON CONFLICT ON CONSTRAINT ... DO NOTHING.ON CONFLICT DO NOTHING, which should continue to accept anyapplicable uniqueness conflict.