Am I misunderstanding how Unique Keys in a Sharded (columnstore) table should work?

[nlello]

Hi,

Singlestore 8.0.9 ; the cluster was originally built as 7.8 and has been upgraded several times since the tables were created.

We have a (large) Columnstore with approximately 208Million rows; we’ve discovered that we have approximately 42k rows that are duplicates according to the Unique Key we have on the table.

The shard key: a,b
Unique key: a,b,c,d,e,f,g,h,i,j,k

Some of the unique key fields are NULLable … could this be what’s allowing duplicate rows to be created ?

[hanson]

Yes. If there are NULL values in a record that are part of the unique key, a duplicate key error will not be generated. This follows the SQL standard.

Behavior Change Due to Unique Key Null Handling Bug Fix in 7.0

In MemSQL 6.8 and earlier, the code incorrectly handles NULL values in unique key columns for the case of a composite unique key, only in the case that the first column’s data type is defined to be NOT NULL but another column’s type is defined to be NULL. In MemSQL 7.0 we found this bug and fixed it, changing the behavior to follow the SQL standard [SQL13]. Application software should not rely on the old behavior present in MemSQL 6.8 when upgrading to 7.0 or later. To avoid confusion, MemSQL …