# 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.