Bug #24134 deal the query against to NULL value and the blank string by different way
Submitted: 9 Nov 2006 10:03 Modified: 10 Nov 2006 12:24
Reporter: ming lu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.19 OS:Windows (windows)
Assigned to: CPU Architecture:Any

[9 Nov 2006 10:03] ming lu
Description:
Server deal the query against to  NULL value and the blank string by different way. The fields with blank string value could be queried out, but the NULL value could not. But when the query has a 'group by' sub clause to the NULL field, server seemed to do the query with the NUll value.

See the 'How to repeat'. 

How to repeat:
create schema mytest;
use mytest;

CREATE TABLE `t1` (
  `jd` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  PRIMARY KEY  (`jd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t1 values(1,'name1');
insert into t1 values(2,'name2');
insert into t1 values(3,'');
insert into t1 values(4,'');
insert into t1 values(5,NULL);
insert into t1 values(6,NULL);

select * from t1 where name='';
+----+------+
| jd | name |
+----+------+
| 3  |      |
| 4  |      |
+----+------+

select * from t1 where name='NULL';
Empty set (0.00 sec)

select * from t1 group by name;
+----+-------+
| jd | name  |
+----+-------+
| 5  |       |
| 3  |       |
| 1  | name1 |
| 2  | name2 |
+----+-------+
[9 Nov 2006 10:49] MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

mysql> select * from t1 where name IS NULL;
+----+------+
| jd | name |
+----+------+
|  5 | NULL |
|  6 | NULL |
+----+------+
2 rows in set (0.00 sec)
[10 Nov 2006 8:17] ming lu
select * from t1 where name<>''
+----+-------+
| jd | name  |
+----+-------+
| 1  | name1 |
| 2  | name2 |
+----+-------+

Why don't the result include the NULL value like this:
+----+-------+
| jd | name  |
+----+-------+
| 1  | name1 |
| 2  | name2 |
| 5  | NULL  |
| 6  | NULL  |
+----+-------+
[10 Nov 2006 12:24] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.