Bug #119835 drop index can lead to different results

• Submitted: 2 Feb 14:34
• Modified: 3 Feb 13:26
• Reporter: fan liu
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[2 Feb 14:34] fan liu

Description:
SELECT ALL t0.c0,t1.c0,t1.c2 FROM t0 NATURAL JOIN t1 ; 
c0                 |c0                 |c2|
-------------------+-------------------+--+
7574176379748798164|7574176379748798164|  |

drop index i0 on t1;
SELECT ALL t0.c0,t1.c0,t1.c2 FROM t0 NATURAL JOIN t1 ;
c0                 |c0                 |c2|
-------------------+-------------------+--+
7574176379748798164|7574176379748798164|  |
7574176379748798164|7574176379748798464|  | 

How to repeat:
DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;   
CREATE TABLE `t0` (
  `c0` tinytext 
) ENGINE=MyISAM ;
CREATE TABLE `t1` (
  `c0` bigint DEFAULT NULL,
  `c2` float DEFAULT NULL,
  KEY `i0` (`c0`)
) ENGINE=MyISAM ;
REPLACE LOW_PRIORITY INTO t0(c0) VALUES("7574176379748798164");  
INSERT DELAYED INTO t1(c2, c0) VALUES(NULL, '7574176379748798164');  
INSERT DELAYED INTO t1(c2, c0) VALUES(NULL, '7574176379748798464');  

SELECT ALL t0.c0,t1.c0,t1.c2 FROM t0 NATURAL JOIN t1 ; 
drop index i0 on t1;
SELECT ALL t0.c0,t1.c0,t1.c2 FROM t0 NATURAL JOIN t1 ;

[3 Feb 13:26] Roy Lyseng

Thank you for the bug report.
This is very similar to bug#119831, thus closing as a duplicate.