Publication date: March 6, 2023
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
DESC in one index and
ASC in the other.
This public issue is tracked by #97090.
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
false. On v21.2 or later, you can also force the optimizer to avoid zigzag joins with a
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 (
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.