Bug #34751 Inside HAVING clause subqueries with aggregates and without GROUP BY rise error
Submitted: 21 Feb 2008 22:19 Modified: 8 Feb 2018 22:42
Reporter: Gleb Shchepa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[21 Feb 2008 22:19] Gleb Shchepa
Description:
In the subquery of query

  SELECT t1.a FROM t1 GROUP BY t1.a
    HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1)

both SUM(t1.a) and t1.a are constants (there is grouping by t1.a in the outer query, and subquery is in the HAVING clause).

However, in the ONLY_FULL_GROUP_BY mode this query rises the ER_MIX_OF_GROUP_FUNC_AND_FIELDS error:

Server version: 5.0.56-debug Source distribution

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t1.a FROM t1 GROUP BY t1.a HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

How to repeat:
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
set @@sql_mode='ONLY_FULL_GROUP_BY';

CREATE TABLE t1 (a INT);
CREATE TABLE t2 LIKE t1;

SELECT t1.a FROM t1 GROUP BY t1.a HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2  LIMIT 1);

DROP TABLE t1, t2;
[21 Feb 2008 23:14] Miguel Solorzano
Thank you for the bug report. Verified as described.
[22 Feb 2008 21:12] Omer Barnir
Workaround: Use temp tables instead of 'having'
[21 Sep 2008 18:19] Roland Bouman
I think this is a duplicate of http://bugs.mysql.com/bug.php?id=8510
[8 Feb 2018 22:42] Roy Lyseng
Posted by developer:
 
Fixed in 5.6 and up.