# Technical Advisory 78681

Publication date: April 11, 2022

## Description

The optimizer has been found to create logically incorrect query plans in some cases, which can cause incorrect query results. The bug can present if all of the following conditions are true:

1. The query contains a semi-join with an equality filter, such as queries in the form: `SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.a = t2.a)`.
2. The inner table has an index containing the column in the equality filter, like `t2.a` in the example query above.
3. The index contains one or more columns that prefix the equality column.
4. The prefix columns are constrained to a set of constant values via the query filter or a `CHECK` constraint, for example via an `IN` operator. In the case of a `CHECK` constraint, the columns must be `NOT NULL`.

## Statement

The bug is resolved in CockroachDB PR 78685.

The fix has been applied to maintenance versions 21.1.17 and 21.2.8 of CockroachDB.

This public issue is tracked by 78681.

## Mitigation

Users of CockroachDB are encouraged to upgrade to a maintenance version with the fix applied: v21.1.17 and v21.2.8.

## Impact

Versions affected include v21.1.0 to v21.1.16, v21.2.0 to v21.2.7, and v22.1.0-alpha.1 to v22.1.0-alpha.5.

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