Bug #15201 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COE
Submitted: 23 Nov 2005 20:34 Modified: 27 Jan 2006 15:32
Reporter: John Campbell Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[23 Nov 2005 20:34] John Campbell
Description:
When I run the following query in version 4.0.15 it runs perfect.  When I run the query through Query Browser in 5.0 I get the error
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION'

The problem seems to be isolated in the group by field.  If the group by field is changed to an integer the query will not give the error listed and will return the desired resultset.  Also,  If I do not do a Union of the two select statements, each one will work and return the desired result.

  SELECT 1 AS SECTION_HEADER,
         VENDOR_CONTACT.CONTACT_LOGON,
         COUNT(DISTINCT(BS_DATA.ID))  AS TICKET_COUNT
    FROM BS_DATA LEFT OUTER JOIN VENDOR_CONTACT ON BS_DATA.CONTACT_ID=VENDOR_CONTACT.CONTACT_ID
   WHERE PRIMARY_VENDOR=487
GROUP BY VENDOR_CONTACT.CONTACT_LOGON

UNION
  SELECT 2 AS SECTION_HEADER,
         'Total',
          COUNT(DISTINCT(BS_DATA.ID))
    FROM BS_DATA LEFT OUTER JOIN VENDOR_CONTACT ON BS_DATA.CONTACT_ID=VENDOR_CONTACT.CONTACT_ID
   WHERE PRIMARY_VENDOR=487

Thank you

How to repeat:
Create a query using UNION between 2 selects.  The first select will perform an aggregate function using a group by clause that is a non-integer.  The second select in the union will perform and aggregate function withouth a group by clause.

What the query is actually doing is returning detail records in the first select and a total of those records in the second select.
[26 Nov 2005 16:51] Valeriy Kravchuk
Thank you for a problem report. Looks like I'll need your tables definitions and my.cnf settings, because I was not able to repeat the problem you described with a simplified test case on 5.0.17-BK on my Linux box:

mysql> create table t15201 (c1 int, c2 int);
Query OK, 0 rows affected (0,00 sec)

mysql> insert into t15201 values(1, 1);
Query OK, 1 row affected (0,00 sec)

mysql> insert into t15201 values(1, 2);
Query OK, 1 row affected (0,00 sec)

mysql> insert into t15201 values(2, 1);
Query OK, 1 row affected (0,00 sec)

mysql> insert into t15201 values(2, 2);
Query OK, 1 row affected (0,00 sec)

mysql> insert into t15201 values(3, 1);
Query OK, 1 row affected (0,01 sec)

mysql> insert into t15201 values(3, 2);
Query OK, 1 row affected (0,01 sec)

mysql> insert into t15201 values(3, 3);
Query OK, 1 row affected (0,00 sec)

mysql> select 1, c1, count(distinct c2) as c2 from t15201 group by c1
    -> union
    -> select 2, 0, count(distinct c2) as c2 from t15201;
+---+------+----+
| 1 | c1   | c2 |
+---+------+----+
| 1 |    1 |  2 |
| 1 |    2 |  2 |
| 1 |    3 |  3 |
| 2 |    0 |  3 |
+---+------+----+
4 rows in set (0,00 sec)

mysql> alter table t15201 modify c1 char(20);
Query OK, 7 rows affected (0,01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> desc t15201;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | char(20) | YES  |     | NULL    |       |
| c2    | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0,01 sec)

mysql> select 1, c1, count(distinct c2) as c2 from t15201 group by c1 union select 2, 'Total', count(distinct c2) as c2 from t15201;
+---+-------+----+
| 1 | c1    | c2 |
+---+-------+----+
| 1 | 1     |  2 |
| 1 | 2     |  2 |
| 1 | 3     |  3 |
| 2 | Total |  3 |
+---+-------+----+
4 rows in set (0,01 sec)

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0,00 sec)

Any other ideas on how to repeat the problem you described are welcomed.
[26 Nov 2005 20:18] David McBride
I also get the same error: Illegal mix of collations (ascii_general_ci,IMPLICIT) and  (latin1_swedish_ci,COERCIBLE) for operation '='

Running 4.1.15

CREATE TABLE `servicelog` (
  `id` int(11) NOT NULL auto_increment,
  `station_id` int(11) NOT NULL default '0',
  `airline_id` int(11) NOT NULL default '0',
  `tail_number` varchar(10) NOT NULL default '',
  `local_capture_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `server_capture_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `agent` varchar(20) NOT NULL default '',
  `user_id` int(11) NOT NULL default '0',
  `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `service_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

and the following query fails: 

select * from servicelog where upper(trim(tail_number)) = Upper('N127F');

Illegal mix of collations (ascii_general_ci,IMPLICIT) and  (latin1_swedish_ci,COERCIBLE) for
operation '='
[27 Dec 2005 0:00] Bugs System
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".
[27 Dec 2005 15:32] Valeriy Kravchuk
To John Campbell:

So, can you provide the additional information to repeat the problem you described?

To David McBride:

I've tried to repeat the problem you described on 4.1.17-BK on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `servicelog` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `station_id` int(11) NOT NULL default '0',
    ->   `airline_id` int(11) NOT NULL default '0',
    ->   `tail_number` varchar(10) NOT NULL default '',
    ->   `local_capture_time` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `server_capture_time` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `agent` varchar(20) NOT NULL default '',
    ->   `user_id` int(11) NOT NULL default '0',
    ->   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP,
    ->   `service_id` int(11) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=ascii;
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into servicelog (tail_number) values('N127F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from servicelog where upper(trim(tail_number)) = Upper('N127F');
+----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+
| id | station_id | airline_id | tail_number | local_capture_time  | server_capture_time | agent | user_id | last_update         | service_id |
+----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+
|  1 |          0 |          0 | N127F       | 0000-00-00 00:00:00 | 0000-00-00
00:00:00 |       |       0 | 2005-12-27 18:17:38 |          0 |
+----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+
1 row in set (0.00 sec)

So, please, explain what shell I do to get this error message in your case.
[30 Dec 2005 15:38] [ name withheld ]
From David McBride: 

Apparently this is MY problem: 

SHOW VARIABLES LIKE 'collation%'  
  
Variable_name  Value  
collation_connection utf8_general_ci 
collation_database ascii_general_ci 
collation_server latin1_swedish_ci 

I'm guessing that having different collations is what's causing my SQL query failure. 

So how do I get all collations to be the same? 

Thx 
David
[28 Jan 2006 0:00] Bugs System
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".