Bug #17389 group_concat returns strange values
Submitted: 14 Feb 2006 16:50 Modified: 14 Feb 2006 18:11
Reporter: Andre Timmer Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:3.1.12 OS:Any (All)
Assigned to: CPU Architecture:Any

[14 Feb 2006 16:50] Andre Timmer 
Description:
Database : 5.0.18-max-log 
Jdbc driver: mysql-connector-java-3.1.12-bin.jar

group_concat in virtual select returns malformed / wrong data.

Using unix client mysql produces a good result, but when using jdbc the result is wrong!!!

How to repeat:
create table adres (
  postcode          varchar:(6)
, code_gemeente char(4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into adres (postcode, code_gemeente) values ('01887', '000');
insert into adres (postcode, code_gemeente) values ('01887', '000');
commit;

select postcode, group_concat(code_gemeente) gemeenten
from   adres
where postcode = 01887
group by 1;

returns an OK resultst:
   postcode  gemeenten
   01887      0000,0000

---------------------------------------------

select * from
(
   select postcode, group_concat(code_gemeente) gemeenten
   from adres
   where postcode = 01887
   group by 1
) aa
where length(gemeenten) > 4;

returns a bad resultset:
   postcode  gemeenten
   01887      [B@75d174

Suggested fix:
.
[14 Feb 2006 16:52] Andre Timmer 
'[B@75d174' looks like a pointer address.
[14 Feb 2006 17:47] Andre Timmer 
Sorry, it's no a bug. 
The custom java code used to interpret the data uses resultSet.getString(..).
This works on integers, dates etc. but when it's of type  java.sql.Types.Array the pointer is returned.

==> Request
The request would be to have getString on a column of type Array to return a human readable string. 

The same default behaviour as in the unix mysql client.
[14 Feb 2006 18:11] Mark Matthews 
This is a duplicate of BUG#16712 

The server is returning metadata in some cases that causes any client (that cares about metadata) to treat the group_concat column value as a blob or (var)binary. Since the JDBC specification requires that those types map to byte[], that's what you get.

(java.sql.Array has nothing to do with it).