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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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)