Bug #119814 drop index can lead to incorrect results

• Submitted: 31 Jan 4:11
• Modified: 31 Jan 20:58
• Reporter: fan liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[31 Jan 4:11] fan liu

Description:
The same query returned different results after I drop the index.

DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;  
CREATE TABLE t0(c0 DECIMAL UNIQUE KEY)  ENGINE =MEMORY;  
CREATE TABLE t1(c0 VARCHAR(500))  ENGINE = InnoDB;  
INSERT INTO t0(c0) VALUES(843390797); 
INSERT INTO t0(c0) VALUES(0);  
REPLACE INTO t1(c0) VALUES('0z'); 
SELECT t0.c0, t1.c0  FROM t0 NATURAL JOIN t1;

c0|c0|
--+--+

DROP INDEX c0 on t0;
SELECT t0.c0, t1.c0  FROM t0 NATURAL JOIN t1;

c0|c0|
--+--+
 0|0z|

How to repeat:
DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;  
CREATE TABLE t0(c0 DECIMAL UNIQUE KEY)  ENGINE =MEMORY;  
CREATE TABLE t1(c0 VARCHAR(500))  ENGINE = InnoDB;  
INSERT INTO t0(c0) VALUES(843390797); 
INSERT INTO t0(c0) VALUES(0);  
REPLACE INTO t1(c0) VALUES('0z'); 
SELECT t0.c0, t1.c0  FROM t0 NATURAL JOIN t1;
drop index c0 on t0;
SELECT t0.c0, t1.c0  FROM t0 NATURAL JOIN t1;

[31 Jan 20:58] Roy Lyseng

Thank you for the bug report.
Verified as described.