Bug #103358 Differnet `not in` result for key/non-key
Submitted: 19 Apr 2021 3:17 Modified: 19 Apr 2021 6:08
Reporter: Shenghui Wu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.32, 5.7.33 OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2021 3:17] Shenghui Wu 
Description:
Differnet `not in` result for key/non-key

How to repeat:
drop table B, E;
CREATE TABLE `B` (
  `a` decimal(35,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `B` VALUES (14390408184332288000);

CREATE TABLE `E` (
  `b` decimal(5,2) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `E` VALUES (NULL);
select a not in (select b from E) from B;
+----------------------------+
| a not in (select b from E) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.000 sec)

alter table `E` drop index `b`;
select a not in (select b from E) from B;
+----------------------------+
| a not in (select b from E) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.000 sec)
[19 Apr 2021 6:03] MySQL Verification Team 
Hello Shenghui Wu,

Thank you for the report and test case.

regards,
Umesh
[19 Apr 2021 6:08] Shenghui Wu 
What is the correct result? Return `1` or `Null`?