# Technical Advisory 97090

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.