Technical Advisory 88047

Publication date: September 29, 2022

Description

In CockroachDB to , 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

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

Mitigation

Users of CockroachDB to are encouraged to upgrade to or a later version. To determine whether your queries may be affected by this issue on to , 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 to , 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 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 to . Please reach out to the support team if more information or assistance is needed.

​Description

​Statement

​Mitigation

​Impact

Description

Statement

Mitigation

Impact