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:
None 
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
Description:
Hello,

I have a latin1 environment and use a function which return latin1 varchar in a CONCAT_WS or CONCAT. One SELECT works fine but a UNION-Select fails. I get ERROR 1271 

mysql> SELECT CONCAT_WS(' - ', getText(), getText()) UNION SELECT CONCAT_WS(' - ', getText(), getText(), getText());

ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'

I think it's in the separator of CONCAT_WS. Convert to latin1 works fine. But it's a workaround! 

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                |
+-----------------------------------------------+

How to repeat:
CREATE DATABASE `concattestdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
use concattestdb;
SET CHARACTER SET 'latin1';
SET collation_connection=latin1_german2_ci;

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------+
| 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     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | /usr/local/mysql-5.1.51-linux-x86_64-glibc23/share/charsets/ |
+--------------------------+-----------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_german2_ci |
| collation_database   | latin1_german2_ci |
| collation_server     | latin1_german2_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

DELIMITER //;

-- The function returns latin1 !!

CREATE FUNCTION `getText`()
    RETURNS varchar(20) CHARSET latin1
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  RETURN "Testtext";
END; //;

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)

-- I think, ' - ' is a utf8 string!?

mysql> SELECT CONCAT_WS(' - ', getText(), getText()) UNION SELECT CONCAT_WS(' - ', getText(), getText(), getText());

ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'

-- If i convert ' - ' to latin1 it works! But i think it's only a workaround!
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)

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.51-log |
+------------+
1 row in set (0.00 sec)
[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;