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.
Was this helpful?
