Bug #292 Search on NULL value returns 0 results

• Submitted: 15 Apr 2003 10:20
• Modified: 15 Apr 2003 10:29
• Reporter: Ryan R
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.12
• OS: Linux (linux glibc 2.2)
• CPU Architecture: Any

[15 Apr 2003 10:20] Ryan R

Description:
Searching for a NULL value returns 0 results, even when record exists:
mysql> select firstname,lastname from employees where uid='2250';
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| NULL      | NULL     |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from employees where firstname=NULL
    -> ;
Empty set (0.01 sec)

mysql> select * from employees where firstname='NULL';
Empty set (0.00 sec)

How to repeat:
Search for NULL value in the server.

[15 Apr 2003 10:29] MySQL Verification Team

All comparisons with NULL always return NULL and never 
true nor false.

For comparison with NULL's only IS NULL and / or IS NOT NULL may be used.