MySQL Bugs: #19552: group_concat returns incorrect value (binary data)

Bug #19552	group_concat returns incorrect value (binary data)
Submitted:	4 May 2006 23:07	Modified:	10 Jun 2006 23:50
Reporter:	David vonThenen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.21	OS:	Windows (WinXP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When you use the GROUP_CONCAT funciton on binary data, the returned value is not correct.

How to repeat:
My platform is windows (WinXp) using Ver 14.12 Distrib 5.0.21

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.21, for Win32 (ia32)

Connection id:          3
Current database:       sample
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.21-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 10 min 43 sec

Threads: 1  Questions: 37  Slow queries: 1  Opens: 1  Flush tables: 1  Open
tabl
es: 11  Queries per second avg: 0.058
--------------

I have a table:

CREATE TABLE file (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
data LONGTEXT,
PRIMARY KEY (id)
)

mysql> describe file;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(16) unsigned | NO   | PRI | NULL    | auto_increment |
| data  | longtext         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

There are two records in this table. Both records contain the same data (to
simply things) and the data field in each record is 1302528. Verified by:

mysql> select id, char_length(data) from file;
+----+-------------------+
| id | char_length(data) |
+----+-------------------+
|  1 |           1302528 |
|  2 |           1302528 |
+----+-------------------+
2 rows in set (0.08 sec)

When I try to run a group_concat to concat the two data columns into one, the
data that is returned is NOT the correct value. The expected value should be 2 x
1302528 in length. But it is not.

I should also mention that the "data" in the data columns are in fact
binary data. In case that does mean anything special. If anyone is wondering
what data is stored, it is actually the libmysql.dll in binary format.

Verified by:

mysql> select char_length( group_concat(data) ) from file;
+-----------------------------------+
| char_length( group_concat(data) ) |
+-----------------------------------+
|                             29461 |
+-----------------------------------+
1 row in set (5.72 sec)

PLEASE NOTE!!!! That I did changes the following mysql variables so that you can
concat items this large. So that isn't the problem.

Adjusted variables in the my.ini:
max_allowed_packet=100M
group_concat_max_len=100M

If you want a copy of the database, table structure and the two inserted
records, you can download it at: http://dev.checkyour6.net/sample.zip

Thank you for the bug report. According the Manual;

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows: 
<cut>
Beginning with MySQL 5.0.19, the type returned by GROUP_CONCAT() is always VARCHAR unless group_concat_max_len is greater than 512, in which case, it returns a BLOB. (Previously, it returned a BLOB with group_concat_max_len greater than 512 only if the query included an ORDER BY  clause.)

Then group_concat is returning truncated values from a longtext since
it is converted to blob:

C:\mysql\bin>mysqladmin -uroot create dbt

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

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

mysql> CREATE TABLE file (
    -> id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> data LONGTEXT,
    -> datab BLOB,
    -> PRIMARY KEY (id) );
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO file (data,datab) values (
    -> LOAD_FILE("C:/mysql/lib/opt/libmysql.dll"),
    -> LOAD_FILE("C:/mysql/lib/opt/libmysql.dll"));
Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'datab' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

Do you agree?

Thanks in advance.

I agree to that. I was under the impression that by increasing group_concat_max_len to a value larger than BLOB, it would accommodate the size required otherwise there is almost no point to having this parameter to set (just always return a BLOB).

Are there any plans or a pending feature request to make it return the value no matter what the length is? The reason why I ask is because if you have data that exceeds a BLOB the only way you can stuff the data into the database as one record is by the CONCAT function. If you have ever done a CONCAT on a 300 Meg file in 1-10Meg chuncks, it is just plain slow (like a high end 4-way server takes nearly 2-4 minutes). By the example thought, you can see why you might want to have a LONGTEXT value from a GROUP_CONCAT so you dont have to store the file in an "unchuncked" form.

Thanks for your help.

Thank you for the feedback. I am not aware if there is a plan or feature
request regarding this issue. However you can change this bug report
as feature request, making a more suitable Synopsis and then I can
edit as verified.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".