Skip to main content
Publication date: November 29, 2021

​
Description

In CockroachDB 21.2.0, the optimizer could plan queries that use semi-joins against multi-region REGIONAL BY ROW tables incorrectly. Uniqueness constraint checks against REGIONAL BY ROW tables fall into this category, so inserts into UNIQUE columns, including PRIMARY KEY columns, in multi-region tables could fail to uphold the UNIQUE constraint. This issue only occurs against REGIONAL BY ROW tables in CockroachDB 21.2.0.

​
Statement

This is resolved by a fix in CockroachDB which disables the incorrect planning of locality-optimized semi-joins with ON-conditions. The fix has been applied to .

​
Mitigation

Users of CockroachDB 21.2.0 are encouraged to audit their REGIONAL BY ROW tables with UNIQUE constraints for uniqueness and upgrade to 21.2.1. To prevent further unique violations from occurring while still on 21.2.0, you can disable locality optimized search by setting the cluster setting sql.defaults.locality_optimized_partitioned_index_scan.enabled or session setting locality_optimized_partitioned_index_scan to false. This will likely hurt performance, however, so the best mitigation is to upgrade to 21.2.1 as soon as possible. These settings should be reenabled upon upgrading to 21.2.1. To determine whether your REGIONAL BY ROW tables have any uniqueness constraint violations, you can either attempt to create a new UNIQUE index on each set of unique columns, or run a query for each UNIQUE constraint to determine whether the table contains any duplicate keys. For example:
If this query returns any results, that indicates the table contains unique constraint violations. To fix the unique violations, you will need to manually update all but one of the duplicate keys with a different value.

​
Impact

REGIONAL BY ROW tables could contain non-unique data in unique columns if insert into from multiple regions in 21.2.0. Additionally, REGIONAL BY ROW tables might have returned incorrect results for queries that use a semi-join, such as:
or
For queries such as these, CockroachDB might not have returned all results if the relevant data was spread across different regions. Please reach out to the support team if you need more information or assistance.