| Bug #21505 | Create view - illegal mix of collation for operation 'UNION' | ||
|---|---|---|---|
| Submitted: | 8 Aug 2006 13:43 | Modified: | 28 Nov 2006 20:09 |
| Reporter: | Espen Berglund | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.24/5.1, 4.1 | OS: | Windows (Windows XP) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Aug 2006 16:52]
MySQL Verification Team
Thank you for the bug report. That is the expected behavior and explained in the Manual: http://dev.mysql.com/doc/refman/5.1/en/charset-literal.html MySQL determines a literal's character set and collation in the following manner: <cut> # If _X is specified but COLLATE is not specified, then character set X and its default collation are used.
[9 Aug 2006 9:39]
Espen Berglund
What you describe is if character set is part of the query. This is not the case. The problem is in regard to the third rule: Otherwise, the character set and collation given by the character_set_connection and collation_connection system variables are used. Maybe it's not a bug, but it's a huge inconsistency.
Lets put up another testcase which illustrates the problem:
mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
mysql> select collation('hello');
+--------------------+
| collation('hello') |
+--------------------+
| utf8_unicode_ci |
+--------------------+
Note that character set is not specified, hence the collation on the connection is used and we get the expected behaviour.
mysql> select collation(_utf8'hello');
+-------------------------+
| collation(_utf8'hello') |
+-------------------------+
| utf8_general_ci |
+-------------------------+
This is expected behaviour and rule 2 is applied ("If _X is specified but COLLATE is not specified, then character set X and its default collation are used.").
mysql> create view view2 as select 'hello' as col1;
mysql> select collation(col1) from view2;
+-----------------+
| collation(col1) |
+-----------------+
| utf8_general_ci |
+-----------------+
This should definetly not be the expected behaviour as character set is not defined in the create query, hence the 3rd rule should apply (e.g. Otherwise, the character set and collation given by the character_set_connection and collation_connection system variables are used. ).
mysql> create view view3 as select _utf8'hello' as col1;
mysql> select collation(col1) from view3;
+-----------------+
| collation(col1) |
+-----------------+
| utf8_general_ci |
+-----------------+
This would be the case if rule 2 should apply, e.g. "If _X is specified but COLLATE is not specified, then character set X and its default collation are used."
[10 Aug 2006 12:07]
MySQL Verification Team
Thank you for the feedback. "Otherwise, the character set and collation given by the character_set_connection and collation_connection system variables are used." mysql> create view view2 as select 'hello' as col1; Query OK, 0 rows affected (0.00 sec) mysql> select collation(col1) from view2; +-----------------+ | collation(col1) | +-----------------+ | utf8_general_ci | +-----------------+ 1 row in set (0.01 sec) mysql> show variables like 'coll%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.01 sec) mysql>
[20 Sep 2006 9:44]
Alexander Barkov
An simplified example, demonstrating the same problem:
--disable_warnings
drop table if exists t1;
drop view if exists v1, v2;
--enable_warnings
set names utf8;
create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci);
insert into t1 values('a'),('b');
create view v1 as select 'f' as col1;
select col1 from v1 union select col1 from t1;
ERROR 1271 (HY000) at line 9: Illegal mix of collations for operation 'UNION'
[20 Sep 2006 12:32]
Alexander Barkov
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12271
[20 Sep 2006 18:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12271 ChangeSet@1.2237, 2006-09-20 17:24:03+05:00, bar@mysql.com +8 -0 Bug#21505 Create view - illegal mix of collation for operation 'UNION' The problem was that any VIEW columns had always implicit derivation. Fix: derivation is now copied from the original expression given in VIEW definition. For example: - a VIEW column which comes from a string constant in CREATE VIEW definition have now coercible derivation. - a VIEW column having COLLATE clause in CREATE VIEW definition have now explicit derivation.
[9 Nov 2006 10:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/15079 ChangeSet@1.2297, 2006-11-09 14:41:34+04:00, bar@mysql.com +8 -0 Bug#21505 Create view - illegal mix of collation for operation 'UNION' The problem was that any VIEW columns had always implicit derivation. Fix: derivation is now copied from the original expression given in VIEW definition. For example: - a VIEW column which comes from a string constant in CREATE VIEW definition have now coercible derivation. - a VIEW column having COLLATE clause in CREATE VIEW definition have now explicit derivation.
[16 Nov 2006 8:55]
Alexander Barkov
Pushed into 5.0.29-rpl Pushed into 5.1.13-rpl
[16 Nov 2006 16:26]
Alexander Barkov
Appeared in 5.0.32 common Appeared in 5.1.14 common
[28 Nov 2006 20:09]
Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs.
[8 Apr 2008 19:15]
Sveta Smirnova
Bug #35896 was marked as duplicate of this one.
Description: If you set up a database with default collation different from default of the character set, then string literals, cast and convert gives wrong default collation for that column when you create a view (possibly other functions as well). E.g. set default character set = utf8 and default collation = utf8_unicode_ci for server and database. Create a view with a string literal, e.g "create view v2 as select 'foo' as bar". Column 'bar' will now have collation utf8_general_ci, so when you use this view in another create view statement, you will get an illegal mix of collation error. How to repeat: DB version 5.0.24, Character set=utf8, Collation=utf8_unicode_ci set via MySQL Administrator set character set utf8; create database mytest character set utf8 collate utf8_unicode_ci; create table a(col1 varchar(12),col2 varchar(12)); insert into a values('b','c'); insert into a values('d','e'); create view v1 as select 'f' as col1, 'g' as col2; create view v2 as select col1,col2 from v1 union select col1,col2 from a This script ends with the error "Illegal mix of collations for operation 'UNION'" The bug seems to be in the "create view v1..." statement, as the columns in this view is generated with utf8_general_ci collation even though default for database, server and connection is utf8_unicode_ci. The error seems to be related to string literals, cast and convert operations. Show create view v1; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _utf8'f' AS `col1`,_utf8'g' AS `col2` As a workaround you can execute: create view v1 as select 'f' collate utf8_unicode_ci as col1, 'g' collate utf8_unicode_ci as col2; but default generation of views should fallback to database default and not character set default Suggested fix: Make sure the default collation is set on each column when creating a view.