MySQL Bugs: #8568: GROUP_CONCAT returns string, unless in a UNION in which case returns BLOB

Bug #8568	GROUP_CONCAT returns string, unless in a UNION in which case returns BLOB
Submitted:	17 Feb 2005 14:42	Modified:	18 Nov 2005 16:17
Reporter:	Steve Owen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.10-nt	OS:	Windows (Windows 2003)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
I upgraded to 4.1.10-nt this morning, and a query I had suddenly stopped working within ASP. It turns out that within two or more UNIONised queries a GROUP_CONCAT request returns a BLOB, despite being comprised purely of string elements and returning a STRING in 4.1.9-nt

This is a bug because in ASP, you can't write out a BLOB field using the same command you would use to write a string. While it's fairly straightforward to change the code, this problem could reoccur if it is indeed a bug and is subsequently fixed.

How to repeat:
CREATE TABLE t (
  c1 char,
  c2 char);

insert into t values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');

select GROUP_CONCAT(c2) from t where c1 = 'a';  #returns a,b as text

(select GROUP_CONCAT(c2) from t where c1 = 'a')
UNION
(select GROUP_CONCAT(c2) from t where c1 = 'b'); #returns a,b as blob

Verified also with Access 2003.

Same problem on Linux

I can't repeat this bug against the latest 4.1:

mysql> create table t1 (select GROUP_CONCAT(c2) from t where c1 = 'a') UNION (select GROUP_CONCAT(c2) from t where c1 = 'b');
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table t2  select GROUP_CONCAT(c2) from t where c1 = 'a';          Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `GROUP_CONCAT(c2)` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `GROUP_CONCAT(c2)` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 4.1.16-valgrind-max-debug |
+---------------------------+
1 row in set (0.00 sec)

Please reopen the bug report if you still can repeat the problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32415