#1087 Results of group_concat on numeric field are displayed as 0x...

[Anonymous]

Originally created by: Invisibl...@gmail.com

What exact steps will reproduce the problem?
1. write a query containing group_concat(field) where field has a numeric
type (in my case int(11) unsigned)
2. execute the query and view the results.

What was the expected output?
E.g. 4918,13708 should be displayed

What happened instead?
E.g. 0x343931382C3133373038 is displayed

Version used?
HeidiSQL revision: 2374
MySQL Server version: 5.0.51
Operating system: WinXP

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Seems to be a server bug:

====================================
# mysql -T
Welcome to the MySQL monitor.

mysql> select group_concat(1) from (select 1 union select 2) a;
Field   1:  `group_concat(1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  binary (63)
Length:     1024
Max_length: 3
Decimals:   0
Flags:      BINARY
====================================

The server:
a) converts the integer values to ASCII strings (as seen in the output when run in
HeidiSQL - 0x31 is "1" in ASCII),
b) adds a comma (0x2C) between the converted strings,
c) sends the data (0x31 0x2C 0x31) to the client,
d) but tells the client that the data is binary, not ASCII.

Also, it looks odd to me that concatenating 1 and 2 yields "1,1".  May be another
server bug in my version (5.0.67).

Oddly, things work if you do not let the server auto-convert integers to strings,
but use strings to begin with:

====================================
mysql> select group_concat(a) from (select 'a' union select 'b') a;
Field   1:  `group_concat(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     1024
Max_length: 3
Decimals:   0
Flags:
====================================

In the above, group_concat yields latin1 (which is ASCII compatible - the exact
character set is a detail, as long as it conveys the information about what 0x61,
0x2C and 0x62 result data returned above means).  So that works.

If you feed strings, but add an integer into the mix, group_concat fails again:

====================================
mysql> select group_concat(a) from (select 'a' union select 1) a;
Field   1:  `group_concat(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  binary (63)
Length:     1024
Max_length: 3
Decimals:   0
Flags:      BINARY
====================================

You should open a bug at bugs.mysql.com.  Or let one of the HeidiSQL devs do it.

Status: Upstream

[Anonymous]

Originally posted by: Invisibl...@gmail.com

Thanks for the analysis so far.

The 1,1 result is correct, you didn't group so MySQL grouped over all columns for you
(there is only one column in your example so that one is chosen). Here's an example
with grouping and a workaround for this bug:

select cast(group_concat(y) as char) as b from (select 1 as x,2 as y union select 1
as x,3 as y) a group by x

I'll let the Heidi developers decide what to do with this bug (maybe add an option to
always display binary as char?). I don't know if it's a MySQL server bug or not, only
that I get the correct result through PHP.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

PHP does not have full Unicode support yet, making it a bad basis for comparison.

(The first version of PHP slated to have complete Unicode support is PHP6.  Even
then, I'd expect it to be buggy for a few years.)