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)