Description:
After creating a unique BTREE index that includes a prefix of a LONGTEXT column, MySQL returns incorrect results for a query with a LIKE '1' predicate.
In the test case, table t2 contains a row where c0 = '1134087631'. According to SQL semantics, c0 LIKE '1' should not match this value, because the pattern '1' contains no wildcard characters and should only match the exact string '1'. Therefore, before the index is created, the query correctly returns an empty result set.
However, after executing the following index creation statement:
CREATE UNIQUE INDEX i0 USING BTREE ON t2(c1 ASC, c0(1));
the same query:
SELECT DISTINCT t0.c1 FROM t0, t1, t2 WHERE t2.c0 LIKE '1';
c1 |
----------+
452243454|
Creating an index should not change the semantics or the result set of a query. This suggests that MySQL may incorrectly use the c0(1) prefix index when optimizing the LIKE '1' predicate, treating it as a prefix match and producing a wrong-result bug.
In addition, the direct query:
SELECT * FROM t2 WHERE t2.c0 LIKE '1';
still returns an empty result set, which further demonstrates an inconsistency in the indexed join query result.
How to repeat:
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
CREATE TABLE t0(c0 LONGTEXT , c1 DECIMAL ) ENGINE = MyISAM;
CREATE TABLE t1 LIKE t0;
CREATE TABLE t2 LIKE t1;
REPLACE INTO t1(c1, c0) VALUES(985046707, NULL);
INSERT INTO t2(c1, c0) VALUES(NULL, '');
INSERT IGNORE INTO t2(c1, c0) VALUES(-1057375426, 1134087631);
REPLACE LOW_PRIORITY INTO t1(c1, c0) VALUES(355996120, '-114874451');
REPLACE INTO t0(c1) VALUES(452243454);
SELECT Distinct t0.c1 FROM t0, t1, t2 WHERE (t2.c0) LIKE '1';
CREATE UNIQUE INDEX i0 USING BTREE ON t2(c1 ASC, c0(1)) ;
SELECT Distinct t0.c1 FROM t0, t1, t2 WHERE (t2.c0) LIKE '1';
SELECT DISTINCT t0.c1 FROM t0, t1, t2 IGNORE INDEX (i0) WHERE t2.c0 LIKE '1';
© 2026, Oracle Corporation and/or its affiliates