Bug #120132 Unexpected Query Results Caused by Prefix Index on TEXT Column

• Submitted: 23 Mar 11:28
• Modified: 24 Mar 7:45
• Reporter: b a
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4.8
• OS: Any
• CPU Architecture: Any

[23 Mar 11:28] b a

Description:
The presence of the prefix index in Query 1 should not change the query result. The index should only affect performance, not the correctness of the comparison. The unexpected result indicates that the index is influencing query logic, which is incorrect.

How to repeat:
```sql
-- query 1
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 text);
CREATE INDEX idx_c0_prefix ON t0(c0(1));
INSERT INTO t0 VALUES ("͕f");
SELECT c0 FROM t0 WHERE c0 > ("+*");
-- query 2
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 text);
INSERT INTO t0 VALUES ("͕f");
SELECT c0 FROM t0 WHERE c0 > ("+*"); 

-- result 1
Empty set (0.00 sec)

-- result 2

+------+
| c0   |
+------+
| ͕f   |
+------+
1 row in set (0.00 sec)

```

[23 Mar 16:14] Jean-François Gagné

Probably related to Bug#118009.

In above, Bug#35169384 is mentioned [1], and this bug fixed in 8.4.0 probably caused the regression described here.

[1]: https://github.com/mysql/mysql-server/commit/6ae1d0216c416c3053a301c7b4df2ce4c0bf212d

[24 Mar 6:56] Chaithra Marsur Gopala Reddy

Hi,

Thank you for the test case. Verified as described.
Problem is present in 8.0 as well. So, it may not be related to Bug#35169384.