MySQL Bugs: #61034: "Illegal mix of collations for operation "By date datatype

Bug #61034	"Illegal mix of collations for operation "By date datatype
Submitted:	3 May 2011 3:58	Modified:	18 Jan 2018 13:16
Reporter:	boy dreaming	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5.11, 5.5.20	OS:	Any (CENTOS 5.6 64bit, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	Collations, date, mix

Description:
When we create a table,set character to GBK,set collation to gbk_chinese_ci,and set the a field datatype to datetime or date or time，
and then we use the query like this "select * from test where f='中国'",it will report error :
Illegal mix of collations for operation

And if we change the field datatype to int or varchar ,it work no problem.

Please Check it.thanks.

How to repeat:
1,create a database,set character gbk,set collation to gbk_chinese_ci
2,create a table
3. create a field,set datatype to datetime.
4. run a query, such as "select * from test where f='中'"
if the search word is any chinese character,it will be show error.but the value is number or alpha,it is ok.

Suggested fix:
At this time,the soluation one is change the date datatype to varchar,the other is when we write code,check the field datatype before.

Thank you for the report.

What SHOW VARIABLES LIKE '%char%' and SHOW VARIABLES LIKE '%coll%' outputs?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

если обернуть поле в DATE_FORMAT() LIKE "%баг%" ошибки не будет.

I can confirm this bug still exists in MySQL 5.5.20, so suggest to reopen this bug.
Here's simple test case:

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.5.20-55-log |
+---------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%coll%';
+----------------------+---------------------+
| Variable_name        | Value               |
+----------------------+---------------------+
| collation_connection | utf8_general_ci     |
| collation_database   | utf8_general_ci     |
| collation_server     | cp1251_ukrainian_ci |
+----------------------+---------------------+
3 rows in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | cp1251                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show create database collation_test;
+----------------+-------------------------------------------------------------------------+
| Database       | Create Database                                                         |
+----------------+-------------------------------------------------------------------------+
| collation_test | CREATE DATABASE `collation_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table test (a date);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test where a like 'тест'; /* 'тест' is any non-latin text */
ERROR 1271 (HY000): Illegal mix of collations for operation 'like'

Please, feel free to request any additional info needed.

Verified as described in the last comment:

mysql> CREATE DATABASE `collation_test` /*!40100 DEFAULT CHARACTER SET utf8
    -> */;
Query OK, 1 row affected (0.17 sec)

mysql> use collation_test;
Database changed
mysql> create table test (a date);
Query OK, 0 rows affected (0.78 sec)

mysql> select * from test where a like 'тест';
ERROR 1271 (HY000): Illegal mix of collations for operation 'like'
mysql> show variables like 'char%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | latin1                                |
| character_set_system     | utf8                                  |
| character_sets_dir       | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.20-debug |
+--------------+
1 row in set (0.00 sec)

This bug affected me with code, generated by PHPRunner.

it affects me too in arabic 
has anyone found a solution
(Server version: 5.6.20 - MySQL Community Server (GPL))

Still reproduces on 5.7.17.

Fixed in 8.0.2 DMR.