Bug #120672 Incorrect result for `NOT (IFNULL(col, n) OR (col = literal))`
Submitted: 13 Jun 17:31 Modified: 15 Jun 12:50
Reporter: mu mu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:9.6.0 OS:Ubuntu
Assigned to: CPU Architecture:Any

[13 Jun 17:31] mu mu 
Description:
On identical table data, `WHERE NOT ((IFNULL(t4.c0, 246509652)) OR (NOT ((t4.c0) = (0.22810337963157334))))` should return **0** rows, but MySQL 9.6.0 returns **1** row.

How to repeat:
DROP TABLE IF EXISTS `t4`;

CREATE TABLE `t4` (
  `c0` decimal(10,0) DEFAULT NULL COMMENT 'asdf'
) STATS_PERSISTENT=0 STATS_AUTO_RECALC=1 CHECKSUM=1 DELAY_KEY_WRITE=1;

INSERT INTO `t4` (`c0`) VALUES ('-2012163698');
INSERT INTO `t4` (`c0`) VALUES (NULL);
INSERT INTO `t4` (`c0`) VALUES ('-2012163698');
INSERT INTO `t4` (`c0`) VALUES (NULL);
INSERT INTO `t4` (`c0`) VALUES ('1912558574');
INSERT INTO `t4` (`c0`) VALUES (NULL);
INSERT INTO `t4` (`c0`) VALUES ('1');
INSERT INTO `t4` (`c0`) VALUES ('0');
INSERT INTO `t4` (`c0`) VALUES (NULL);

SELECT t4.c0 AS ref0 FROM t4 WHERE (! ((IFNULL(t4.c0, 246509652)) OR ((! ((t4.c0) = (0.22810337963157334))))));
[15 Jun 12:50] Chaithra Marsur Gopala Reddy 
Hi mu mu,

Thi problems seems to be resolved on 9.7. Likely duplicate of Bug#118033.