Bug #121301 NULLIF() returns different results for a NULL literal and a NULL-valued BIGINT

• Submitted: 17 Sep 6:12
• Modified: 17 Sep 7:33
• Reporter: Hust DBTesting
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4
• OS: MacOS (15.6.1)
• CPU Architecture: ARM (Apple Silicon)

[17 Sep 6:12] Hust DBTesting

Description:
MySQL returns an incorrect result for NULLIF() when the first argument is a string literal and the second argument is a BIGINT column containing NULL.

In the test case below, NULLIF('str24', t0.c0) returns 0, although t0.c0 contains NULL. In contrast, NULLIF('str24', NULL) returns 'str24'.

How to repeat:
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
    c0 BIGINT(28)
);

INSERT INTO t0 (c0) VALUES (NULL);

SELECT
    NULLIF('str24', t0.c0) AS column_case,
    NULLIF('str24', NULL) AS literal_case
FROM t0;

Actual result:

+-------------+--------------+
| column_case | literal_case |
+-------------+--------------+
|           0 | str24        |
+-------------+--------------+

Expected result:
+-------------+--------------+
| column_case | literal_case |
+-------------+--------------+
| str24       | str24        |
+-------------+--------------+

[17 Sep 7:33] Roy Lyseng

Thank you for the bug report.
Verified as described.