Bug #120676 Incorrect result for `COALESCE(...) && (literal IN col)`
Submitted: 13 Jun 17:33 Modified: 30 Jun 13:23
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:33] mu mu 
Description:
On identical table data, a `WHERE` clause combining `COALESCE(t0.c0, ...)` with `(0.6045404113255772) IN (t0.c0)` via `&&` should return **0** rows, but MySQL 9.6.0 returns **1** row.

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

CREATE TABLE `t0` (
  `c0` decimal(10,0) NOT NULL  COMMENT 'asdf',
  PRIMARY KEY (`c0`)
);

INSERT INTO `t0` (`c0`) VALUES ('-1333791986');
INSERT INTO `t0` (`c0`) VALUES ('-1249670347');
INSERT INTO `t0` (`c0`) VALUES ('0');
INSERT INTO `t0` (`c0`) VALUES ('1');
INSERT INTO `t0` (`c0`) VALUES ('1357901723');
INSERT INTO `t0` (`c0`) VALUES ('1747401361');

SELECT t0.c0 AS ref0 FROM t0 WHERE (+ ((COALESCE(t0.c0, -809172146, t0.c0, t0.c0)) && ((0.6045404113255772) IN (t0.c0))));
[30 Jun 13:23] Chaithra Marsur Gopala Reddy 
Hi mu mu,

This is fixed in the upcoming MySQL-9.7 LTS release. Its a duplicate of Bug#113302.

Thanks,
Chaithra