| Bug #79353 | IN operator works incorrectly with collate | ||
|---|---|---|---|
| Submitted: | 20 Nov 2015 10:02 | Modified: | 20 Nov 2015 10:45 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.7.8, 5.6.29, 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Nov 2015 10:45]
MySQL Verification Team
Hello Su Dylan, Thank you for the report and test case. Verified as described with 5.6.29, 5.7.9 build. Thanks, Umesh
[20 Nov 2015 10:45]
MySQL Verification Team
// 5.6.29
mysql> select version();
+---------------------------------------+
| version() |
+---------------------------------------+
| 5.6.29-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "b")) \G
*************************** 1. row ***************************
(1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)): 1
(1, "a") in ((1, "A" collate utf8mb4_bin)): 0
(1, "a") in ((1, "b")): 0
1 row in set (0.00 sec)
[20 Nov 2015 10:46]
MySQL Verification Team
// 5.7.9
[root@cluster-repo ~]# mysql -ubug -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
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.
bug@localhost:(none)>select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "b")) \G
ERROR 1253 (42000): COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'
bug@localhost:(none)>
bug@localhost:(none)>
bug@localhost:(none)>set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
bug@localhost:(none)>select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "b")) \G
*************************** 1. row ***************************
(1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)): 1
(1, "a") in ((1, "A" collate utf8mb4_bin)): 0
(1, "a") in ((1, "b")): 0
Description: Output: ======= mysql> set names utf8mb4; Query OK, 0 rows affected (0.00 sec) mysql> select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "b")) \G *************************** 1. row *************************** (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)): 1 (1, "a") in ((1, "A" collate utf8mb4_bin)): 0 (1, "a") in ((1, "b")): 0 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.8-rc | +-----------+ 1 row in set (0.00 sec) Problem: ======== "select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin))" should return 0. How to repeat: set names utf8mb4; select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "A" collate utf8mb4_bin)), (1, "a") in ((1, "b")) \G Suggested fix: "select (1, "a") in ((1, "b"), (1, "A" collate utf8mb4_bin))" should return 0.