Since we upgraded from MariaDB 10.3.16 to MariaDB 10.4.6/10.4.8 we encounter an issue with one of our criteria queries.
After searching for what the cause might be we found out that it was due to an optimizer_switch, when deactivating it everything worked fine. The optimizer_switch causing the issue is 'mrr=on'.
After spending some time on it we were able to create a simpler database with 2 tables and fake data to replicate the issue. When doing this we noticed that the amount of rows in both databases seems to be of some relevance, when having too few row the query works but otherwise not. We could be wrong about this but it's just something we noticed.
In attachment I added the query that fails and the queries that can create a dataset to test it with.
- Activate MariaDB server (version 10.4.6 - 10.4.8) and enable the optimizer_switch 'mrr=on'
- Create the database containing the 2 tables and the data
- Execute the query
Doing this will produce the following error:
/* SQL Error (1032): Can't find record in 't2' */
/* Affected rows: 0 Found rows: 2 Warnings: 0 Duration for 1 query: 0.015 sec. */
If you need any more information just ask and I'll see what we can do.