# Technical Advisory 114393

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 ROW` table.
- 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.