MySQL Bugs: #4035: GROUP_CONCAT with HAVING clause truncates field

Bug #4035	GROUP_CONCAT with HAVING clause truncates field
Submitted:	7 Jun 2004 12:39	Modified:	16 Jun 2004 9:54
Reporter:	Klaas Geldof	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3	OS:
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
GROUP_CONCAT was introduced in MySQL 4.1 and can be used to get a list of fields in one field separated by a given separator. When a HAVING clause is added to the query, the field which contains the list of fields is truncated. The number of characters that's missing at the end is equal to the length of the separator. In MySQL 5.0.0-alpha this bug is also present.

How to repeat:
Create a table 'person' with one field 'name'. Insert some rows, let's say 'John', 'Anna' and 'Bill'.

The query 'SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM person' returns one row: 'John, Anna, Bill'. That's ok.

The query 'SELECT GROUP_CONCAT(name SEPARATOR ', ') AS names FROM person HAVING names LIKE '%An%'' however returns 'John, Anna, Bi'. A WHERE clause instead of the HAVING clause is not possible because 'names' cannot be used in a WHERE clause.

Verified against 4.1.3/Linux.  Thank you for the report.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a char(4) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('John'),('Anna'),('Bill');
SELECT GROUP_CONCAT(a SEPARATOR ',') AS names FROM t1 HAVING names LIKE '%An%';
SELECT GROUP_CONCAT(a SEPARATOR ',') AS names FROM t1 HAVING LEFT(names,1) = 'J';
DROP TABLE t1;

ChangeSet
  1.1903 04/06/08 18:01:15 ram@gw.mysql.r18.ru +6 -0

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html