Bug #5218 No Row if comparing varchar and NULL

• Submitted: 25 Aug 2004 23:58
• Modified: 26 Aug 2004 7:42
• Reporter: Jean-Sébastien Goupil
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.1.3-beta-standard
• OS: Linux (Linux Redhat 9)
• CPU Architecture: Any

[25 Aug 2004 23:58] Jean-Sébastien Goupil

Description:
When I do a SELECT on a table that contains a VARCHAR that can be NULL there is a problem !

I do it with PHP and it returns no row when I do a WHERE login!=NULL or login=NULL

How to repeat:
CREATE TABLE `ls_warnuser` (
  `login` varchar(8) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

-- 
-- Contenu de la table `ls_warnuser`
-- 

INSERT INTO `ls_warnuser` VALUES ('look1002');
INSERT INTO `ls_warnuser` VALUES (NULL);

<?php
$result = mysqli_query($db,"SELECT * FROM ls_warnuser WHERE login!=NULL");

$row = mysqli_fetch_row($result);
print_r($row);
?>

Suggested fix:
Supposed to work :P

[26 Aug 2004 7:42] MySQL Verification Team

mysql> CREATE TABLE `ls_warnuser` (
    ->   `login` varchar(8) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO `ls_warnuser` VALUES ('look1002');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `ls_warnuser` VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ls_warnuser WHERE login is not null;
+----------+
| login    |
+----------+
| look1002 |
+----------+
1 row in set (0.04 sec)

Please read the Manual regarding null comparison in mysqld.