Bug #112394 Inconsistent results caused by subqueries in FROM and EXISTS
Submitted: 20 Sep 2023 13:13 Modified: 21 Sep 2023 8:58
Reporter: Zuming Jiang 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

[20 Sep 2023 13:13] 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 (vkey int);
insert into t0 (vkey) values (5);

*** Test Case 1 ***

select 1 as c0
  from
    ((select
            case when true then 0 else 0 end as c_0
          from
            t0 as ref_5
          ) as subq_2
      right outer join t0 as ref_6
      on (subq_2.c_0 = ref_6.vkey ))
  where exists (
      select 
          1 
        from 
          t0 as ref_9
        where (subq_2.c_0 <> ref_9.vkey)); 

I changed "case when true then 0 else 0" to "0", and got Test Case 2:

*** Test Case 2 ***

select 1 as c0
  from
    ((select
            0 as c_0
          from
            t0 as ref_5
          ) as subq_2
      right outer join t0 as ref_6
      on (subq_2.c_0 = ref_6.vkey ))
  where exists (
      select 
          1 
        from 
          t0 as ref_9
        where (subq_2.c_0 <> ref_9.vkey)); 

*** 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:

+----+
| c0 |
+----+
|  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 8.0.16 - 8.0.34. In version 8.0.0 - 8.0.15, both Test Case 1 and 2 return the same results:

+----+
| c0 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
[21 Sep 2023 8:58] MySQL Verification Team
HI Mr. Jiang,

Thank you for your bug report.

We have ran your test case and we agree with you that this is a bug.

Verified as reported.