Technical Advisory 88993

Publication date: October 17, 2022

Description

In CockroachDB v22.1.0 to v22.1.8, a query with ORDER BY and LIMIT clauses could return incorrect results if it scanned a multi-column index containing the ORDER BY columns, and a prefix of the index columns was held fixed to two or more constant values by the query filter or schema. Examples of schema elements that could constrain the index columns include:

• A CHECK constraint
• A computed column expression
• A PARTITION BY clause

This issue could also cause a query that performs an aggregation with min or max aggregate functions to produce incorrect results, since the optimizer might have transformed it into a query using ORDER BY and LIMIT.

Statement

This is resolved in CockroachDB by #89113.

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

This public issue is tracked by #88993.

Mitigation

Users of CockroachDB v22.1.0 to v22.1.8 are encouraged to upgrade to v22.1.9 or a later version. To determine whether your queries may be affected by this issue on v22.1.0 to v22.1.8, examine the query plans of any queries with an ORDER BY and LIMIT clause or aggregation with min or max by using an EXPLAIN (OPT) statement. In particular, you should examine plans for queries that have a multi-column index containing the ORDER BY, min, or max columns and for which a prefix of the index columns are constrained to two or more constant values by one of the following:

• A query filter (e.g., WHERE a IN (1, 3))
• A CHECK constraint (e.g., CHECK (a IN (1, 3)))
• A computed column expression (e.g., b INT AS (a + 10) STORED given column a has filter WHERE a IN (1, 3))
• A PARTITION BY clause (e.g., INDEX (a, ...) PARTITION BY LIST (a) (PARTITION p VALUES ((1), (3))))

If the plan shown by EXPLAIN (OPT) uses the multi-column index and shows a union-all, these queries may produce incorrect results.

To mitigate this problem for affected queries on v22.1.0 to v22.1.8, remove the multi-column index that is used by the scans that are children of the union-all.

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

Impact

Some queries with an ORDER BY and LIMIT clause or aggregation with min or max could produce incorrect results if a table in the query contained a multi-column index with the ORDER BY, min, or max columns, and a prefix of the index columns was held fixed to two or more constant values by the query or schema. Versions affected include v22.1.0-alpha.1 to v22.1.8 and v22.2.0-alpha.1 to v22.2.0-beta.2.

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