# Technical Advisory 88047

Publication date: September 29, 2022

## Description

In CockroachDB v22.1.0 to v22.1.7, querying a `REGIONAL BY ROW` or partitioned multi-region table with an inverted index could produce incorrect results if the query has a `LIMIT` of less than 100,000 and uses the inverted index. In this case, it is possible that the optimizer will erroneously discard the predicate over the inverted index and therefore return some rows that do not match the predicate.

## Statement

This is resolved in CockroachDB by #88079.

The fix has been applied to the maintenance release of CockroachDB v22.1.8.

This public issue is tracked by #88047.

## Mitigation

Users of CockroachDB v22.1.0 to v22.1.7 are encouraged to upgrade to v22.1.8 or a later version.

To determine whether your queries may be affected by this issue on v22.1.0 to v22.1.7, you should examine the query plans of any `LIMIT` queries over `REGIONAL BY ROW` or partitioned multi-region tables with inverted indexes by using `EXPLAIN (OPT)`. If the resulting plan shows locality-optimized-search, these queries may produce incorrect results.

For example:

    EXPLAIN(OPT)
      SELECT * FROM t88047
    WHERE json_col->'loc' @> '{"state":"NY"}'
      LIMIT 2

    ----
    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 v22.1.0 to v22.1.7, either remove inverted indexes from all `REGIONAL BY ROW` and partitioned 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.

The best mitigation, however, is to upgrade to v22.1.8 as soon as possible.

## Impact

Some queries over `REGIONAL BY ROW` or partitioned multi-region tables with a `LIMIT` clause could produce incorrect results if the scanned index is `INVERTED` and uses an inverted constraint with `JSONB` operators, `ARRAY` operators, or operators on spatial data. Versions affected include v22.1.0-alpha.1 to v22.1.7.

Please reach out to the support team if more information or assistance is needed.