> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Technical Advisory 88047

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

Publication date: September 29, 2022

## Description

In CockroachDB <InternalLink version="releases" path="v22.1">v22.1.0</InternalLink> to <InternalLink version="releases" path="v22.1">v22.1.7</InternalLink>, querying a [`REGIONAL BY ROW`](https://www.cockroachlabs.com/docs/v22.1/multiregion-overview#regional-by-row-tables) or [partitioned](https://www.cockroachlabs.com/docs/v22.1/partitioning) multi-region table with an [inverted index](https://www.cockroachlabs.com/docs/v22.1/inverted-indexes) could produce incorrect results if the query has a [`LIMIT`](https://www.cockroachlabs.com/docs/v22.1/limit-offset) of less than 100,000 and uses the inverted index. In this case, it is possible that the [optimizer](https://www.cockroachlabs.com/docs/v22.1/cost-based-optimizer) will erroneously discard the predicate over the inverted index and therefore return some rows that do not match the predicate.

## Statement

The fix has been applied to the maintenance release of CockroachDB <InternalLink version="releases" path="v22.1">v22.1.8</InternalLink>.

## Mitigation

Users of CockroachDB <InternalLink version="releases" path="v22.1">v22.1.0</InternalLink> to <InternalLink version="releases" path="v22.1">v22.1.7</InternalLink> are encouraged to upgrade to <InternalLink version="releases" path="v22.1">v22.1.8</InternalLink> or a later version.

To determine whether your queries may be affected by this issue on <InternalLink version="releases" path="v22.1">v22.1.0</InternalLink> to <InternalLink version="releases" path="v22.1">v22.1.7</InternalLink>, you should examine the query plans of any [`LIMIT`](https://www.cockroachlabs.com/docs/v22.1/limit-offset) queries over [`REGIONAL BY ROW`](https://www.cockroachlabs.com/docs/v22.1/multiregion-overview#regional-by-row-tables) or [partitioned](https://www.cockroachlabs.com/docs/v22.1/partitioning) multi-region tables with [inverted indexes](https://www.cockroachlabs.com/docs/v22.1/inverted-indexes) by using [`EXPLAIN (OPT)`](https://www.cockroachlabs.com/docs/v22.1/explain#opt-option). If the resulting plan shows [locality-optimized-search](https://www.cockroachlabs.com/docs/v22.1/cost-based-optimizer#locality-optimized-search-in-multi-region-clusters), these queries may produce incorrect results.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPLAIN(OPT)
  SELECT * FROM t88047
WHERE json_col->'loc' @> '{"state":"NY"}'
  LIMIT 2
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
----
index-join t88047
 └── locality-optimized-search
      ├── scan t88047@t88047_inv_idx
      │    ├── constraint: /11: [/'ap-southeast-2' - /'ap-southeast-2']
      │    └── limit: 2
      └── scan t88047@t88047_inv_idx
           ├── constraint: /18
           │    ├── [/'ca-central-1' - /'ca-central-1']
           │    └── [/'us-east-1' - /'us-east-1']
           └── limit: 2
```

Note that this query plan is incorrect: it should contain an "inverted constraint" with the JSON predicate, but it does not, which is why the query may return incorrect results. To mitigate this problem for affected queries on <InternalLink version="releases" path="v22.1">v22.1.0</InternalLink> to <InternalLink version="releases" path="v22.1">v22.1.7</InternalLink>, either remove [inverted indexes](https://www.cockroachlabs.com/docs/v22.1/inverted-indexes) from all [`REGIONAL BY ROW`](https://www.cockroachlabs.com/docs/v22.1/multiregion-overview#regional-by-row-tables) and [partitioned](https://www.cockroachlabs.com/docs/v22.1/partitioning) multi-region tables, or set the session setting `locality_optimized_partitioned_index_scan` or cluster setting `sql.defaults.locality_optimized_partitioned_index_scan.enabled` to false, which disables [locality-optimized-search](https://www.cockroachlabs.com/docs/v22.1/cost-based-optimizer#locality-optimized-search-in-multi-region-clusters).

The best mitigation, however, is to upgrade to <InternalLink version="releases" path="v22.1">v22.1.8</InternalLink> as soon as possible.

## Impact

Some queries over [`REGIONAL BY ROW`](https://www.cockroachlabs.com/docs/v22.1/multiregion-overview#regional-by-row-tables) or [partitioned](https://www.cockroachlabs.com/docs/v22.1/partitioning) multi-region tables with a [`LIMIT`](https://www.cockroachlabs.com/docs/v22.1/limit-offset) clause could produce incorrect results if the scanned index is `INVERTED` and uses an inverted constraint with [`JSONB`](https://www.cockroachlabs.com/docs/v22.1/jsonb) operators, [`ARRAY`](https://www.cockroachlabs.com/docs/v22.1/array) operators, or [operators](https://www.cockroachlabs.com/docs/v22.1/functions-and-operators#operators) on spatial data. Versions affected include <InternalLink version="releases" path="v22.1">v22.1.0-alpha.1</InternalLink> to <InternalLink version="releases" path="v22.1">v22.1.7</InternalLink>.

Please reach out to the [support team](https://support.cockroachlabs.com/) if more information or assistance is needed.