Skip to content

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

Description

@fulghum

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions