Bug #119591 Incorrect result type for NULLIF(DOUBLE,BIGINT) function

• Submitted: 24 Dec 7:27
• Modified: 26 Dec 17:48
• Reporter: John Jove
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[24 Dec 7:27] John Jove

Description:
Run the case 1, in which the result type is BIGINT. Since the NULLIF function takes the first parameter's result type as the result type and DOUBLE should be yield. 

If we try another type MEDIUMINT like in case 2, in which the result is expected in terms of the value and the result type.

I observed the different result types from JDBC client and also reproduced the case using CREATE TABLE SELECT statement to get the resulted column type.

How to repeat:
-- case 1
DROP TABLE t1;
CREATE TABLE t1 (c1 BIGINT);
INSERT INTO t1 VALUES (8001690);
SELECT nullif(CAST(-1 AS DOUBLE), c1) FROM t1; -- {-1} BIGINT result type

-- case 2
DROP TABLE t1;
CREATE TABLE t1 (c1 MEDIUMINT);
INSERT INTO t1 VALUES (8001690);
SELECT nullif(CAST(-1 AS DOUBLE), c1) FROM t1; -- {-1.0} DOUBLE

[26 Dec 17:48] Roy Lyseng

Verified as described.