Skip to content

Latest commit

 

History

History
73 lines (50 loc) · 3.02 KB

a81448.md

File metadata and controls

73 lines (50 loc) · 3.02 KB
title advisory summary toc affected_versions advisory_date docs_area
Technical Advisory 81448
A-81448
Secondary indexes containing columns that are not null, have a volatile default expression, and are present in one or more secondary indexes will have inconsistent values relative to the primary index, which can lead to incorrect query results.
true
v21.1.x, v21.2.0 to v21.2.12, v22.1.0
2022-06-28
releases

Publication date: {{ page.advisory_date | date: "%B %e, %Y" }}

Description

In CockroachDB v21.1.x, v21.2.0 to v21.2.12, and v22.1.0, adding a column which:

  • is not null,
  • has a volatile default expression,
  • and is present in one or more secondary indexes,

will backfill different default values in the primary and the secondary indexes. This can therefore lead to incorrect query results.

For example, the following statements will cause the column b to contain different values in the primary index t_pkey and the secondary index t_b_key:

CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t VALUES (1), (2);
ALTER TABLE t ADD COLUMN b FLOAT NOT NULL UNIQUE DEFAULT random();

This is evidenced by executing the queries SELECT b FROM t@t_pkey and SELECT b FROM t@t_b_key, which will have different results.

Statement

This is resolved in CockroachDB by PR #81486, which fixes the incorrect backfill logic.

The fix is applied to CockroachDB maintenance version v22.1.1, and is expected to be applied to maintenance version v21.2.13.

This public issue is tracked by #81448.

Mitigation

Users of CockroachDB v21.2.0-21.2.12 and v22.1.0 are encouraged to upgrade to v21.2.13 or v22.1.1, or to a later version.

Alternatively, and re-using the earlier example:

  • Drop and recreate all secondary indexes containing the added column:

    {% include_cached copy-clipboard.html %}

     DROP INDEX t@t_b_key CASCADE;
 CREATE UNIQUE INDEX t_b_key on t(b);

  • Or, instead, add the new column and then add and backfill the default value in a separate transaction:

    {% include_cached copy-clipboard.html %}

     ALTER TABLE t ADD COLUMN b FLOAT UNIQUE;
 BEGIN;
 ALTER TABLE t ALTER COLUMN b SET DEFAULT random();
 UPDATE t SET b = random() WHERE b IS NULL;
 ALTER TABLE t ALTER COLUMN b SET NOT NULL;
 COMMIT;

Note that the latter transaction is somewhat contrived because in v21.1 and v21.2, an open transaction cannot begin with a DML statement if it is to be followed by DDL statements. This limitation has since been removed in v22.1.

Impact

Versions v21.1.x, v21.2.0 to v21.2.12, and v22.1.0 are affected. In these versions, secondary indexes containing columns that are not null, have a volatile default expression, and are present in one or more secondary indexes will have inconsistent values relative to the primary index, which can lead to incorrect query results.

Please reach out to the support team if more information or assistance is needed.