Bug #24273 Query fails on CONCAT - Works in Command Line
Submitted: 13 Nov 2006 18:23 Modified: 13 Nov 2006 19:22
Reporter: Jim Collins Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.2.5 Beta OS:Windows (XP)
Assigned to: CPU Architecture:Any
Tags: concat

[13 Nov 2006 18:23] Jim Collins 
Description:
The following Query from the Managing Hierarchical Data in MySQL ( http://dev.mysql.com/tech-resources/articles/hierarchical-data.html )fails in MySQL Query browser but works in the command line utility.

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

with the following error message

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

How to repeat:
create nested_category table from article and insert rows

CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);

INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

RUN Query
[13 Nov 2006 19:22] Sveta Smirnova 
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicates bug #9006