Bug #120675 Incorrect result for `CAST(NULL) IN col || CASE ... IN col`
Submitted: 13 Jun 17:32 Modified: 30 Jun 13:24
Reporter: mu mu Email Updates:
Status: Verified 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:32] mu mu 
Description:
On identical table data, `WHERE (CAST(NULL AS SIGNED) IN (t2.c0)) || ((CASE ... END) IN (t2.c0))` should return **0** rows, but MySQL 9.6.0 returns **1** row.

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

CREATE TABLE `t2` (
  `c0` smallint(71) unsigned zerofill NOT NULL,
  UNIQUE KEY `c0` (`c0`),
  KEY `i0` (`c0`)
) PACK_KEYS=0 CHECKSUM=1;

INSERT INTO `t2` (`c0`) VALUES (0);
INSERT INTO `t2` (`c0`) VALUES (1);
INSERT INTO `t2` (`c0`) VALUES (65535);

SELECT t2.c0 AS ref0 FROM t2 WHERE ((CAST(NULL AS SIGNED)) IN (t2.c0)) || (((CASE -619584367 WHEN '5t(+' THEN '>? 0' ELSE 0.9857883729851544 END)) IN (t2.c0));
[30 Jun 13:24] Chaithra Marsur Gopala Reddy 
Hi mu mu,

Thanks for the test case. Verified as described.