MySQL Bugs: #21894: group_concat needs a cast for numeric values

Bug #21894	group_concat needs a cast for numeric values
Submitted:	29 Aug 2006 8:00	Modified:	30 Aug 2006 6:49
Reporter:	Armin Lorenz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12.00	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
If i run a group_concat against a char/varchar column everything is fine. If i run it against a numeric column i get a result like this '?,?,?'. If i run it like 'group_concat(cast(column as CHAR))' everything is fine. If i use the direct driver it works without the 'CAST'. Got this after upgrading from server 4.1.7 to 4.1.21.

How to repeat:
See above.

Hi Armin and thanks for your problem report.
There were several changes in GROUP_CONCAT behavior between 4.17 and 4.1.21, you may check 
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-8.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-10.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-11.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-12.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-13.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-14.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-15.html
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-19.html 
and Bug report 14169 for details.
I was unable to repeat reported problem on MySQL server 4.1.22 BK and MyODBC 3.51.12 GA with attached test case.

C:\mysql\bin>mysql -uroot testdb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.22

mysql> select @@group_concat_max_len;
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+
1 row in set (0.02 sec)

mysql> create table t2 (fl1 FLOAT, fl2 FLOAT);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t2 values (1,11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (2,22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (2,33);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(fl1, fl2) as col1 FROM t2 GROUP BY fl1 ORDER BY fl1;
+---------+
| col1    |
+---------+
| 111     |
| 222,233 |
+---------+
2 rows in set (0.01 sec)

MyODBC test case output:
Connecting to myodbc1
COL val -> 111
COL val -> 222,233

Test case

Attachment: Bug21894.cpp (text/x-c++), 3.19 KiB.

Sorry. I missed some information yesterday. I have the problem when i use ODBC with ADODB (2.7) in ASP and VB6. I had not ODBC GUI for testing it directly. Downloaded ODBCView and everything works fine here. Seems to be a problem with ADO or anything with the Connection String. I use the one from http://dev.mysql.com/doc/refman/4.1/en/myodbc-configuration-connection-without-dsn.html only with a additional Port comand because my server runs on 3307.