MySQL Bugs: #19473: GROUP_CONCAT always returns BLOB type

Bug #19473	GROUP_CONCAT always returns BLOB type
Submitted:	2 May 2006 2:57	Modified:	3 May 2006 2:26
Reporter:	Craig Everard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20	OS:	Windows (Win2K)
Assigned to:		CPU Architecture:	Any

Description:
When using GROUP_CONCAT() function in queries, the data type of the returned column is always BLOB, regardless of the value of the group_concat_max_len system variable.
I have read that if this value is less than or equal to 512 then the type returned should be VARCHAR. I have set this value to 512(bytes) using the MySQL Administrator tool. I have also checked that the value was updated correctly in the my.ini file, which it is. I then re-started the MySQL server and re-ran the query but it still always returns the BLOB type. For my purposes I need this query to return a VARCHAR. I have tried a value lower than 512 (510) but the problem recurred.
I am not using any ORDER BY in the query.

How to repeat:
Create any query against a table with multiple rows and a value to use as a group. Use the GROUP_CONCAT() function. Make sure the group_concat_max_len system variable is set to 512 bytes

Thank you for the bug report. Could you please provide a test case
with table definition, some insert data and a query sample.

Thanks in advance.

Thanks for the response, I have attached the requested build scripts for the table and some data. Note that you may wish to disable keys as the FK will not be valid in this test.

Here is the query itself that returns a BLOB type

select group_concat(sp.start_time1) AS MAINTENANCE_WINDOW_START_TIMES from service_periods sp where sp.period_type = 'Maintenance Window' group by sp.service_id

Note that all of my VIEWS that query different tables return a BLOB type if a group_concat() is utilised.

Regards Craig

Table and data scripts to reproduce group_concat() bug

Attachment: service_periods.sql (application/octet-stream, text), 18.22 KiB.

QB displaying query

Attachment: group_concat512.PNG (image/png, text), 40.52 KiB.

Thank you for the feedback. Indeed when starting the server with its
default value of group_concat_max_len=1024; QB displays the result
as BLOB. However when I edited the my.ini file as below:

[mysqld]
group_concat_max_len=512

and verified with mysql client:

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%concat%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 512   |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>

and issuing the query using QB the result is varchar as explained
in the Manual. You can see that in the screenshot I attached.
Also I tested starting the server as standalone as below:

C:\mysql\bin>mysqld-nt --standalone --console --group_concat_max_len=512
060502 21:28:24  InnoDB: Started; log sequence number 0 133516
060502 21:28:24 [Note] mysqld-nt: ready for connections.
Version: '5.0.20-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)

Thanks again for your assistance.
I have resolved this issue. The problem is that you cannot set the value of group_concat_max_len to < 1 Kb using the MySQL Adminstrator GUI. Mine was showing 512, but it actually represented 512 Kb, not 512 bytes. I resolved the issue by setting the value directly in the my.ini file with a text editor to 512 without the 'k' suffix.
Possibly this could be a minor issue to address in the next release of the Administrator tool.
Thanks for your prompt and helpfull responses.

Regards

Craig