Bug #5595 NULLIF() IS NULL returns false if NULLIF() returns NULL

• Submitted: 15 Sep 2004 17:30
• Modified: 18 Sep 2004 9:58
• Reporter: [ name withheld ]
• Status: Closed
• Category: Server
• Severity: S2 (Serious)
• Version: 4.0.20, 5.0.1
• OS: Microsoft Windows (Win XP, Win 2000/Linux)
• Assigned to: Sergey Gluhov

[15 Sep 2004 17:30] [ name withheld ]

Description:
If NULLIF() returns NULL the expression NULLIF() IS NULL returns false and the expression
NULLIF() IS NOT NULL returns true.

Consider the following examples:

mysql> SELECT NULLIF(5,5), NULLIF(5,5) IS NULL, NULL IS NULL;
+-------------+---------------------+--------------+
| NULLIF(5,5) | NULLIF(5,5) IS NULL | NULL IS NULL |
+-------------+---------------------+--------------+
|        NULL |                   0 |            1 |
+-------------+---------------------+--------------+

mysql> SELECT NULLIF(5,5), NULLIF(5,5) IS NOT NULL, NULL IS NOT NULL;
+-------------+-------------------------+------------------+
| NULLIF(5,5) | NULLIF(5,5) IS NOT NULL | NULL IS NOT NULL |
+-------------+-------------------------+------------------+
|        NULL |                       1 |                0 |
+-------------+-------------------------+------------------+

How to repeat:
Enter the following query in the MySQL client:

SELECT NULLIF(5,5), NULLIF(5,5) IS NULL, NULL IS NULL;

Suggested fix:
Use the following workaround until the bug is fixed: IFNULL(NULLIF(),NULL).

[16 Sep 2004 19:16] Miguel Solorzano

Verified against latest 4.0 BK source.

[17 Sep 2004 14:15] Sergey Gluhov

ChangeSet
  1.2022 04/09/17
  Fix for bug #5595:NULLIF() IS NULL returns false if NULLIF() returns NULL