Technical Advisory 81448

On this page

Publication date: June 28, 2022

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:

  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:

  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.

Was this helpful?