Bug #120676 Incorrect result for `COALESCE(...) && (literal IN col)`

• Submitted: 13 Jun 17:33
• Reporter: mu mu
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Ubuntu
• 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))));