| Bug #103836 | select cannot get right result with a COLLATE clause | ||
|---|---|---|---|
| Submitted: | 28 May 2021 8:54 | Modified: | 31 May 2021 16:43 |
| Reporter: | Qilu Wei | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.24, 8.0.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[28 May 2021 9:13]
MySQL Verification Team
Hello Qilu Wei, Thank you for the report and test case. Observed that 8.0.25 release build is affected. regards, Umesh
[31 May 2021 4:45]
MySQL Verification Team
Hello Qilu Wei, Confirmed from internal discussion that this issue is already fixed in upcoming version and no longer seen. I'll try to find out which bug#/commit fixed this issue and let you know. Thank you! regards, Umesh - Quoting Dev's results Works ok in 8.0.26: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE t2 (gdbds int primary key,c varchar(255) , INDEX (c)); Query OK, 0 rows affected (0,07 sec) mysql> INSERT INTO t2 VALUES (10005,_ucs2 0x039C03C903B403B11F770308); Query OK, 1 row affected (0,02 sec) mysql> SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; +-------+---------------+ | gdbds | c | +-------+---------------+ | 10005 | Μωδαί̈ | +-------+---------------+ 1 row in set (0,00 sec) mysql>
[31 May 2021 16:43]
MySQL Verification Team
Fixed in MySQL 8.0.26, The following commit fixed reported problem: commit 5ad4ff759ac52320f27b176e33c5fe3502986887 Author: Roy Lyseng <roy.lyseng@oracle.com> Date: Wed Mar 10 13:48:06 2021 +0100 Bug#32593032
Description: 8.0.24 has a different behavior from 8.0.18 and other previous version: CREATE TABLE t2 (gdbds int primary key,c varchar(255) , INDEX (c)); INSERT INTO t2 VALUES (10005,_ucs2 0x039C03C903B403B11F770308); SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; 8.0.18: mysql> SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; +-------+---------------+ | gdbds | c | +-------+---------------+ | 10005 | Μωδαί̈ | +-------+---------------+ 1 row in set (0.00 sec) 8.0.24: mysql> SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; Empty set (0.00 sec) It seems that non-const items like c do not convert in 8.0.24 while convertion is done in 8.0.18 : 8.0.18: mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (convert(t2.c using ucs2) like <cache>((_ucs2'?\0%' collate ucs2_unicode_ci))) (cost=0.35 rows=1) -> Index scan on t2 using c (cost=0.35 rows=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) 8.0.24: mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; +-------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t2.c like <cache>((_ucs2'?\0%' collate ucs2_unicode_ci))) (cost=0.35 rows=1) -> Index scan on t2 using c (cost=0.35 rows=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) How to repeat: As above.