| Bug #118053 | cast((NULLIF) as datetime) returns incorrect result | ||
|---|---|---|---|
| Submitted: | 24 Apr 15:47 | Modified: | 25 Apr 8:16 |
| Reporter: | Chunling Qin | Email Updates: | |
| Status: | Verified | 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 8:16]
MySQL Verification Team
Hello Chunling Qin, Thank you for the report and test case. Verified as described. regards, Umesh
[19 May 2:25]
ximin liang
pull request: https://github.com/mysql/mysql-server/pull/612
[22 May 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.
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 ;