MySQL Bugs: #58325: group_concat function returns binary result for non-string columns

Bug #58325	group_concat function returns binary result for non-string columns
Submitted:	19 Nov 2010 15:00	Modified:	23 Nov 2010 17:39
Reporter:	Amit Dutta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.44, 5.0.91	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am using group_concat function and it is returning me binary for non-string data types viz. int, float, datetime

My program runs a query as:
select order_id, group_concat(id) as order_item_id, group_concat(sku) as sku,  from order_item group by order_id desc;
id* = int
sku* = varchar
order_id* = int

I expect mysql to return:
order_id order_item_id  sku
-------- -------------  ------------
1        1,2           a,b 

It is returning : 
order_id order_item_id  sku
-------- -------------  -------------
1        0x312C31       a,b

How to repeat:
Same steps

Suggested fix:
We can convert it to expected character set using the convert() function, however it seems to that it should return as normal like other data types.

Putting the db version.

Please, check if the same problem happens with a newer version, 5.0.91. If it still happens, please, provide complete repeatable test case, with CREATE TABLE, INSERTs etc.

Yes it is happening on 5.0.91 too, I have checked and tried to simulate using the following:

//Create table:
CREATE TABLE  `order_item` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `order_id` int(10) unsigned NOT NULL,
  `sku` varchar(10) NOT NULL,
  PRIMARY KEY  USING BTREE (`id`,`order_id`)
);

//insert 
insert into `order_item` values (1, 1, 'a'), (2,1,'b'), (3,1,'c');

//select using group_concat
select group_concat(id), group_concat(sku) from order_item group by order_id;

Thank you for the feedback.

I can not repeat described behavior:

mysql> CREATE TABLE  `order_item` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `order_id` int(10) unsigned NOT NULL,
    ->   `sku` varchar(10) NOT NULL,
    ->   PRIMARY KEY  USING BTREE (`id`,`order_id`)
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql> insert into `order_item` values (1, 1, 'a'), (2,1,'b'), (3,1,'c');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select group_concat(id), group_concat(sku) from order_item group by order_id;
+------------------+-------------------+
| group_concat(id) | group_concat(sku) |
+------------------+-------------------+
| 1,2,3            | a,b,c             |
+------------------+-------------------+
1 row in set (0.22 sec)

Do you use mysql command line client? If not please try if you can repeat the problem there.

No, I'm not using command line client.

I'm using mysql client - TOAD. Also, I tried using another client - mysql query browser, it returns blob for non-string types.

However, most of the developers/DBAs use client for their day to day work!, please suggest?

Correction, please read as:

However, most of the developers/DBAs use such clients for their day to day work!, please suggest?

Thank you for the feedback.

Please try in MySQL command line client and send us results.

It works fine on mysql command line client.

Thank you for the feedback.

This is not MySQL bug than. Please open bug for Todd developers. Regarding to MySQL Query Browser it is not supported, use MySQL Workbench instead.