Description
A critical bug has been identified in the optimizer that may lead to incorrect query results, affecting queries involvingREGIONAL BY ROW tables. The bug can cause rows in the local region of a REGIONAL BY ROW table to be omitted from query results when performing a locality-optimized lookup join. The bug manifests under the following conditions:
- The query includes at least one join operation, either explicitly with the JOIN keyword or implicitly, for example, a correlated subquery.
- The query fetches data from a
REGIONAL BY ROWtable. - Additional non-join related filters are present, such as equality (or inequality) conditions with constant values.
Statement
This fix has been applied to maintenance release of CockroachDB , which fixes a bug that could cause a query plan to skip scanning rows from the local region when performing a lookup join with aREGIONAL BY ROW table as the input.
Mitigation
Users running affected versions in multi-region clusters are encouraged to upgrade to or a later version. Without upgrading, the bug can only be prevented by disabling locality-optimized search with the session setting:SET locality_optimized_partitioned_index_scan=off. Note that this will increase the latency for some queries involving REGIONAL BY ROW tables.