An index changes the query result: SUBSTR(c, 1, 1) = 'a ' or LEFT(c, 1) = 'a ' misses 'abc'

CREATE TABLE t1 (c CHAR(4), KEY (c));

INSERT INTO t1 VALUES ('abc'), ('x'), ('y');

SELECT COUNT(*) INTO @c FROM t1 WHERE SUBSTR(c, 1, 1) = 'a ';

--let $c= query_get_value(SELECT @c AS v, v, 1)

if ($c != 1)

{

  --die COUNT(*) for SUBSTR(c, 1, 1) = 'a ' is $c and not 1

}

DROP TABLE t1;

Leads to:

mysqltest: At line 7: COUNT(*) for SUBSTR(c, 1, 1) = 'a ' is 0 and not 1

The index changes the query result. Without it (IGNORE INDEX (c)), the query finds 'abc'. EXPLAIN shows a range access on c. LEFT(c, 1) = 'a ', 'a ' = SUBSTR(c, 1, 1), a prepared statement SUBSTR(c, 1, 1) = ? and a utf8mb4_bin column give the same. With a row 'a b', SUBSTR(c, 1, 2) = 'a ' gives 0 and not 1. SUBSTR(c, 1, 1) = 'a' and a utf8mb4_nopad_bin column give the right result. Optimized builds give the same result. MySQL is not affected: with a latin1 column, MySQL 5.5.62 to 9.1.0 find 'abc' with and without the key.

From the code it seems that get_mm_leaf_for_LIKE() in sql/opt_range.cc makes the range for SUBSTR(c, 1, N) = 'const' and LEFT(c, N) = 'const' as for c LIKE 'const%'. For 'a ' the pattern is 'a %', so the range only holds values that start with 'a' and a space. The equality ignores the trailing spaces of 'a ' in a PAD SPACE collation, so it is true for 'abc'.