Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20851

optimizer_switch 'mrr=on' causes failing queries



    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.4.6, 10.4.8, 10.4
    • N/A
    • Optimizer
    • Linux (CentOS), Windows (10)


      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.

      Repro steps:

      1. Activate MariaDB server (version 10.4.6 - 10.4.8) and enable the optimizer_switch 'mrr=on'
      2. Create the database containing the 2 tables and the data
      3. Execute the query

      Doing this will produce the following error:
      /* SQL Error (1032): Can't find record in 't2' */

      Expected result:
      /* 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.


        Issue Links



              igor Igor Babaev
              JensVanDeynse Jens Van Deynse
              2 Vote for this issue
              5 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.