Publication date: December 5, 2023
Description
A critical bug has been identified in the optimizer that may lead to incorrect query results, affecting queries involving REGIONAL 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 is resolved in CockroachDB by #114394.
This fix has been applied to maintenance release of CockroachDB v23.1.13, which fixes a bug that could cause a query plan to skip scanning rows from the local region when performing a lookup join with a REGIONAL BY ROW table as the input.
This public issue is tracked by #114393.
Mitigation
Users running affected versions in multi-region clusters are encouraged to upgrade to v23.1.13 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.
Impact
Versions affected include v23.1.0 to v23.1.12, and pre-release versions v23.1.0-beta.1, v23.1.0-beta.2, v23.1.0-beta.3, v23.1.0-rc.1, and v23.1.0-rc.2.
Questions about any technical alert can be directed to our support team.
Was this helpful?
