Bug #106155 subquery return wrong record when use expression index
Submitted: 13 Jan 2022 7:19 Modified: 13 Jan 2022 8:12
Reporter: yu zhao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql Ver 8.0.27 for Linux on x86_64 (M OS:Any
Assigned to: CPU Architecture:Any

[13 Jan 2022 7:19] yu zhao
Description:
create table use expression index use function reverse, subquery use all return error result

How to repeat:
CREATE TABLE `t1` (
  `c_int` int(11) NOT NULL,
  `c_str` varchar(40) NOT NULL,
  `c_datetime` datetime DEFAULT NULL,
  `c_timestamp` timestamp NULL DEFAULT NULL,
  `c_double` double DEFAULT NULL,
  `c_decimal` decimal(12,6) DEFAULT NULL,
  `c_enum` enum('blue','green','red','yellow','white','orange','purple') DEFAULT NULL,
  PRIMARY KEY (`c_int`,`c_str`) nonclustered,
  KEY `c_datetime` (`c_datetime`),
  KEY `expression_index_8` ((reverse(`c_str`)))
);
CREATE TABLE `t2` (
  `c_int` int(11) NOT NULL,
  `c_str` varchar(40) DEFAULT NULL,
  `c_datetime` datetime DEFAULT NULL,
  `c_timestamp` timestamp NULL DEFAULT NULL,
  `c_double` double DEFAULT NULL,
  `c_decimal` decimal(12,6) DEFAULT NULL,
  `c_enum` enum('blue','green','red','yellow','white','orange','purple') DEFAULT NULL,
  PRIMARY KEY (`c_int`) /*T![clustered_index] CLUSTERED */,
  KEY `c_int_2` (`c_int`),
  UNIQUE KEY `c_str` (`c_str`),
  UNIQUE KEY `c_datetime` (`c_datetime`),
  KEY `expression_index_8` ((reverse(`c_str`)))
);
insert into t1 values(11,'recursing shaw'       ,'2020-06-06 09:29:20','2020-03-08 19:56:58',59.280809,5.396000,'red'   );
insert into t2 values(12,'pensive aryabhata'   ,'2020-06-10 04:04:29','2020-03-22 22:32:19',36.836614,6.521000,'red'   );
select * from t1 where c_str > any (select c_str from t2 where t1.c_int = t2.c_int or t1.c_int < t2.c_int or t1.c_int = 4) ;
select should return:
mysql> select * from t1 where c_str > any (select c_str from t2 where t1.c_int = t2.c_int or t1.c_int < t2.c_int or t1.c_int = 4) ;
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
| c_int | c_str          | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum |
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
|    11 | recursing shaw | 2020-06-06 09:29:20 | 2020-03-08 19:56:58 | 59.280809 |  5.396000 | red    |
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
1 row in set (0.01 sec)

but return:
mysql> select * from t1 where c_str > any (select c_str from t2 where t1.c_int = t2.c_int or t1.c_int < t2.c_int or t1.c_int = 4) ;
Empty set (0.01 sec)
[13 Jan 2022 8:12] MySQL Verification Team
Hello yu zhao,

Thank you for the report and test case.
Observed that 8.0.27 build is affected.

regards,
Umesh