MySQL Bugs: #19635: NULL seems to neither identical or NOT identical to '' (empty string)

Bug #19635	NULL seems to neither identical or NOT identical to '' (empty string)
Submitted:	9 May 2006 12:01	Modified:	9 May 2006 14:37
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
Se below.  This: 

select * from t1 where t = ' '; -- 2 row returned (with id = 1 and 2)
select * from t1 where t <> ' '; -- empty set
select count(*) from t1; -- returns 3

is a logical mystery to me!  There is a row with the value NULL for column `t`.  This value is not IDENTICAL to '' (empty string) with a WHERE-clause and also not NOT IDENTICAL to '' (empty string).

I wonder if I am missing a dimension in my life ....

How to repeat:
create table `test`.`t1` (`id` bigint NOT NULL AUTO_INCREMENT ,`t` varchar (20)NULL, PRIMARY KEY (`id`));
insert into `t1` (`id`,`t`) values ( NULL,'');
insert into `t1` (`id`,`t`) values ( NULL,' ');
insert into `t1` (`id`,`t`) values ( '3',NULL);

select * from t1 where t = ''; -- 2 row returned (with id = 1 and 2)
select * from t1 where t <> ''; -- empty set
select * from t1 where t = ' '; -- 2 row returned (with id = 1 and 2)
select * from t1 where t <> ' '; -- empty set
select count(*) from t1; -- returns 3
select count(*) from t1 where t = '';  -- returns 2
select count(*) from t1 where t <> ''; -- returns 0

-- now where is row no. 3?
-- NULL is NOT "EQUAL" and NOT "NOT EQUAL" '' either
-- did I miss something ?
-- (!= operator and <> operator does the same.)

-- this is as I would expect: 
select count(*) from t1 where t is null; -- returns 1
select count(*) from t1 where t is not null; -- returns 2

Suggested fix:
explain .. document .. fix ... :-)

> I wonder if I am missing a dimension in my life ....

Yes, you do. ;-) The dimension of "unknown" in addition to true and false. NULL in SQL means that a value is not known. Thus also the comparison of that unknown (NULL) value to any other value (for equality or not) will have an unknown result - not true, not false, but unknown! So MySQL does the right thing here according to the standards.

Thanks for the explanation ...
I see now!

Thank you for the bug report. Already explained.