Bug #119824 drop index can lead to incorrect results

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

[1 Feb 11:59] 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; --{}
drop index c0 on t1;
SELECT t0.c1, t1.c0 FROM t1, t0 WHERE t1.c0 = t0.c1; --{0*,0}

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

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;

[2 Feb 3:57] Chaithra Marsur Gopala Reddy

Hi fan liu,

Thank you for the test case. Verified as described.