MySQL Bugs: #101796: Incorrect query result for index

Bug #101796	Incorrect query result for index_subquery type IN subquery
Submitted:	30 Nov 2020 8:52	Modified:	30 Nov 2020 8:58
Reporter:	z xb	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.34, 5.6.50, 5.7.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	dependent subquery, Full scan on NULL key, index_subquery, Using index

Description:
An IN subquery that is executed via index_subquery access method produces wrong result 

How to repeat:

DROP TABLE IF EXISTS z_newcreate_tmp;
CREATE TABLE `z_newcreate_tmp` (
  `state` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO z_newcreate_tmp 
	SELECT '99645' state UNION  ALL
	SELECT 'Alaska' state;

DROP TABLE IF EXISTS z_newcreate_tmp1;
CREATE TABLE `z_newcreate_tmp1` (
  `abbreviation` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO z_newcreate_tmp1(abbreviation) 
	SELECT 'AL';
ALTER TABLE z_newcreate_tmp1 ADD INDEX(abbreviation);

SELECT * FROM z_newcreate_tmp WHERE state NOT IN(SELECT abbreviation FROM z_newcreate_tmp1);

'Alaska' is not in the result

Hello!

Thank you for the report and feedback.
Observed that 5.6.50 and 5.7.32 are affected(no issues with 8.0.22).

regards,
Umesh