Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows

Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.

When I perform a query like
SELECT * FROM A a
LEFT JOIN B b ON a.id b.id
WHERE a.idx1 = 'X' AND a.idx2 = 'Y';


It will return nothing... BUT, if I add a precise condition like:
SELECT * FROM A a
LEFT JOIN B b ON a.id b.id
WHERE a.idx1 = 'X' AND a.idx2 = 'Y' AND a.id = 1337;


It will find the row with the specified value as PRIMARY KEY.

An explain show that MySQL will use 'index_merge_intersection' optimization when it doesn't find anything. If we disable the 'index_merge' optimizations, the first query will return all results as expected.

Why?

It's actually a MySQL 8.0 bug (reported for MySQL 8.0.27-debug / 8.0.28), but also existing before (8.0.23) and seems to also exist on fork (MariaDB/Percona/AWS Aurora/...).

You can check if the bug get eventually fixed here: https://bugs.mysql.com/bug.php?id=106207

Before this, you can mitigate the problem by using one of these solutions:
Do NOT use DESC ordering on your PRIMARY KEYS, only ASC

(This can also avoid the duplication of an INDEX appearing with a FOREIGN_KEY due to MySQL not being able to understand that the FK can use the DESC PK)

OR
Disable the index_merge optimization from the optimizer_switch settings

By using the following query:
SET optimizer_switch='index_merge=off,index_merge_intersection=off';


(or by changing default/global value of optimizer_switch)
So MySQL will not be able to fall into this bug.

StackExchange Database Administrators Q#315687, answer score: 6