| Bug #112524 | Inconsistent results of UNIX_TIMESTAMP with NULLIF and COALESCE | ||
|---|---|---|---|
| Submitted: | 26 Sep 2023 13:32 | Modified: | 27 Sep 2023 7:46 |
| Reporter: | Zuming Jiang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.7, 8.0.0, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Sep 2023 13:39]
Zuming Jiang
There are typos in the reports. The following is the correct version: *** Actual results *** Test Case 1 and Test Case 2 return inconsistent results. Test Case 1 return: +------+ | c_6 | +------+ | NULL | +------+ 1 row in set, 1 warning (0.00 sec) Test Case 2 return: +------+ | c_6 | +------+ | 0 | +------+ 1 row in set, 1 warning (0.00 sec) *** 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: +------+ | c_6 | +------+ | 0 | +------+ 1 row in set, 1 warning (0.00 sec)
[27 Sep 2023 7:46]
MySQL Verification Team
Hello Zuming Jiang, Thank you for the report and test case. regards, Umesh
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: *** Test Case 1 *** select UNIX_TIMESTAMP(case when true then (nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)) else (nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)) end) as c_6; Because the true branch and the false branch are the same in "case when true then (nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)) else (nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)) end", I changed it to "nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)" and got Test Case 2: *** Test Case 2 *** select UNIX_TIMESTAMP(nullif(coalesce(nullif(TIMESTAMP('2090-9-4', '7:6:19'), null), null), null)) as c_6; *** 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_6 | +------+ | 0 | +------+ 1 row in set, 1 warning (0.00 sec) Test Case 2 return: +------+ | c_6 | +------+ | NULL | +------+ 1 row in set, 1 warning (0.00 sec) *** Note *** The bug can be reproduced in version 8.0.33 and 8.0.34. In version 8.0.0 - 8.0.33, both Test Case 1 and 2 return the same results: +------+ | c_6 | +------+ | 0 | +------+ 1 row in set, 1 warning (0.00 sec)