Bug #111272 Inconsistent behaviors on string comparison with/without index

• Submitted: 5 Jun 2023 8:47
• Modified: 5 Jun 2023 8:50
• Reporter: John Jove
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.32, 8.0.33
• OS: Any
• CPU Architecture: Any

[5 Jun 2023 8:47] John Jove

Description:
In the following case 1, the SELECT only returns 1 row.
When I remove the index from case 1, re-execute the SELECT, two rows are returned as shown in case 2.

How to repeat:
-- case 1
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYTEXT, KEY i0 (c1(1)));
INSERT INTO t1 VALUES ("͕fB-'"),("^1o");
SELECT c1 FROM t1 WHERE c1 > ("?ykFo|1"); -- 1

-- case 2
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYTEXT);
INSERT INTO t1 VALUES ("͕fB-'"),("^1o");
SELECT c1 FROM t1 WHERE c1 > ("?ykFo|1"); -- 2

[5 Jun 2023 8:50] MySQL Verification Team

Hello John,

Thank you for the report and test case.
Verified as described.

regards,
Umesh