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
This is resolved in CockroachDB by PR #82054.
The fix has been applied to the maintenance release of CockroachDB v22.1.1.
This issue is tracked by #81968.
Mitigation
Users of CockroachDB v22.1.0 are encouraged to upgrade to v22.1.1.
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:
└── left-join (lookup table2 [as=t2])
├── lookup columns are key
├── second join in paired joiner
├── left-join (lookup table2@table2_idx [as=t2])
│ ├── first join in paired joiner; continuation column: continuation
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.
Was this helpful?
