Bug #120579 LIKE query returns inconsistent results with MyISAM prefix index

• Submitted: 30 May 11:39
• Reporter: fan liu
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[30 May 11:39] fan liu

Description:
A query with a LIKE predicate returns inconsistent results depending on the selected columns when a MyISAM table has a prefix index on a TEXT column.

In the test case below, table t0 has a TEXT column c0 and an index on the first character of c0 together with c1: KEY i0 (c0(1), c1)
The table contains a row inserted with c0 = -0.0 and c1 = 2101102281, plus another row where c0 is NULL.

The following query returns one row:

SELECT t0.c1 FROM t0 WHERE t0.c0 LIKE '0'; --{2101102281}

However, if the TEXT column c0 is added to the SELECT list, the same WHERE predicate returns an empty result set:

SELECT t0.c0, t0.c1 FROM t0 WHERE t0.c0 LIKE '0';

How to repeat:
DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
CREATE TABLE `t0` (`c0` text ,  `c1` bigint DEFAULT NULL , KEY `i0` (`c0`(1),`c1`)) ENGINE=MyISAM ;
REPLACE LOW_PRIORITY INTO t0(c1, c0) VALUES(2101102281, -0.0);
REPLACE INTO t0(c0) VALUES(NULL);
SELECT t0.c1 FROM t0 WHERE (t0.c0) LIKE '0';
SELECT t0.c0 ,t0.c1 FROM t0 WHERE (t0.c0) LIKE '0';