Bug #72753 mysql 5.6.17 user defined variables charset problem
Submitted: 26 May 2014 18:09 Modified: 28 May 2014 9:23
Reporter: Gökay Güneş Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version:5.6.17 OS:Windows
Assigned to: CPU Architecture:Any

[26 May 2014 18:09] Gökay Güneş
Description:
mysql> set names latin5;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DATE1='1900-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DATE2=CURDATE();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @DATE1>@DATE2;
ERROR 1267 (HY000): Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (
latin1_swedish_ci,IMPLICIT) for operation '>'

mysql> select charset(@DATE1),charset(@DATE2); ## on mysql 5.6.17
.+-----------------+-----------------+
| charset(@DATE1) | charset(@DATE2) |
+-----------------+-----------------+
| latin5          | latin1          |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select charset(@DATE1),charset(@DATE2); ## on mysql 5.1.70
.+-----------------+-----------------+
| charset(@DATE1) | charset(@DATE2) |
+-----------------+-----------------+
| latin5          | binary          |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "%CHARACTER%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin5                     |
| character_set_connection | latin5                     |
| character_set_database   | latin5                     |
| character_set_filesystem | latin5                     |
| character_set_results    | latin5                     |
| character_set_server     | latin5                     |
| character_set_system     | utf8                       |
| character_sets_dir       | D:\eskom56\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

How to repeat:
mysql> set names latin5;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DATE1='1900-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DATE2=CURDATE();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @DATE1>@DATE2;
ERROR 1267 (HY000): Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (
latin1_swedish_ci,IMPLICIT) for operation '>'

mysql> select charset(@DATE1),charset(@DATE2); ## on mysql 5.6.17
.+-----------------+-----------------+
| charset(@DATE1) | charset(@DATE2) |
+-----------------+-----------------+
| latin5          | latin1          |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select charset(@DATE1),charset(@DATE2); ## on mysql 5.1.70
.+-----------------+-----------------+
| charset(@DATE1) | charset(@DATE2) |
+-----------------+-----------------+
| latin5          | binary          |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "%CHARACTER%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin5                     |
| character_set_connection | latin5                     |
| character_set_database   | latin5                     |
| character_set_filesystem | latin5                     |
| character_set_results    | latin5                     |
| character_set_server     | latin5                     |
| character_set_system     | utf8                       |
| character_sets_dir       | D:\eskom56\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
[27 May 2014 20:38] Erlend Dahl
Maybe a dup of: Bug#62077 Assign date/time type to user-defined variable changes type/charset/collation