Bug #61226 Control Flow Functions IF and NULL problem
Submitted: 19 May 2011 11:15 Modified: 19 May 2011 17:44
Reporter: Fatih UNAL Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.5.9 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: IF AND NULL PROBLEM

[19 May 2011 11:15] Fatih UNAL
Description:
# Server version 5.5.9-log

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin5;

INSERT INTO `test` VALUES (1,NULL);
INSERT INTO `test` VALUES (2,'content');

-----------------------------------

SELECT 
IFNULL(t.text,'NULL'), 
NULLIF(t.text,'NULL'),
IF(t.text=NULL,t.text,'NULL'),
IF(t.text!=NULL,t.text,'NULL'),
IF(CAST(t.text AS CHAR)=NULL,t.text,'NULL'),
IF(CAST(t.text AS CHAR)!=NULL,t.text,'NULL'),
(CASE WHEN t.text=NULL THEN 'true' ELSE 'false' END),
(CASE WHEN t.text!=NULL THEN 'true' ELSE 'false' END)
FROM test t

-----------------------------------

IF = NULL functions doesnt working.
You can be test it.

How to repeat:
All times.
[19 May 2011 12:29] Valeriy Kravchuk
Doesn't this manual page, http://dev.mysql.com/doc/refman/5.5/en/problems-with-null.html, explain the results you get?
[19 May 2011 15:03] Fatih UNAL
That was wrote; "If you want to search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression" ...
[19 May 2011 15:52] Valeriy Kravchuk
Try to use "NULL-safe comparison":

to use IF(a<=>NULL,1,0)
[19 May 2011 17:05] Fatih UNAL
I was shocked. "NULL-safe comparison" sounds good.

IF(t.text<=>NULL,'NULL',t.text) 

It was working.

I learned itsnt a bug.
Thans for all.