release-20.1: opt: fix bug where ON CONFLICT DO NOTHING ignores some input #59172
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Prior to this patch, it was possible for some valid input to an
INSERT ... ON CONFLICT DO NOTHING to be discarded. For example,
consider the following example:
CREATE TABLE uniq (
k INT PRIMARY KEY,
v INT UNIQUE,
w INT UNIQUE,
x INT,
y INT DEFAULT 5,
UNIQUE (x, y)
);
INSERT INTO uniq VALUES (1, 1, 1, 1, 1);
INSERT INTO uniq VALUES (1, 20, 20, 20, 20),
(20, 1, 20, 20, 20),
(20, 20, 20, 20, 20)
ON CONFLICT DO NOTHING;
Since row (20, 20, 20, 20, 20) does not conflict with the existing
row in uniq, it should be inserted. However, prior to this patch, all
three rows in the second INSERT statement were discarded.
This commit fixes the problem by applying the upsert-distinct-on
operators for each index after all conflicting rows are removed by
left-joins + filters.
Fixes cockroachdb#59125
Release note (bug fix): Fixed a bug in which some non-conflicting rows
provided as input to an INSERT ... ON CONFLICT DO NOTHING statement could
be discarded, and not inserted. This could happen in cases where the
table had one or more unique indexes in addition to the primary index,
and some of the rows in the input conflicted with existing values in one
or more unique index. This scenario could cause the rows that did not
conflict to be erroneously discarded. This has now been fixed.
|
This change is |
mgartner
approved these changes
Jan 20, 2021
There was a problem hiding this comment.
Reviewed 5 of 5 files at r1.
Reviewable status:complete! 0 of 0 LGTMs obtained (waiting on @RaduBerinde)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Backport 1/1 commits from #59147.
/cc @cockroachdb/release
Prior to this patch, it was possible for some valid input to an
INSERT ... ON CONFLICT DO NOTHINGto be discarded. For example,consider the following example:
Since row
(20, 20, 20, 20, 20)does not conflict with the existingrow in uniq, it should be inserted. However, prior to this patch, all
three rows in the second
INSERTstatement were discarded.This commit fixes the problem by applying the
upsert-distinct-onoperators for each index after all conflicting rows are removed by
left-joins + filters.
Fixes #59125
Release note (bug fix): Fixed a bug in which some non-conflicting rows
provided as input to an
INSERT ... ON CONFLICT DO NOTHINGstatement couldbe discarded, and not inserted. This could happen in cases where the
table had one or more unique indexes in addition to the primary index,
and some of the rows in the input conflicted with existing values in one
or more unique index. This scenario could cause the rows that did not
conflict to be erroneously discarded. This has now been fixed.