Bug #119819 drop index can lead to different results
Submitted: 31 Jan 10:29 Modified: 31 Jan 21:41
Reporter: fan liu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.44 OS:Any
Assigned to: CPU Architecture:Any

[31 Jan 10:29] fan liu 
Description:
The same query returned different results after I drop the index.

SELECT * FROM t0, t2 WHERE ((t2.c1) IN (t0.c0));
c0|c1        |c0|c1|
--+----------+--+--+
  |-243113943|  | 0|

drop index c1 on t2;
SELECT * FROM t0, t2 WHERE ((t2.c1) IN (t0.c0));
c0|c1        |c0|c1|
--+----------+--+--+
 0|          |  | 0|
  |-243113943|  | 0|

How to repeat:
DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;
CREATE TABLE `t0` ( `c0` mediumtext ,`c1` decimal(10,0) 
  ) ENGINE=MyISAM;
CREATE TABLE `t2` (
  `c0` mediumtext ,
  `c1` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=MyISAM;
INSERT IGNORE INTO t2(c0, c1) VALUES(NULL, 'EEkå“¡');
INSERT IGNORE INTO t2(c0) VALUES(-243113943);
INSERT  IGNORE INTO t2(c1) VALUES(NULL);  
INSERT  IGNORE INTO t0(c0, c1) VALUES("", -2.43113943E8);
REPLACE  INTO t0(c0) VALUES(-84623130);  
REPLACE  INTO t0(c0) VALUES ('0*');
SELECT * FROM t0, t2 WHERE ((t2.c1) IN (t0.c0));
drop index c1 on t2;
SELECT * FROM t0, t2 WHERE ((t2.c1) IN (t0.c0));
[31 Jan 21:41] Roy Lyseng 
Thank you for the bug report.
Verified as described.