Bug #119832 drop index can lead to different results

• Submitted: 2 Feb 9:12
• Modified: 3 Feb 12:20
• Reporter: fan liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[2 Feb 9:12] fan liu

Description:
The same SELECT statement returned different results after I dropped the index ;

SELECT t0.c1, t1.c0 FROM t1, t0 WHERE t1.c0 = t0.c1; 
Empty set.

drop index c0 on t1;
SELECT t0.c1, t1.c0 FROM t1, t0 WHERE t1.c0 = t0.c1;
c1|c0|
--+--+
2r|2 |

How to repeat:
DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;   
CREATE TABLE `t0` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` tinytext
) ENGINE=InnoDB ;
CREATE TABLE `t1` (
  `c0` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `c0` (`c0`)
) ENGINE=InnoDB ;
INSERT INTO t0(c1, c0) VALUES ('2r', -1);  
INSERT INTO t1 VALUES (2);

SELECT t0.c1, t1.c0 FROM t1, t0 WHERE t1.c0 = t0.c1; 
drop index c0 on t1;
SELECT t0.c1, t1.c0 FROM t1, t0 WHERE t1.c0 = t0.c1;

[3 Feb 12:20] Roy Lyseng

Thank you for the bug report.
Verified as described.