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

[16 Sep 2013 17:35] Rgb W
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!
[17 Sep 2013 14:22] Miguel Solorzano
Thank you for the bug report. Please provide a test case (create table, the necessary insert data, query, real and expected results). Thanks.
[18 Oct 2013 1:00] Bugs System
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".
[17 Nov 2013 14:14] Krasimir Vatev
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
[17 Nov 2013 14:28] Rgb W
Exactly, nicely done!

I can confirm this bug is also on 5.6.14 (Windows).
[21 Nov 2013 14:07] Andrii Nikitin
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 |
+----+-------+----------+
[21 Nov 2013 14:12] Andrii Nikitin
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 |
+----+-------+----------+