MySQL Bugs: #112642: Wrong results when MRR is activated

Bug #112642	Wrong results when MRR is activated
Submitted:	5 Oct 2023 18:40	Modified:	7 Oct 2023 13:08
Reporter:	Zacharias Creutznacher	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I dumped a database from a UAT stage and imported it locally.

1. Select with wrong results
Now when I run the following query:

select
  *
from
  `execution_element_progress`
where
  `execution_element_id` in (
    29412, 29413, 49662, 49663, 49664, 49665, 54520, 54522, 105839, 107267, 107268, 107269, 107270, 107279, 107284, 107285, 107286, 107303, 107304, 107305, 107306, 107307, 107308, 107309, 107310, 107320, 107334, 107427, 107515, 107688, 107689, 107690, 107691, 107692, 107695, 107802, 107803, 107804, 107811, 107812, 107813, 107849
  )

I do not get certain records, even though I checked that the execution_element_id exists. For instance the one where execution_element_id equals 107515.

2. Select with only one id
The strange thing is if I query only for this one element it works:

select
      *
    from
      `execution_element_progress`
    where
      `execution_element_id` in (
        107515
      )

3. Select with ORDER BY
Another strange thing is, that if I add an ORDER BY it also works!

select
  *
from
  `execution_element_progress`
where
  `execution_element_id` in (
    29412, 29413, 49662, 49663, 49664, 49665, 54520, 54522, 105839, 107267, 107268, 107269, 107270, 107279, 107284, 107285, 107286, 107303, 107304, 107305, 107306, 107307, 107308, 107309, 107310, 107320, 107334, 107427, 107515, 107688, 107689, 107690, 107691, 107692, 107695, 107802, 107803, 107804, 107811, 107812, 107813, 107849
  ) ORDER BY execution_element_id DESC

4. Select with MRR disabled
Now I also found out, that if I turn off MRR:

SET SESSION optimizer_switch='mrr=off';
And rerun the same query, it works:

select
  *
from
  `execution_element_progress`
where
  `execution_element_id` in (
    29412, 29413, 49662, 49663, 49664, 49665, 54520, 54522, 105839, 107267, 107268, 107269, 107270, 107279, 107284, 107285, 107286, 107303, 107304, 107305, 107306, 107307, 107308, 107309, 107310, 107320, 107334, 107427, 107515, 107688, 107689, 107690, 107691, 107692, 107695, 107802, 107803, 107804, 107811, 107812, 107813, 107849
  )

If I turn it back on, it again fails.

My local MySQL version is: mysql  Ver 8.0.33 for macos13.3 on arm64 (Homebrew)

The MySQL version on UAT is: mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

Test on another machine
To make sure it is not an issue with the dump itself, I imported it on a Windows 11 with WSL (mysql  Ver 8.0.34-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))) and tried the queries also there. Surprise, there it is working!

How to repeat:
If I turn MRR back on, it again fails.

I'm gonna add some EXPLAIN debug outputs and the database to reproduce the error in a minute...

explain debug for all the sql statements

Attachment: explain_debug.zip (application/zip, text), 11.28 KiB.

the database...

Attachment: pm_prod.dump.zip (application/zip, text), 36.22 MiB.

change category

fixed version

Hi Mr. Creutznacher,

Thank you for your bug report.

We were fully able to repeat your bug report on the latest 8.0 and 8.1.

Not only that, we also found out that adding ORDER BY execution_element_id at the end of the query also shows all rows with the value of 107517.

We have changed the severity since there are several workarounds for the query and because no data have been lost.

This is now a fully verified bug report.

Great to hear that! May I ask, if there is a slightly vague estimation, when this issue will be fixed?

Hi,

No, unfortunately there are no rough estimates for any verified bugs, except for Security Vulnerability bugs.

Each team has it's own schedule, which changes daily, so nobody can actually knows when some bug will be worked upon.