Bug #119136 Incompatible behavior between integer columns when comparing with datetime

• Submitted: 13 Oct 3:14
• Reporter: John Jove
• Status: Open
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.4.6
• OS: Any
• CPU Architecture: Any

[13 Oct 3:14] John Jove

Description:
I try to store the same value 1 into integer columns with different type, i.e., INT and BIGINT. Then, I try to compare the value with a DATETIME value. However, the server returns inconsistent outputs.

How to repeat:
-- Run the following cases.

DROP TABLE t1;
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);
SELECT (c1 = CAST(0.0 AS DATETIME)) FROM t1; -- {NULL}

DROP TABLE t1;
CREATE TABLE t1 (c1 BIGINT);
INSERT INTO t1 VALUES (1);
SELECT (c1 = CAST(0.0 AS DATETIME)) FROM t1; -- {0}