Publication date: April 11, 2022
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:
- 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).
- The inner table has an index containing the column in the equality filter, like
t2.ain the example query above.
- The index contains one or more columns that prefix the equality column.
- The prefix columns are constrained to a set of constant values via the query filter or a
CHECKconstraint, for example via an
INoperator. In the case of a
CHECKconstraint, the columns must be
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.
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.