Bug #57926 | Illegal mix of collations for operation 'UNION' .. using CONCAT/Function/latin1 | ||
---|---|---|---|
Submitted: | 2 Nov 2010 12:54 | Modified: | 16 Oct 2012 5:17 |
Reporter: | Richard Teubel | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.1.51, 5.1.52 | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | Collations, concat, Concat_WS, FUNCTION, Latin1, UNION |
[2 Nov 2010 12:54]
Richard Teubel
[2 Nov 2010 13:44]
Valeriy Kravchuk
I can repeat this with 5.1.52 on Windows: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.1.52-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE `concattestdb` DEFAULT CHARACTER SET latin1 COLLATE latin 1_german2_ci; Query OK, 1 row affected (0.01 sec) mysql> use concattestdb; Database changed mysql> SET CHARACTER SET 'latin1'; Query OK, 0 rows affected (0.00 sec) mysql> SET collation_connection=latin1_german2_ci; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'char%'; +--------------------------+---------------------------------------------------- -----+ | Variable_name | Value | +--------------------------+---------------------------------------------------- -----+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars ets\ | +--------------------------+---------------------------------------------------- -----+ 8 rows in set (0.02 sec) mysql> show variables like 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_german2_ci | | collation_database | latin1_german2_ci | | collation_server | utf8_general_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE FUNCTION `getText`() -> RETURNS varchar(20) CHARSET latin1 -> DETERMINISTIC -> CONTAINS SQL -> SQL SECURITY DEFINER -> COMMENT '' -> BEGIN -> RETURN "Testtext"; -> END; // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> select getText(); +-----------+ | getText() | +-----------+ | Testtext | +-----------+ 1 row in set (0.00 sec) mysql> SELECT CONCAT_WS(' - ', getText(), getText()); +----------------------------------------+ | CONCAT_WS(' - ', getText(), getText()) | +----------------------------------------+ | Testtext - Testtext | +----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CONCAT_WS(' - ', getText(), getText()) UNION SELECT CONCAT_WS(' - ', -> getText(), getText(), getText()); ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' mysql> SELECT CONCAT_WS(_latin1' - ', getText(), getText()) UNION SELECT -> CONCAT_WS(_latin1' - ', getText(), getText(), getText()); +-----------------------------------------------+ | CONCAT_WS(_latin1' - ', getText(), getText()) | +-----------------------------------------------+ | Testtext - Testtext | | Testtext - Testtext - Testtext | +-----------------------------------------------+ 2 rows in set (0.00 sec)
[2 Nov 2010 17:14]
MySQL Verification Team
Verified on Ubuntu 10.10 X86_64: mysql 5.1> SELECT CONCAT_WS(' - ', getText(), getText()) UNION SELECT CONCAT_WS(' - ', -> getText(), getText(), getText()); ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' mysql 5.1> show variables like "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | innodb_version | 1.0.12 | | protocol_version | 10 | | version | 5.1.53-debug-log | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+---------------------+ 6 rows in set (0.00 sec) mysql 5.1>
[11 Nov 2010 17:37]
Alexander Barkov
A smaller test demonstrating the same problem: mysql> DELIMITER //; mysql> CREATE FUNCTION getText() -> RETURNS varchar(20) CHARSET latin1 -> DETERMINISTIC -> CONTAINS SQL -> SQL SECURITY DEFINER -> COMMENT '' -> BEGIN -> RETURN "Testtext"; -> END; //; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> SET NAMES latin1 COLLATE latin1_german2_ci; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ' - ' UNION SELECT getText(); ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
[13 Apr 2011 12:21]
MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=60854.
[16 Oct 2012 5:17]
Erlend Dahl
Analysis from dev team: This is not a bug. In order to have the function return latin1_german2_ci please use: - either data type definition without specifying character set / collation: CREATE FUNCTION `getText`() RETURNS varchar(20) BEGIN RETURN "Testtext"; END; in this case the defaults will be taken from the "CREATE DATABASE" definition (which are latin1 / latin1_german2_ci) - or specify the full "CHARACTER SET .. COLLATE .." clause: CREATE FUNCTION `getText`() RETURNS varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci BEGIN RETURN "Testtext"; END;