Description:
Dear MySQL developers,
I used my fuzzer to test MySQL and found a logic bug that made the MySQL server output inconsistent results.
How to repeat:
*** Set up the database ***
create table t2 (c14 double, c16 double);
insert into t2 values (38.64, -0.0);
insert into t2 values (null, -58.49);
*** Test Case 1 ***
select * from t2
where not (
((((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14))
and
(not ((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14))))
and
(((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) is not null))
or (5 < 0));
Because a boolean value can be only either TRUE, FALSE, or NULL, ((boolean_value and (not boolean_value)) and (boolean_value is not null)) must be FALSE.
Therefore, I simplified "((((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) and (not ((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)))) and (((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) is not null))" to "FALSE", and got Test Case 2:
*** Test Case 2 ***
select * from t2
where not (FALSE
or (5 < 0));
*** 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:
+-------+------+
| c14 | c16 |
+-------+------+
| 38.64 | 0 |
+-------+------+
1 row in set (0.00 sec)
Test Case 2 return:
+-------+--------+
| c14 | c16 |
+-------+--------+
| 38.64 | 0 |
| NULL | -58.49 |
+-------+--------+
2 rows in set (0.00 sec)
*** Note ***
The bug can be reproduced in version 8.0.14 - 8.0.34. In version 5.5, 5.6, 5.7, 8.0.0 - 8.0.13, both Test Case 1 and 2 return the same results:
+-------+--------+
| c14 | c16 |
+-------+--------+
| 38.64 | 0 |
| NULL | -58.49 |
+-------+--------+
2 rows in set (0.00 sec)
Description: Dear MySQL developers, I used my fuzzer to test MySQL and found a logic bug that made the MySQL server output inconsistent results. How to repeat: *** Set up the database *** create table t2 (c14 double, c16 double); insert into t2 values (38.64, -0.0); insert into t2 values (null, -58.49); *** Test Case 1 *** select * from t2 where not ( ((((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) and (not ((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)))) and (((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) is not null)) or (5 < 0)); Because a boolean value can be only either TRUE, FALSE, or NULL, ((boolean_value and (not boolean_value)) and (boolean_value is not null)) must be FALSE. Therefore, I simplified "((((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) and (not ((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)))) and (((t2.c14 <=> nullif(-92.48, t2.c16)) or (t2.c16 <= t2.c14)) is not null))" to "FALSE", and got Test Case 2: *** Test Case 2 *** select * from t2 where not (FALSE or (5 < 0)); *** 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: +-------+------+ | c14 | c16 | +-------+------+ | 38.64 | 0 | +-------+------+ 1 row in set (0.00 sec) Test Case 2 return: +-------+--------+ | c14 | c16 | +-------+--------+ | 38.64 | 0 | | NULL | -58.49 | +-------+--------+ 2 rows in set (0.00 sec) *** Note *** The bug can be reproduced in version 8.0.14 - 8.0.34. In version 5.5, 5.6, 5.7, 8.0.0 - 8.0.13, both Test Case 1 and 2 return the same results: +-------+--------+ | c14 | c16 | +-------+--------+ | 38.64 | 0 | | NULL | -58.49 | +-------+--------+ 2 rows in set (0.00 sec)