Bug #121344 Prefix index on TINYTEXT causes wrong result for range predicate with utf8mb4_0900_ai_ci

• Submitted: 21 Sep 16:06
• Reporter: Ann C
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.1
• OS: Ubuntu (22.04)
• CPU Architecture: x86

[21 Sep 16:06] Ann C

Description:
On MySQL 26.7.1, a prefix index on a TINYTEXT column using
utf8mb4_0900_ai_ci can cause a range predicate to incorrectly miss a row.

For the value U+05A8 followed by 'x', the expression:

    c0 >= '0'

evaluates to TRUE when evaluated normally.

However, when a prefix index on c0(1) is present, the equivalent WHERE
predicate returns an empty result.

Using IGNORE INDEX or dropping the prefix index returns the correct row.

This is a silent wrong-result issue.

This may be related to Bug #20353, but this testcase reproduces on
MySQL 26.7.1 with InnoDB, utf8mb4_0900_ai_ci, and a range predicate (>=).

How to repeat:
DROP DATABASE IF EXISTS mysql_prefix_test;

CREATE DATABASE mysql_prefix_test
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE mysql_prefix_test;

CREATE TABLE t0 (
  c0 TINYTEXT
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

CREATE INDEX i0 ON t0(c0(1));

-- U+05A8 followed by ASCII 'x'
INSERT INTO t0 VALUES (CONVERT(0xD6A878 USING utf8mb4));

-- Verify the value and predicate evaluation.

SELECT c0, HEX(c0), c0 >= '0' AS ge
FROM t0;

-- Result:
--
-- +------+---------+----+
-- | c0   | HEX(c0) | ge |
-- +------+---------+----+
-- | ֨x   | D6A878  |  1 |
-- +------+---------+----+

-- Query using the prefix index.

SELECT c0, HEX(c0)
FROM t0
WHERE c0 >= '0';

-- Actual result:
--
-- Empty set
--
-- Expected result:
--
-- +------+---------+
-- | c0   | HEX(c0) |
-- +------+---------+
-- | ֨x   | D6A878  |
-- +------+---------+

-- Control: disable use of the prefix index.

SELECT c0, HEX(c0)
FROM t0 IGNORE INDEX (i0)
WHERE c0 >= '0';

-- Result:
--
-- +------+---------+
-- | c0   | HEX(c0) |
-- +------+---------+
-- | ֨x   | D6A878  |
-- +------+---------+

-- The result is also correct after removing the index.

DROP INDEX i0 ON t0;

SELECT c0, HEX(c0)
FROM t0
WHERE c0 >= '0';

-- Result:
--
-- +------+---------+
-- | c0   | HEX(c0) |
-- +------+---------+
-- | ֨x   | D6A878  |
-- +------+---------+

Expected result：

The WHERE query should return the row because c0 >= '0' evaluates to TRUE.

The presence of a prefix index must not change the semantic result of
the query.