CockroachDB partial indexes expose a shared-projection bug
Advisory A-74385 concerns partial indexes with identical predicates and different updated columns. The optimizer incorrectly simplified a shared write-control value, leaving an index inconsistent with its table.
Two partial indexes can include exactly the same rows while indexing different values. An update may therefore need to change one index and leave the other alone. CockroachDB advisory A-74385 concerns an optimization that confused those two facts, causing a shared control expression to suppress index work that was still required.
The January 6 advisory identifies the necessary combination. A table has at least two partial indexes with identical WHERE clauses, and an update changes columns included in one index but not another. Both unique and non-unique partial indexes can be affected. Queries using a corrupted index can then return incorrect results.
I value CockroachDB's emphasis on serializable transactions, but an index-maintenance defect is a separate correctness issue. Transaction isolation cannot make a lookup correct when the database has failed to maintain the representation that lookup uses. The advisory and its small reproducer make that separation concrete.
Identical predicates do not imply identical mutations
A partial index includes rows selected by its predicate. If two indexes both use a condition such as a null deletion timestamp, their membership decision can share the same expression. Their keys can still differ. Updating one indexed field requires work for its index even when another index's key remains unchanged.
The fix in pull request 74431, merged January 5, explains the internal mistake. The optimizer synthesized columns controlling partial-index additions and deletions. When multiple partial indexes used the same predicate, those indexes could reuse a synthesized column.
The simplification rule saw that one index had no changed columns and replaced its write-control value with false. Because the value was shared, that replacement also disabled writes for an index whose columns did change. The optimization was locally plausible for one index but incorrect for all uses of the shared expression.
This is why merely noticing a repeated predicate is insufficient to establish the safety of the transformation. Predicate equivalence answers whether the row belongs in each index. It does not establish that changing a particular source column leaves both index representations untouched.
The report demonstrates disagreement between access paths
The original issue 74385, filed January 3, uses two unique partial indexes on an assets table. Both select rows whose deletion timestamp is null. One indexes an external identifier with a tenant identifier, while the other indexes a legacy identifier with that tenant identifier.
After changing the external identifier to null, the reporter queries using its former value. The index-based query still returns the row, even though the displayed row has a null external identifier. A query forced through the primary index returns no matching row, which is the expected result for that condition.
That contrast is stronger evidence than a general complaint about a surprising plan. The same logical predicate produces different answers depending on the access path. The stale secondary entry and the current row disagree. An application can therefore receive a row that does not satisfy the condition it asked the database to evaluate.
The report identifies version 21.2.3 and reproduces the behavior on a single node. Its relevance is not limited to consensus across several replicas. The demonstrated failure occurs in planning and maintaining SQL indexes, before distribution can supply any different answer to what those entries should contain.
The repair tracks all consumers of the expression
The patch changes the simplification eligibility check. A synthesized addition or deletion column used by multiple partial indexes becomes ineligible for replacement with false through this rule. It can no longer be simplified solely because one of its index consumers has no changed columns.
The patch includes a regression with two partial indexes sharing a predicate. It inserts a row, changes a column belonging to one index, and checks an indexed query against the old value. The expected result contains no rows. An optimizer test also verifies that the shared control column survives the transformation.
Those checks address both the transformation and its visible consequence. Retaining the expression matters because the executor still needs the index writes it controls. Verifying only that the optimizer accepts the update would not establish that queries stop finding the stale key.
I find the patch explanation useful because it names the incorrect assumption. Sharing an expression is safe only when later transformations account for every consumer. The membership expression could be reused, but a consumer-specific conclusion could not replace it for the entire mutation.
Preventing new damage and repairing existing indexes differ
The advisory identifies the affected 21.1 and 21.2 branches and names 21.1.13 and 21.2.4 as the maintenance versions carrying the fix. Its mitigation distinguishes installing fixed code from reconstructing existing indexes. After an upgrade, it directs users to drop and recreate all partial indexes so that earlier corruption does not remain.
That extra work follows from the failure mode. A repaired optimizer changes how later updates maintain an index. It does not necessarily discover and replace every entry an earlier update left behind. Recreating the structure supplies index contents derived from the table again.
The documented workaround avoids having multiple partial indexes in one table with identical predicates. It describes removing or changing predicates, while warning that changes to unique partial indexes can change their uniqueness guarantees. That is a semantic consequence, not merely a performance consideration. An index definition can enforce an application rule in addition to providing a lookup path.
The advisory's conditions therefore help identify the relevant schema arrangement without reducing the response to dropping indexes indiscriminately. The issue is not limited to uniqueness enforcement. A non-unique partial index can also hold a stale representation and influence ordinary selection. The predicate and the changed column determine the documented trigger, so the absence of a uniqueness constraint does not exclude an otherwise matching table from consideration. The shared-predicate pattern explains exposure, and the affected mutation explains how the inconsistency arises. Reconstructing an index addresses existing entries, while the optimizer repair preserves the write-control expression needed by future updates.