MySQL Bugs: #12176: Difference between "IS NULL" and "= NULL"

Bug #12176	Difference between "IS NULL" and "= NULL"
Submitted:	26 Jul 2005 14:42	Modified:	26 Jul 2005 15:11
Reporter:	Thomas Belot	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.7-beta	OS:	Any (*)
Assigned to:		CPU Architecture:	Any

Description:
I'm not sure if it's a bug or something I haven't understood about the use of null column, but I ran into this : 
When one uses a "= NULL" condition the results are rather strange,
but when one uses "IS NULL", the results are clean.

In the "how to repeat" below i'm trying to obtain t2.type, but only if it exists.

PS. I'm not allowed to download from mysql.com (proxy rule) so i'm not sure the bug hasn't been fixed in 5.0.9. But I've searched the bug database and found nothing

How to repeat:
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id_test` int(10) unsigned NOT NULL auto_increment,
  `type` enum('A','B','C') NOT NULL default 'A',
  PRIMARY KEY  (`id_test`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test`
VALUES (1, 'A'),(2, 'C'),(3, 'A'),(4, 'A'),(5, 'B'),
(6, 'B'),(7, 'A'),(8, 'C'),(9, 'A'),(10, 'C');

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id_test` int(10) unsigned NOT NULL default '0',
  `type` enum('A','B','C') NOT NULL default 'A',
  PRIMARY KEY  (`id_test`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test2`
VALUES (1, 'C'),(3, 'B'),(5, 'A'),(7, 'C'),(8, 'B');

SELECT t1.id_test,
  IF(t2.TYPE = NULL,t1.TYPE,t2.TYPE) AS `TYPE`,
  IF(t2.TYPE IS NULL,t1.TYPE,t2.TYPE) AS `type_bis`
FROM test t1
  LEFT JOIN test2 t2 ON t1.id_test=t2.id_test;

Thomas, 

According to sql standarts, "null" can't be compared with any other values. And only valid operation with NULL is ' is null' command.

Please read:

http://dev.mysql.com/doc/mysql/en/working-with-null.html

My apologies for the mistake.
I've searched the doc for something like this but didn't found it.