Bug #120678 Incorrect result for `NOT IN (LEAST(NULL, ...)) IS UNKNOWN`
Submitted: 13 Jun 17:34 Modified: 30 Jun 13:47
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:34] mu mu 
Description:
On identical table data, `WHERE ((t0.c1) NOT IN ((+ (LEAST(NULL, 236618698))), NULL)) IS UNKNOWN` should return **1** row, but MySQL 9.6.0 returns **0** rows.

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

CREATE TABLE `t0` (
  `c1` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`),
  UNIQUE KEY `i0` ((cast(NULL as signed))) USING BTREE 
) STATS_PERSISTENT=1 STATS_AUTO_RECALC=0;

INSERT INTO `t0` (`c1`) VALUES ('0');

SELECT ALL t0.c1 AS ref0 FROM t0 WHERE ((t0.c1) NOT IN ((+ (LEAST(NULL, 236618698))), NULL)) IS UNKNOWN;
[30 Jun 13:47] Chaithra Marsur Gopala Reddy 
Hi mu mu,

This is fixed in MySQL-9.7.0 release. It is duplicate of Bug#119650.

Thanks,
Chaithra