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:
None 
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 8:54] Qilu Wei
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.
[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