Bug #112389 Inconsistent results caused by a subquery in FROM clause

• Submitted: 19 Sep 2023 14:56
• Modified: 21 Sep 2023 9:19
• Reporter: Zuming Jiang
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0.0 - 8.0.34
• OS: Any
• CPU Architecture: Any

[19 Sep 2023 14:56] Zuming Jiang

Description:
Dear MySQL developers,

I used my new fuzzer to fuzz MySQL and found a logic bug that make MySQL server output inconsistent results:

How to repeat:
*** Set up the database ***

create table t0 (c0 int);
create table t4 (c17 int);
insert into t0  values (16);

*** Test Case 1 ***

select
  1 as c_0
from
  ((select
          case when true then ref_0.c17 else ref_0.c17 end as c_1
        from
          t4 as ref_0
        ) as subq_0
    right outer join t0 as ref_1
    on (subq_0.c_1 = ref_1.c0))
where FIELD(subq_0.c_1, null) <= (ref_1.c0);

I changed "case when true then ref_0.c17 else ref_0.c17 end" to "ref_0.c17", and got Test Case 2:

*** Test Case 2 ***

select
  1 as c_0
from
  ((select
          ref_0.c17 as c_1
        from
          t4 as ref_0
        ) as subq_0
    right outer join t0 as ref_1
    on (subq_0.c_1 = ref_1.c0))
where FIELD(subq_0.c_1, null) <= (ref_1.c0);

*** Expected results ***

Test Case 1 and Test Case 2 return the same results.

*** Actual results ***

Test Case 1 and Test Case 2 return inconsistent results.

Test Case 1 return:

+-----+
| c_0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

Test Case 2 return:

Empty set (0.00 sec)

*** Note ***

The bug can be reproduced in version 5.7, 8.0.0 - 8.0.34. In version 5.5 and 5.6, both Test Case 1 and 2 return the same results:

Empty set (0.00 sec)

[21 Sep 2023 9:19] MySQL Verification Team

Hi Mr. Jiang,

Thank you for your bug report.

We have repeated a test case and it is truly a proper bug.

We have analysed our code and your queries and concluded that this (true and veritable) bug is a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=112394

We simply do not verify multiple bugs that are caused by the same error in our code.

Hence, this is a true bug, but a duplicate of the above bug.

Duplicate.