Bug #41859 Two element row constructor causes Illegal mix of collations
Submitted: 5 Jan 2009 10:47 Modified: 23 Jan 2009 15:13
Reporter: Janek Bogucki Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0.51a-3ubuntu5.4-log, 5.0.74 OS:Linux (Ubuntu 8.04.2)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[5 Jan 2009 10:47] Janek Bogucki
Description:
Adding a second item to a row constructor equality comparison causes this error,

mysql> select Name, Continent from Country where (Continent, Region) = ('Europe', 'Western Europe');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

but using the same strings in isolation does not cause the error,

mysql> select Name, Continent from Country where (Continent) = ('Europe') limit 1;
+-------------+-----------+
| Name        | Continent |
+-------------+-----------+
| Netherlands | Europe    |
+-------------+-----------+
1 row in set (0.00 sec)

mysql> select Name, Continent from Country where (Region) = ('Western Europe') limit 1;
+-------------+-----------+
| Name        | Continent |
+-------------+-----------+
| Netherlands | Europe    |
+-------------+-----------+
1 row in set (0.00 sec)

How to repeat:
Install the world database.

mysql> select Name, Continent from Country where (Continent, Region) = ('Europe', 'Western Europe');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
mysql> select Name, Continent from Country where (Continent) = ('Europe') limit 1;
+-------------+-----------+
| Name        | Continent |
+-------------+-----------+
| Netherlands | Europe    |
+-------------+-----------+
1 row in set (0.00 sec)

mysql> select Name, Continent from Country where (Region) = ('Western Europe') limit 1;
+-------------+-----------+
| Name        | Continent |
+-------------+-----------+
| Netherlands | Europe    |
+-------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_connection';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
+----------------------+-----------------+
1 row in set (0.00 sec)

mysql> show create table Country\G
*************************** 1. row ***************************
       Table: Country
Create Table: CREATE TABLE `Country` (
  `Code` char(3) NOT NULL default '',
  `Name` char(52) NOT NULL default '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
  `Region` char(26) NOT NULL default '',
  `SurfaceArea` float(10,2) NOT NULL default '0.00',
  `IndepYear` smallint(6) default NULL,
  `Population` int(11) NOT NULL default '0',
  `LifeExpectancy` float(3,1) default NULL,
  `GNP` float(10,2) default NULL,
  `GNPOld` float(10,2) default NULL,
  `LocalName` char(45) NOT NULL default '',
  `GovernmentForm` char(45) NOT NULL default '',
  `HeadOfState` char(60) default NULL,
  `Capital` int(11) default NULL,
  `Code2` char(2) NOT NULL default '',
  PRIMARY KEY  (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.51a-3ubuntu5.4-log |
+------------------------+
1 row in set (0.00 sec)

Extra info: When the strings are introduced with _latin1 the problem is not present but this does not the odd change in behaviour when a second element is added to the row constructor when introductions are not used.
 
mysql> select Name, Continent from Country where (Continent, Region) = (_latin1 'Europe', _latin1 'Western Europe');
+---------------+-----------+
| Name          | Continent |
+---------------+-----------+
| Netherlands   | Europe    |
| Belgium       | Europe    |
| Austria       | Europe    |
| Liechtenstein | Europe    |
| Luxembourg    | Europe    |
| Monaco        | Europe    |
| France        | Europe    |
| Germany       | Europe    |
| Switzerland   | Europe    |
+---------------+-----------+
9 rows in set (0.00 sec)
[5 Jan 2009 13:10] Valeriy Kravchuk
Verified with 5.0.74:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

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

mysql> CREATE TABLE `Country` (
    ->   `Code` char(3) NOT NULL default '',
    ->   `Name` char(52) NOT NULL default '',
    ->   `Continent` enum('Asia','Europe','North America','Africa','Oceania','An
tarctica','South
    '> America') NOT NULL default 'Asia',
    ->   `Region` char(26) NOT NULL default '',
    ->   `SurfaceArea` float(10,2) NOT NULL default '0.00',
    ->   `IndepYear` smallint(6) default NULL,
    ->   `Population` int(11) NOT NULL default '0',
    ->   `LifeExpectancy` float(3,1) default NULL,
    ->   `GNP` float(10,2) default NULL,
    ->   `GNPOld` float(10,2) default NULL,
    ->   `LocalName` char(45) NOT NULL default '',
    ->   `GovernmentForm` char(45) NOT NULL default '',
    ->   `HeadOfState` char(60) default NULL,
    ->   `Capital` int(11) default NULL,
    ->   `Code2` char(2) NOT NULL default '',
    ->   PRIMARY KEY  (`Code`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.09 sec)

mysql>  select Name, Continent from Country where (Continent, Region) = ('Europe
', 'Western Europe');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (
utf8_general_ci,COERCIBLE) for operation '='
mysql>  select Name, Continent from Country where (Continent, Region) = (_latin1
'Europe', _latin1'Western Europe');
Empty set (0.00 sec)

The other workaroud is just to set latin1 as "default":

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>  select Name, Continent from Country where (Continent, Region) = ('Europe
', 'Western Europe');
Empty set (0.00 sec)

But still I think this is a bug, as individual columns can be compared without any problems:

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>  select Name, Continent from Country where (Continent, Region) = ('Europe
', 'Western Europe');
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (
utf8_general_ci,COERCIBLE) for operation '='
mysql>  select Name, Continent from Country where Continent = 'Europe';
Empty set (0.00 sec)
[23 Jan 2009 15:13] Alexander Barkov
Duplicate to "Bug#37601 Cast Is Not Done On Row Comparison"