Bug #112387 Inconsistent results caused by "UNION ALL" with empty subqueries

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

[19 Sep 2023 12:53] 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 (c4 double);
create table t1 (c8 varchar(200));
insert into t1 values (null);
insert into t0 values (-66.49);
insert into t0 values (null);

*** Test Case 1 ***

select * from t0
where
not (null in (
    select 1 from (select 1) as subq where false
    union all
    select
        ref_5.c8 as c_0
      from
        t1 as ref_5
      where t0.c4 >= t0.c4));

Because "select 1 from (select 1) as subq where false" produce an empty set, anything union all empty set is itself. Therefore, I remove "select 1 from (select 1) as subq where false union all", and get the Test Case 2:

*** Test Case 2 ***

select * from t0
where
not (null in (
    select
        ref_5.c8 as c_0
      from
        t1 as ref_5
      where t0.c4 >= t0.c4));

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

Empty set (0.00 sec)

Test Case 2 return:

+------+
| c4   |
+------+
| NULL |
+------+

*** Note ***

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

+------+
| c4   |
+------+
| NULL |
+------+

[19 Sep 2023 14:51] Zuming Jiang

change severity to S2

[21 Sep 2023 9:16] 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.