Publication date: March 6, 2023
Description
In all versions of CockroachDB from v19.1 to v22.1.15 and v22.2.0 to v22.2.5, queries that used a zigzag join could return incorrect results in some cases. Zigzag joins can be planned when at least two columns from the same table are fixed to constant values by the query predicate, and indexes exist on both of those columns. The bug could occur when the two indexes used had a matching suffix of key columns where the direction of at least one column differed between the two indexes.
For example, if a table t contained indexes INDEX (a ASC, c DESC) and INDEX (b ASC, c ASC), a query such as SELECT * FROM t WHERE a = 1 AND b = 2; could return incorrect results, since the direction of column c was DESC in one index and ASC in the other.
Statement
This is resolved in CockroachDB by #97151, which prevents the optimizer from planning zigzag joins when any columns in the matching suffix between two candidate indexes have different directions.
The fix has been applied to maintenance releases of CockroachDB v22.1.16 and v22.2.6.
This public issue is tracked by #97090.
Mitigation
Users of CockroachDB v19.1 to v22.1.15 and v22.2.0 to v22.2.5 are encouraged to upgrade to v22.1.16, v22.2.6, or a later version.
To determine whether your queries may be affected by this issue on prior versions, you should examine your query plans. If the output of EXPLAIN shows that the query is using a zigzag join, it may be affected by this issue. You can mitigate the problem by setting the session setting enable_zigzag_join to false. On v21.2 or later, you can also force the optimizer to avoid zigzag joins with a NO_ZIGZAG_JOIN hint.
The best mitigation, however, is to upgrade to v22.1.16 or v22.2.6 as soon as possible.
Impact
Some queries planned with a zigzag join could produce incorrect results if the two indexes used for the zigzag join had a matching suffix of index key columns where the direction (ASC/DESC) was different between the two indexes. Versions affected include all versions of CockroachDB v19.1 to v22.1.15 and v22.2.0 to v22.2.5.
Questions about any technical alert can be directed to our support team.
Was this helpful?
