Skip to main content
Publication date: June 6, 2022

​
Description

In CockroachDB v22.1.0, executing a left outer join can produce incorrect results if the right side of the join is a table that has a secondary index that includes some, but not all, columns required by the join condition. In this case, it is possible that rows from the left side of the join that do not have matches in the right side can incorrectly return some of the right side columns as non-null values. This issue can also lead to incorrect results for queries with a correlated subquery, where the table inside the subquery has a secondary index including some, but not all, columns in the WHERE clause.

​
Statement

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

​
Mitigation

Users of CockroachDB v22.1.0 are encouraged to upgrade to . To determine whether your queries may be affected by this issue on v22.1.0, examine the query plans of any queries with left joins or correlated subqueries by using EXPLAIN (OPT). If the resulting plan shows two nested left lookup joins as part of a “paired joiner”, these queries may produce incorrect results. The EXPLAIN (OPT) output for affected plans will look similar to the following plan:
To mitigate this issue on v22.1.0, ensure that tables used by queries with a left join or correlated subquery have only covering secondary indexes. That is, any secondary indexes on these tables should include (either as a key column or a STORING column) all columns from the table used in the ON or WHERE conditions. Cockroach Labs strongly recommends upgrading to v22.1.1 as soon as possible. On v22.1.1, covering indexes are not required.

​
Impact

Some queries with left outer joins or correlated subqueries can produce incorrect results if the table on the right side of the join (or the table inside the subquery, in the case of a correlated subquery) has any secondary indexes containing some, but not all, of the columns needed for the join condition. Affected versions include v22.1.0-alpha.1 to v22.1.0. Please reach out to the support team if more information or assistance is needed.