| 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: | |
| 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 | |
[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"
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)