Bug #120673 Incorrect result for `NOT (literal NOT IN col)`
Submitted: 13 Jun 17:31 Modified: 22 Jun 9:07
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 (((- ((- (0.7796926659049549))))) NOT IN (t0.c0))` should return **0** rows, but MySQL 9.6.0 returns **1** row.

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

CREATE TABLE `t0` (
  `c0` tinyint DEFAULT NULL,
  KEY `i0` (`c0`) USING BTREE
);

INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (1);

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT (((- ((- (0.7796926659049549))))) NOT IN (t0.c0)));
[22 Jun 9:07] Chaithra Marsur Gopala Reddy 
Hi mu mu,

Thank you for the test case. Verfieid as described. This bug is fixed in the upcoming MySQL-9.7 LTS release as part of the fix for Bug#113302.

Thanks,
Chaithra
MySQL optimizer team