cockroachdb / cockroachdb/cockroach
sql/importer: exact duplicate rows sometimes not rejected as duplicates
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
reproduction:
```
# Make three files that each have the same row: 1,2,3,
> export into csv 'userfile:///x' from (select 1, 2, 3);
> export into csv 'userfile:///y' from (select 1, 2, 3);
> export into csv 'userfile:///z' from (select 1, 2, 3);
# IMPORT the three files -- this should either fail because there's a duplicate or succeed and import three rows. Let's make a PK so it fails since we specify the same value -- 1 -- more than once.
> create table abc (a int primary key, b int, c int);
> import into abc csv data ('userfile:///x/*', 'userfile:///y/*', 'userfile:///z/*') ;
# Weird, it didn't fail... so we imported three rows, right?
demo@127.0.0.1:26257/movr> select count(*) from abc;
count
---------
1
(1 row)
```
Note that duplicates that end up in the same buffer inside IMPORT before flushing _are_ rejected as expected: when the buffer is sorted and tries to flush it will notice the duplicate key when it fails to construct an sstable, and immediately sequential duplicates will be rejected before they are even buffered. But if the duplicates end up in separate batches, or are processed by separate nodes entirely, for example if they occur in different input files, then the first chance to detect the collision is when the batch is actually sent to KV, using the parameter `DisallowShadowingBelow` and the function it invokes `checkForKeyCollisions`.
But here we ignore a collision if it is with an existing key if the value is exactly the same and the timestamp indicates it was imported during the same IMPORT (key timestamp >= import start time, which is provided to the disallowShadowingBelow flag). This is done because an import which is resumed after being paused or retried after an error is _expected_ to re-read and re-flush some keys which it had previous imported before being paused or retried, i.e. anything above the most recent progress checkpoint. However in allowing these re-imports of the same input row, we're allowing allowing re-imports of a different input row with the same values.
Jira issue: CRDB-24396
Contributor guide
Assessment
This issue has not been assessed yet.