Bug #61226 Control Flow Functions IF and NULL problem

• Submitted: 19 May 2011 11:15
• Modified: 19 May 2011 17:44
• Reporter: Fatih UNAL
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.5.9
• OS: Windows
• 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.