Bug #119823 drop index can lead to different results

• Submitted: 1 Feb 11:42
• Modified: 2 Feb 3:55
• 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:42] fan liu

Description:
The same SELECT statement returned different results after I dropped the index on the datatype VARCHAR;

SELECT t1.c1 FROM t1 STRAIGHT_JOIN t0 ON (t0.c0) LIKE (( EXISTS (SELECT 1 WHERE FALSE)) >= (CAST('wP''oO[]' AS SIGNED))) ; --{-1563220260}

drop index i0 on t0;
SELECT t1.c1 FROM t1 STRAIGHT_JOIN t0 ON (t0.c0) LIKE (( EXISTS (SELECT 1 WHERE FALSE)) >= (CAST('wP''oO[]' AS SIGNED))) ;  --{}

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `t0` (
`c0` varchar(500) DEFAULT NULL,
KEY `i0` (`c0`(1))
) ENGINE=MyISAM;
CREATE TABLE `t1` (
`c0` float DEFAULT NULL,
`c1` double DEFAULT NULL
) ENGINE=MyISAM ;
INSERT DELAYED INTO t0(c0) VALUES('3');
INSERT INTO t0(c0) VALUES(1379089153);
REPLACE INTO t1(c1) VALUES(-1563220260);

SELECT t1.c1 FROM t1 STRAIGHT_JOIN t0 ON (t0.c0) LIKE (( EXISTS (SELECT 1 WHERE FALSE)) >= (CAST('wP''oO[]' AS SIGNED))) ; --{-1563220260}
drop index i0 on t0;
SELECT t1.c1 FROM t1 STRAIGHT_JOIN t0 ON (t0.c0) LIKE (( EXISTS (SELECT 1 WHERE FALSE)) >= (CAST('wP''oO[]' AS SIGNED))) ;  --{}

[2 Feb 3:55] Chaithra Marsur Gopala Reddy

Hi fan liu,

Thank you for the test case. Verified as described.