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:
None 
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 5:55] Владислав Сокол
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.
[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.