Bug #119831 drop index can lead to different results

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

[2 Feb 8:32] fan liu

Description:
SELECT t2.c0,t0.c0 FROM t0 NATURAL JOIN t2 ;
Empty set.

drop index c0 on t0;  
SELECT t2.c0,t0.c0 FROM t0 NATURAL JOIN t2 ; 
c0                |c0                |
------------------+------------------+
453589975569587960|453589975569587968|

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `t0` (`c0` bigint , UNIQUE KEY `c0` (`c0`)) ;
CREATE TABLE `t2` (`c0` text ,`c1` tinytext );
INSERT INTO t0(c0) VALUES(NULL);  
REPLACE  INTO t2(c0, c1) VALUES("453589975569587960", -1);  
INSERT INTO t0(c0) VALUES(4.5358997556958797E17);  

SELECT t2.c0,t0.c0 FROM t0 NATURAL JOIN t2 ;  
drop index c0 on t0;  
SELECT t2.c0,t0.c0 FROM t0 NATURAL JOIN t2 ;

[3 Feb 12:55] Roy Lyseng

Thank you for the bug report.
Verified as described.