CockroachDB fixes local rows omitted by a regional lookup plan

Searching nearby data first should change a query's cost without changing which qualifying rows it returns. A CockroachDB optimizer defect violated that requirement for some locality-optimized lookup joins involving REGIONAL BY ROW tables. The resulting plan could skip scanning rows in the local region. A successful query could therefore return an incomplete result without a storage failure or an unavailable node.

The December 5 advisory A-114393 describes the affected query conditions and provides a session-setting mitigation. The 23.1.13 release notes date the corrected maintenance release December 11 and explicitly identify the missing-local-scan fix. This post uses that later date so the available release and the advisory can be discussed together.

I accept CockroachDB's inter-region coordination latency because consistency is an explicit requirement. Locality optimization is attractive when it reduces that cost for a query, but the optimizer must preserve the query's result. This advisory concerns that preservation, rather than a choice between stronger and weaker transaction isolation.

Affected queries combine locality with additional filtering

The advisory gives three conditions. The query has a join operation, reads a REGIONAL BY ROW table, and includes additional filters unrelated to the join. Equality or inequality tests against constants are examples of the extra filters. These conditions describe the query forms in which the faulty plan can occur.

The join need not appear as an explicit JOIN keyword. The advisory includes a correlated subquery as an implicit join example. Reading only the original SQL text for an obvious join clause would therefore miss part of the documented scope. The optimizer's relational representation can include join work that the application expressed through another syntax.

The failure belongs to a locality-optimized lookup join. The release notes identify the regional table as the input whose local rows can be skipped. Omitting those rows changes the result, independently of where the remaining work executes. Preserving that distinction is necessary because the application may receive an ordinary successful response containing too few rows.

The sources do not say that every join over a regional table fails. They identify a plan form and additional filtering conditions. A query can use the relevant table without receiving that plan, so the table locality alone is not a complete reproduction of the issue. The advisory's configuration workaround avoids the optimization path instead of trying to enumerate every application query that might reach it.

Correctly stored rows can disappear from an answer

The described defect skips a scan. It does not require the omitted local rows to be absent from the database. That separates query-result correctness from data durability. A replicated row can remain intact while a planner error produces an execution path that never reads it.

This also explains why a cluster can appear healthy while the application observes missing data. Storage and replication checks concern the copies that exist. They do not independently establish that every query plan examines the required subset of those copies. The advisory locates the error in the optimizer and the chosen lookup plan.

An additional filter matters because query transformations must preserve both join relationships and eligibility conditions. The presence of a constant comparison does not authorize removing an otherwise required local scan. The published description identifies that combination without presenting a full derivation of the faulty transformation, so a more detailed internal cause would require evidence beyond these sources.

My preference is to describe this as a wrong-result defect with a defined optimization path. Calling it a latency problem would understate the consequence. Calling it general multi-region data loss would overstate the mechanism, because the sources describe omitted query rows rather than deletion of stored data.

The session workaround disables the optimization

The advisory gives a direct way to prevent the bug before upgrading. It disables locality-optimized search through this session setting.

SET locality_optimized_partitioned_index_scan = off;

The scope is the session executing the affected workload. Applying it in one administrative connection does not establish that an existing application connection now uses the setting. The mitigation needs to reach the sessions that plan and execute the relevant queries, because the defect is in an optimization selected for those queries.

Cockroach Labs explicitly warns that disabling the optimization increases latency for some queries involving REGIONAL BY ROW tables. That is the stated tradeoff of the workaround. The advisory supplies no universal slowdown factor, because different queries need different regional work. The latency consequence should therefore remain tied to the affected optimization, without turning an unspecified cost into a numerical claim. It removes an optimization opportunity to avoid the known incomplete-result path, rather than relaxing a consistency guarantee to achieve lower latency.

The fixed release repairs the plan behavior while retaining the optimization. Users on affected multi-region clusters are encouraged to upgrade to 23.1.13 or later. As of December 11, 23.1.13 is the named available correction, and its release notes repeat the local-scan failure in the bug-fix list.

Version and plan scope belong together

The advisory lists 23.1.0 through 23.1.12 as affected stable releases. It also identifies the three 23.1 beta releases and two release candidates in its prerelease scope. That establishes a branch interval, rather than a claim that every CockroachDB version has the same regional-plan bug.

Within that interval, the multi-region layout and query conditions determine relevance. A version inventory identifies potentially affected code. Knowledge of table locality and query planning identifies whether the workload can exercise it. The workaround is useful precisely because it addresses the common optimization path without requiring a separate SQL rewrite for each correlated or explicit join.

The release repairs several join-related issues, but they should not be conflated. Its notes also describe a separate lookup or index join problem involving tables with at least three column families. A-114393 concerns omitted local-region rows in the stated regional plan. The corresponding fix ensures that the plan includes the required local scan, so locality-based execution preserves the input rows demanded by the query.