MySQL Bugs: #70357: Use of group_concat causes having conditions to be ignored

Bug #70357	Use of group_concat causes having conditions to be ignored
Submitted:	16 Sep 2013 17:35	Modified:	21 Nov 2013 14:07
Reporter:	Rgb W	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dear,

We had an issue on our 5.6.13 server with a query using multiple group_concats, multiple joins (m-n) , a group by on the first table on the primary integer id, a  single where condition and a single having condition, an order by clause and no limit clause.

Thing is, when i keep the order by clause, i get 14 results (wrong) . When i remove the order by clause, i get 11(!) results (correct). That is not possible if this were not a bug, right?

Even worse, the 11 correct results are not even close to being a subset of the 14 incorrect result. The having condition is not only ignored, but good results are missing. 

How to repeat:
any query with multiple joins, multiple group_concats using distinct and separators and order by within the group_concats + group by integer primary key on the first table + any having condition.

result: having condition ignored, wrong results

Suggested fix:
Our current workaround was this:
Removing the group_concat's from the query and not using it in combination with having!

Thank you for the bug report. Please provide a test case (create table, the necessary insert data, query, real and expected results). Thanks.

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".

This is a simplified version of a query which reproduces the same bug. I'm using RAND instead of real data to make it independent of the schema, 'test' can be any table with some records in it.

SELECT
    t._id,
    IF(RAND()>0.5, 'no','yes') AS _status,
    group_concat('something') AS gc
    FROM (
        SELECT
        RAND() * 10000 as _id,
        RAND() * 100 as _gr
        FROM test
        LIMIT 100
    ) AS t
 GROUP BY t._gr 
 HAVING _status = 'no'  
 ORDER BY  _id ASC

Exactly, nicely done!

I can confirm this bug is also on 5.6.14 (Windows).

Verified with following test case:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `value`) VALUES (1, 'test1'), (2, 'test2'), (3, 'test3');

SELECT
id, COALESCE(`value`) as val,
GROUP_CONCAT('whatever') AS gc
FROM test t
GROUP BY t.value
HAVING val = 'test2'
ORDER BY id ASC;
+----+-------+----------+
| id | val   | gc       |
+----+-------+----------+
|  1 | test1 | whatever |
+----+-------+----------+
1 row in set (0.54 sec)

SELECT
id, COALESCE(`value`) as val,
GROUP_CONCAT('whatever') AS gc
FROM test t
GROUP BY t.value
HAVING val = 'test3'
ORDER BY id ASC;
+----+-------+----------+
| id | val   | gc       |
+----+-------+----------+
|  2 | test2 | whatever |
|  3 | test3 | whatever |
+----+-------+----------+
2 rows in set (0.62 sec)

Expected result: 'Having' clause must work as described in manual, regarding to following output:

SELECT
id, COALESCE(`value`) as val,
GROUP_CONCAT('whatever') AS gc
FROM test t
GROUP BY t.value
ORDER BY id ASC;
+----+-------+----------+
| id | val   | gc       |
+----+-------+----------+
|  1 | test1 | whatever |
|  2 | test2 | whatever |
|  3 | test3 | whatever |
+----+-------+----------+

Workaround:

Wrap original query without HAVING into braces and apply HAVING clause to it, as below

select * from (
SELECT
id, COALESCE(`value`) as val,
GROUP_CONCAT('whatever') AS gc
FROM test t
GROUP BY t.value
ORDER BY id ASC
) x having val='test2';
+----+-------+----------+
| id | val   | gc       |
+----+-------+----------+
|  2 | test2 | whatever |
+----+-------+----------+