Publication date: November 2, 2021
Description
Zigzag joins, which may be performed when two or more columns are held constant in a query filter on a table with two or more indexes with those columns, could potentially produce incorrect results.
Incorrect results occur only when the indexes in question have multiple columns, the leading columns are held constant by the filter, and the trailing columns are the same amongst the indexes and are nullable. Incorrect results occur only when these nullable columns have null values.
CockroachDB versions 19.2, 20.1, 20.2, and 21.1 are affected by this bug. Version 20.2 is only affected until maintenance version 20.2.18. Versions 19.2 and 20.1 are no longer eligible for maintenance support, so there will be no patches for these versions.
Statement
This bug was resolved in CockroachDB by PR 71758, which corrects the bug by no longer planning zigzag joins when they produce incorrect results.
The fix has been applied to maintenance release 20.2.18. The fix will be applied to an upcoming maintenance version for 21.1. See Mitigation for the steps to avoid the issue until a patch is released.
This public issue is tracked by 71655.
Mitigation
To work around the issue, disable zigzag joins by running SET CLUSTER SETTING sql.defaults.zigzag_join.enabled = false. This may result in poorer performance in some cases.
Impact
SQL statements that used zigzag joins can return incorrect results in some cases.
Please reach out to the support team if you need more information or assistance.
Was this helpful?
