Bug #120580 MyISAM prefix index causes incorrect range query result

• Submitted: 31 May 7:52
• Modified: 1 Jun 6:21
• Reporter: fan liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[31 May 7:52] fan liu

Description:
A range query on a LONGTEXT column returns different results depending on
whether a prefix index exists. The index causes a matching row to be omitted.

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE `t0` (
  `c0` LONGTEXT,
  KEY `i0` (`c0`(1))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t0(c0) VALUES ('꙰dKc%z');
REPLACE INTO t0(c0) VALUES ('kQ');

SELECT ALL t0.c0 FROM t0 WHERE 'CZ' <= t0.c0;
c0|
--+
kQ|
DROP INDEX i0 ON t0;

SELECT ALL t0.c0 FROM t0 WHERE 'CZ' <= t0.c0;
c0    |
------+
꙰dKc%z|
kQ    |

[1 Jun 6:21] Chaithra Marsur Gopala Reddy

Hi fan liu,

Thank you for the test case. Verified as described.