MySQL Bugs: #119286: Query Results Incorrect Due to Indexed String Field

Bug #119286	Query Results Incorrect Due to Indexed String Field
Submitted:	1 Nov 9:57	Modified:	4 Nov 9:22
Reporter:	Go Yakult	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The only difference between the two tables is that one has an index, but after performing the same insert and query operations, the results are different.

CREATE TABLE `t0` (
  `c0` varchar(500) DEFAULT NULL,
  KEY `i2` (`c0`(1))
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t0` (
  `c0` varchar(500) DEFAULT NULL
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- insert data
INSERT INTO `t0` VALUES (NULL),(''),('-1015653034'),('1760552060'),('1760552060');

-- Below are the comparison results

mysql> SELECT t0.c0 FROM t0 WHERE (t0.c0) < ("ֳ5sFsg4");
+------+
| c0   |
+------+
|      |
+------+

mysql> SELECT t0.c0 FROM t0 WHERE (t0.c0) < ("ֳ5sFsg4");
+-------------+
| c0          |
+-------------+
|             |
| -1015653034 |
| 1760552060  |
| 1760552060  |
+-------------+

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` varchar(500) DEFAULT NULL,
  KEY `i2` (`c0`(1))
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t0` VALUES (NULL),(''),('-1015653034'),('1760552060'),('1760552060');
SELECT t0.c0 FROM t0 WHERE (t0.c0) < ("ֳ5sFsg4");

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` varchar(500) DEFAULT NULL
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t0` VALUES (NULL),(''),('-1015653034'),('1760552060'),('1760552060');
SELECT t0.c0 FROM t0 WHERE (t0.c0) < ("ֳ5sFsg4");

Thanks for the bug report. This issue was fixed in MySQL 8.4.0 as part of bug#35169384 (the bug report is not available in the public bug tracker). Closing as duplicate.

I am able to repro in 8.0.44 and 8.3.0.  I am not able to repro in 9.5.0, 8.4.7, 8.4.0 and 5.7.44 (for 5.7, I had to remove the COLLATE part of the CREATE TABLE.

Because this bug is not in 5.7, I think it should be considered a regression in 8.0.

For reference, I traced-down the fix to below commit.

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

Is there a plan to back-port the fix in 8.0 ?

If you use the utf8mb4_unicode_ci collation, you can see the problem on 5.7 too.

I'm not aware of any plans to backport it to 8.0. There were some bugs filed for performance regressions because of this fix (bug#118009, bug#118486), so it's probably not a candidate for backporting in its current shape.