Bug #71777 Incorrect results are returned with NULLIF()
Submitted: 20 Feb 2014 9:39 Modified: 20 Feb 2014 10:46
Reporter: Saulius Gurklys Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.5.35-0ubuntu0.13.10.1 OS:Linux (ubuntu0.13.10.1)
Assigned to: CPU Architecture:Any

[20 Feb 2014 9:39] Saulius Gurklys
Description:
Hi,

Execute
-------------------------------------------------------------------

DROP TABLE IF EXISTS `e`;
CREATE TABLE `e` (
  `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scode` varchar(40) DEFAULT NULL,
  `f_dt1` date DEFAULT NULL,
  `f_dt2` date DEFAULT NULL,
  PRIMARY KEY (`rid`),
  KEY `f_dt1` (`f_dt1`),
  KEY `f_dt2` (`f_dt2`),
  KEY `scode` (`scode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `e` VALUES
(962328,'ABC1216',NULL,'2014-01-31'),
(962330,'ABC1217','2014-02-04','2014-02-04'),
(963122,'ABC1219',NULL,NULL),
(967451,'ABC1217',NULL,NULL);

DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `scode` varchar(40) DEFAULT NULL,
  `pcode` varchar(20) NOT NULL,
  PRIMARY KEY (`rid`),
  UNIQUE KEY `scode` (`scode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `s` VALUES
(300676, 'ABC1216','ABC'),
(300677, 'ABC1217','ABC'),
(300964, 'ABC1219','ABC');

SELECT s.scode,

concat(
	ifnull(nullif(e1.f_dt2,"0000-00-00"),""),
	"#",
	ifnull(nullif(e1.f_dt1,"0000-00-00"),"")
) bad,

length(
concat(
	ifnull(nullif(e1.f_dt2,"0000-00-00"),""),
	"#",
	ifnull(nullif(e1.f_dt1,"0000-00-00"),"")
)) bad_len,

concat(
	ifnull(nullif(cast(e1.f_dt2 as char),"0000-00-00"),""),
	"#",
	ifnull(nullif(cast(e1.f_dt1 as char),"0000-00-00"),"")
) good,

length(
concat(
	ifnull(nullif(cast(e1.f_dt2 as char),"0000-00-00"),""),
	"#",
	ifnull(nullif(cast(e1.f_dt1 as char),"0000-00-00"),"")
)) good_len

FROM s
LEFT JOIN e  e1  ON e1.scode=s.scode
WHERE s.pcode = 'ABC'

GROUP BY  s.scode
ORDER BY 1 DESC;

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

Result are

+---------+---------+---------+-----------------------+----------+
| scode   | bad     | bad_len | good                  | good_len |
+---------+---------+---------+-----------------------+----------+
| ABC1219 | #       |       1 | #                     |        1 |
| ABC1217 | 2014-02 |      21 | 2014-02-04#2014-02-04 |       21 |
| ABC1216 | 2014-01 |      11 | 2014-01-31#           |       11 |
+---------+---------+---------+-----------------------+----------+
3 rows in set (0.08 sec)

"bad" column, of the last SQL statement, differ from "good" column results, while "bad_len" and "good_len" are the same.

Cast is used for "good" results. No cast for "bad".

How to repeat:
1. Execute supplied SQL commands
2. See the result
[20 Feb 2014 10:46] MySQL Verification Team
Hello Saulius,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh