Bug #119530 Incorrect results for NULLIF functions
Submitted: 9 Dec 12:38 Modified: 11 Dec 9:30
Reporter: John Jove Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.4.7 OS:Any
Assigned to: CPU Architecture:Any

[9 Dec 12:38] John Jove 
Description:
Run the following statements, which are expected to return 0000-00-00 00:00:00.

How to repeat:
CREATE TABLE t1 (c1 TINYINT UNSIGNED);
INSERT INTO t1 VALUES (18);
SELECT (nullif((CAST(0 AS DATETIME)), c1)) FROM t1; -- actual: {NULL}, expected: {0000-00-00 00:00:00}
[10 Dec 12:45] Roy Lyseng 
This is not a bug.
In default SQL mode, 0000-00-00 is not a valid date value.
You need to disable the NO_ZERO_DATE SQL mode to obtain the desired result.
[11 Dec 3:30] John Jove 
However, I run the following statements, which return 0 instead of null.

CREATE TABLE t1 (c1 BIGINT UNSIGNED);
INSERT INTO t1 VALUES (18);
SELECT (nullif((CAST(0 AS DATETIME)), c1)) FROM t1; -- {0}
[11 Dec 9:30] Roy Lyseng 
Verified - inconsistent results