Bug #117651 STRAIGHT_JOIN produces inconsistent results when comparing text values

• Submitted: 9 Mar 15:43
• Modified: 10 Mar 5:42
• Reporter: jinhui lai
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.2.0, 9.1.0, 8.0.41, 8.4.4
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[9 Mar 15:43] jinhui lai

Description:
STRAIGHT_JOIN produces inconsistent results when comparing text values, likely due to character encoding.

How to repeat:
docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.2.0

CREATE TABLE t0(c1 TEXT STORAGE MEMORY);
CREATE TABLE t1 LIKE t0;
CREATE INDEX i1 ON t1(c1(1)) VISIBLE;
INSERT INTO t0(c1) VALUES('29'), ('30');
INSERT INTO t1(c1) VALUES("⅗");

SELECT t0.c1 AS t0c1, t1.c1 AS t1c1 FROM t1 STRAIGHT_JOIN t0 ON t1.c1 < t0.c1;
+------+------+
| t0c1 | t1c1 |
+------+------+
| 30   | ⅗    |
+------+------+
1 row in set (0.00 sec)
SELECT t0.c1 AS t0c1, t1.c1 AS t1c1 FROM t0 STRAIGHT_JOIN t1 ON t1.c1 < t0.c1;
Empty set (0.00 sec)

[10 Mar 5:42] MySQL Verification Team

Hello jinhui lai,

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

regards,
Umesh