| Bug #118053 | cast((NULLIF) as datetime) returns incorrect result | ||
|---|---|---|---|
| Submitted: | 24 Apr 2025 15:47 | Modified: | 13 Aug 16:33 |
| Reporter: | Chunling Qin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.4.3, 9.3.0, 8.4.5, 8.0.42 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[25 Apr 2025 8:16]
MySQL Verification Team
Hello Chunling Qin, Thank you for the report and test case. Verified as described. regards, Umesh
[19 May 2025 2:25]
ximin liang
pull request: https://github.com/mysql/mysql-server/pull/612
[22 May 2025 13:48]
OCA Admin
Contribution submitted via Github - add get_date and get_time for null_if func (*) Contribution by ximin liang (Github liangximin2046, mysql-server/pull/612#issuecomment-2889746918): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: git_patch_2527369752.txt (text/plain), 3.51 KiB.
[28 May 1:13]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[13 Aug 16:33]
Edward Gilmore
Posted by developer:
Added the following note to the MySQL Server 9.7.2 and 26.7.0 release notes:
CAST(NULLIF(...) AS DATETIME) could return an incorrect result
because temporal values produced by NULLIF() were not evaluated
with the appropriate temporal type handling.
Temporal evaluation is now handled correctly for IF() and
NULLIF() expressions. As a result, NULLIF() applied to a YEAR
expression now preserves the YEAR data type; related aggregate
expressions may therefore return DECIMAL rather than DOUBLE.
Our thanks to Ximin Liang for the contribution.
Description: The following SQL returns a NULL value in MySQL, whereas in Oracle, TiDB, and PostgreSQL, it returns '2018-07-16 10:24:37.022727'. MySQL [test2]> select cast((NULLIF('2018-07-16 10:24:37.022727', '2023-01-01 00:00:00' )) as datetime(6)) c0 from dual ;+------+ | c0 | +------+ | NULL | +------+ 1 row in set, 2 warnings (0.00 sec) MySQL [test2]> select NULLIF('2018-07-16 10:24:37.022727', '2023-01-01 00:00:00' ) from dual; +---------------------------------------------------------------+ | NULLIF('2018-07-16 10:24:37.022727', '2023-01-01 00:00:00' ) | +---------------------------------------------------------------+ | 2018-07-16 10:24:37.022727 | +---------------------------------------------------------------+ 1 row in set (0.00 sec) MySQL [test2]> MySQL [test2]> select cast('2018-07-16 10:24:37.022727' as datetime(6)) c0 from dual ; +----------------------------+ | c0 | +----------------------------+ | 2018-07-16 10:24:37.022727 | +----------------------------+ 1 row in set (0.00 sec) How to repeat: select cast((NULLIF('2018-07-16 10:24:37.022727', '2023-01-01 00:00:00' )) as datetime(6)) c0 from dual ;