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)
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)