Bug #119816 Create index can lead to incorrect results
Submitted: 31 Jan 7:56 Modified: 31 Jan 21:19
Reporter: fan liu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.44 OS:Any
Assigned to: CPU Architecture:Any

[31 Jan 7:56] fan liu 
Description:
The same query returned different results after I created the index.

SELECT ALL t0.c0 FROM t0 NATURAL JOIN t1 ;--{}
create index i0 on t1(c0);
SELECT ALL t0.c0 FROM t0 NATURAL JOIN t1 ;--{0}

How to repeat:
DROP DATABASE IF EXISTS test;  
CREATE DATABASE  test;  
USE test;   
CREATE TABLE `t0` (`c0` varchar(500));
CREATE TABLE `t1` (`c0` decimal(10,0));  
REPLACE INTO t0(c0) VALUES('0NbeO]');  
REPLACE INTO t1(c0) VALUES(0);  
SELECT ALL t0.c0 FROM t0 NATURAL JOIN t1 ; 
create index i0 on t1(c0);
SELECT ALL t0.c0 FROM t0 NATURAL JOIN t1 ;
[31 Jan 21:19] Roy Lyseng 
Thank you for the bug report.
Verified as described.