Alice Sherepa added a comment - 2025-03-26 10:56 - edited Thanks! 11.8.1: MariaDB [test]> CREATE TABLE t1 (c1 TEXT, UNIQUE a (c1(1))) engine=innodb; Query OK, 0 rows affected (0,053 sec)   MariaDB [test]> INSERT INTO t1 (c1) VALUES ('a'); Query OK, 1 row affected (0,006 sec)   MariaDB [test]> SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 FROM t1)) IS FALSE; Empty set (0,001 sec)   MariaDB [test]> explain extended SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 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 | 1 | 100.00 | | | 2 | SUBQUERY | t1 | index_subquery | a | a | 7 | const | 1 | 100.00 | | +------+-------------+-------+----------------+---------------+------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0,004 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1   MariaDB [test]> explain extended SELECT c1 FROM t1 ignore index (a) WHERE ('abc' IN (SELECT c1 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 | 1 | 100.00 | | | 2 | SUBQUERY | t1 | index_subquery | a | a | 7 | const | 1 | 100.00 | | +------+-------------+-------+----------------+---------------+------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0,004 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` IGNORE INDEX (`a`) where 1   MariaDB [test]> alter table t1 drop index a; Query OK, 0 rows affected (0,055 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 FROM t1)) IS FALSE; +------+ | c1 | +------+ | a | +------+ 1 row in set (0,005 sec)   MariaDB [test]> explain extended SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 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 | 1 | 100.00 | | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0,002 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1 11.4: MariaDB [test]> CREATE TABLE t1 (c1 TEXT, UNIQUE a (c1(1))) engine=innodb; Query OK, 0 rows affected (0,054 sec)   MariaDB [test]> INSERT INTO t1 (c1) VALUES ('a'); Query OK, 1 row affected (0,006 sec)   MariaDB [test]> SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 FROM t1)) IS FALSE; +------+ | c1 | +------+ | a | +------+ 1 row in set (0,002 sec) MariaDB [test]> explain extended SELECT c1 FROM t1 WHERE ('abc' IN (SELECT c1 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 | 1 | 100.00 | | | 2 | SUBQUERY | t1 | ref_or_null | a | a | 4 | const | 1 | 100.00 | Using where | +------+-------------+-------+-------------+---------------+------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0,004 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where 1

John Jove added a comment - 2025-03-26 02:46 - edited I noticed a quite interesting fact that if we change the length of index to cover the data 'abc' or remove the index, a correct result is returned. -- Indexed with length 3 CREATE TABLE t1 (c1 TEXT, UNIQUE (c1(3))); INSERT INTO t1 (c1) VALUES ( 'a' ); SELECT c1 FROM t1 WHERE ( 'abc' IN ( SELECT c1 FROM t1)) IS FALSE ; -- {a} -- Remove index CREATE TABLE t1 (c1 TEXT); INSERT INTO t1 (c1) VALUES ( 'a' ); SELECT c1 FROM t1 WHERE ( 'abc' IN ( SELECT c1 FROM t1)) IS FALSE ; -- {a}