Bug #21760 Illegal mix of collations with Functions

• Submitted: 21 Aug 2006 17:17
• Modified: 22 Aug 2006 9:05
• Reporter: William Chiquito
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.24
• OS: Windows (Windows XP SP2)
• CPU Architecture: Any
• Tags: UNION

[21 Aug 2006 17:17] William Chiquito

Description:
Error Code : 1271
Illegal mix of collations for operation 'UNION'

How to repeat:
DELIMITER $$;

DROP FUNCTION IF EXISTS `copsios_dbo`.`TEST`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `TEST`() RETURNS varchar(10)
BEGIN

RETURN '1234567890';

END$$

DELIMITER ;$$

Execute:

SELECT CONCAT(TEST(), ' ', TEST())
UNION
SELECT CONCAT(TEST(), ' ', TEST())

Error Code : 1271
Illegal mix of collations for operation 'UNION'
(0 ms taken)

Suggested fix:
SELECT CONVERT(CONCAT(TEST(), ' ', TEST()), CHAR(21))
UNION
SELECT CONVERT(CONCAT(TEST(), ' ', TEST()), CHAR(21))

WHY?

[22 Aug 2006 9:05] Sveta Smirnova

Thank you for the report.

I can not repeat it using last 5.0.25 BK sources:

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `copsios_dbo`.`TEST`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `TEST`() RETURNS varchar(10)
    -> BEGIN
    ->
    -> RETURN '1234567890';
    ->
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> SELECT CONCAT(TEST(), ' ', TEST())
    -> UNION
    -> SELECT CONCAT(TEST(), ' ', TEST());
+-----------------------------+
| CONCAT(TEST(), ' ', TEST()) |
+-----------------------------+
| 1234567890 1234567890       |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Looks like you have different character sets/collations for database and client.