| Bug #96379 | First query successfull, second - ERROR 1270 (HY000): Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 31 Jul 2019 5:55 | Modified: | 1 Aug 2019 4:20 |
| Reporter: | Владислав Сокол | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.16, 8.0.17, 5.7.27 | OS: | Windows |
| Assigned to: | CPU Architecture: | x86 | |
[31 Jul 2019 6:09]
MySQL Verification Team
Hello Владислав Сокол, Thank you for the report and feedback. regards, Umesh
[31 Jul 2019 6:10]
MySQL Verification Team
- 8.0.16, 8.0.17
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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.
mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
mysql> WITH
-> cte1 AS ( SELECT 0 num
-> UNION SELECT 1
-> UNION SELECT 2
-> UNION SELECT 3
-> UNION SELECT 4
-> UNION SELECT 5
-> UNION SELECT 6),
-> cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @month:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate
-> FROM cte1)
-> SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday
-> FROM cte2
-> HAVING friday BETWEEN @firstday
-> AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;
+------------+
| friday |
+------------+
| 2019-05-03 |
| 2019-05-10 |
| 2019-05-17 |
| 2019-05-24 |
| 2019-05-31 |
+------------+
5 rows in set, 3 warnings (0.04 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> WITH
-> cte1 AS ( SELECT 0 num
-> UNION SELECT 1
-> UNION SELECT 2
-> UNION SELECT 3
-> UNION SELECT 4
-> UNION SELECT 5
-> UNION SELECT 6),
-> cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @month:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate
-> FROM cte1)
-> SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday
-> FROM cte2
-> HAVING friday BETWEEN @firstday
-> AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;
ERROR 1270 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'between'
mysql>
mysql> WITH
-> cte1 AS ( SELECT 0 num
-> UNION SELECT 1
-> UNION SELECT 2
-> UNION SELECT 3
-> UNION SELECT 4
-> UNION SELECT 5
-> UNION SELECT 6),
-> cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @month:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate
-> FROM cte1)
-> SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday
-> FROM cte2
-> HAVING friday BETWEEN @firstday
-> AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;
ERROR 1270 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'between'
mysql>
[31 Jul 2019 13:24]
Peter Laursen
There will not need to be a Russian collation involved. Even with defaults like collation_database utf8mb4_0900_ai_ci collation_server utf8mb4_0900_ai_ci default_collation_for_utf8mb4 utf8mb4_0900_ai_ci .. the same thing happens.
[1 Aug 2019 4:20]
Владислав Сокол
2 Peter Laursen Of course. The query do not interact with database, so its collation doesn't matter.
Description: First query execution within a connection is successfull. All another (for the same query) fails with error message "ERROR 1270 (HY000): Illegal mix of collations". The reason - when assigning a value of explicitly set DATE type into user-defined variable it is treated as a STRING type variable having implicit latin1_swedish_ci collation whereas INTERVAL operator produces the value with coercible utf8mb4_0900_ai_ci collation (collation_connection?). Server version: Server version: 8.0.16 MySQL Community Server - GPL Server collation settings are: mysql> show variables like '%coll%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_ru_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci | +-------------------------------+-----------------------+ How to repeat: Execute the query (it calculates all fridays within all weeks of given year and month, ever when they are in the next month) twice within the same connection. The expressions with user-defined variable in last BETWEEN .. AND condition causes the error. WITH cte1 AS ( SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6), cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @month:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate FROM cte1) SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday FROM cte2 HAVING friday BETWEEN @firstday AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY; Suggested fix: Setting session collation to latin1_swedish_ci before the query execution solves the problem, but restoring collation for a connection restores the problem. And it can be incorrect when another collation-sensitive string operations are used in a query.