John Jove added a comment - 2025-04-11 02:34 I wonder whether MDEV-36286 and this bug suffer from the same root cause. MDEV-36286 exposes inconsistencies between LONGTEXT and TINYTEXT. Thus, I modify this case by changing their data types as LONGTEXT and TINYTEXT, respectively. However, the following results seem correct. Thus, I think these are two different bugs, not duplicates. -- case 1 CREATE TABLE t1 (c1 LONGTEXT); INSERT INTO t1 (c1) VALUES ( '55' ); INSERT INTO t1 (c1) VALUES ( '95' ); SELECT c1 AS r1 FROM t1 WHERE (AES_ENCRYPT(c1, 48) <= ALL ( SELECT c1 AS ca7 FROM t1)) IS FALSE ; -- {} -- case 1 CREATE TABLE t1 (c1 TINYTEXT); INSERT INTO t1 (c1) VALUES ( '55' ); INSERT INTO t1 (c1) VALUES ( '95' ); SELECT c1 AS r1 FROM t1 WHERE (AES_ENCRYPT(c1, 48) <= ALL ( SELECT c1 AS ca7 FROM t1)) IS FALSE ; -- {}

Neha Pagar added a comment - 5 days ago I am able to replicate the issue in 10.5 -11.8 in both debug and non debug mode. worker[01] > Started [mysqltest - pid: 150857, winpid: 150857] CREATE TABLE t1 (c1 LONGBLOB); INSERT INTO t1 (c1) VALUES ('55'); INSERT INTO t1 (c1) VALUES ('95'); EXplain extended SELECT c1 AS r1 FROM t1 WHERE (AES_ENCRYPT(c1, 48) <= ALL (SELECT c1 AS ca7 FROM t1)) IS FALSE; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `r1` from `test`.`t1` where <not>(<expr_cache><aes_encrypt(`test`.`t1`.`c1`,48)>(<in_optimizer>(aes_encrypt(`test`.`t1`.`c1`,48),<exists>(/* select#2 */ select `test`.`t1`.`c1` AS `ca7` from `test`.`t1` where trigcond(<cache>(aes_encrypt(`test`.`t1`.`c1`,48)) > `test`.`t1`.`c1` or `test`.`t1`.`c1` is null) having trigcond(`test`.`t1`.`c1` is null))))) is false drop table t1; worker[01] > Started [warnings-mysqld.1 - pid: 150859, winpid: 150859] worker[01] Got [warnings-mysqld.1 - pid: 150859, winpid: 150859, exit: 15872] worker[01] Started [check-mysqld_1 - pid: 150861, winpid: 150861] worker[01] Got [check-mysqld_1 - pid: 150861, winpid: 150861, exit: 0] worker[01] atomic.MDEV-36559_TC1 worker[01] [ pass ] 76 atomic.MDEV-36559_TC1 [ pass ] 76 worker[01] Stopping [mysqld.1 - pid: 150848, winpid: 150848] -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.076 of 12 seconds executing testcases   Completed: All 1 tests were successful. worker[01] > Started [mysqltest - pid: 150800, winpid: 150800] CREATE TABLE t1 (c1 TINYBLOB); INSERT INTO t1 (c1) VALUES ('55'); INSERT INTO t1 (c1) VALUES ('95'); Explain extended SELECT c1 AS r2 FROM t1 WHERE (AES_ENCRYPT(c1, 48) <= ALL (SELECT c1 AS ca7 FROM t1)) IS FALSE; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `r2` from `test`.`t1` where <not>(<expr_cache><aes_encrypt(`test`.`t1`.`c1`,48)>(<in_optimizer>(aes_encrypt(`test`.`t1`.`c1`,48),<exists>(/* select#2 */ select `test`.`t1`.`c1` AS `ca7` from `test`.`t1` where trigcond(<cache>(aes_encrypt(`test`.`t1`.`c1`,48)) > `test`.`t1`.`c1` or `test`.`t1`.`c1` is null) having trigcond(`test`.`t1`.`c1` is null))))) is false drop table t1; worker[01] > Started [warnings-mysqld.1 - pid: 150802, winpid: 150802] worker[01] Got [warnings-mysqld.1 - pid: 150802, winpid: 150802, exit: 15872] worker[01] Started [check-mysqld_1 - pid: 150804, winpid: 150804] worker[01] Got [check-mysqld_1 - pid: 150804, winpid: 150804, exit: 0] worker[01] atomic.MDEV-36559_TC2 worker[01] [ pass ] 92 atomic.MDEV-36559_TC2 [ pass ] 92 worker[01] Stopping [mysqld.1 - pid: 150791, winpid: 150791] -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.092 of 12 seconds executing testcases   Completed: All 1 tests were successful.