MySQL Bugs: #34751: Inside HAVING clause subqueries with aggregates and without GROUP BY rise error

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)

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;

Thank you for the bug report. Verified as described.

Workaround: Use temp tables instead of 'having'

I think this is a duplicate of http://bugs.mysql.com/bug.php?id=8510

Posted by developer:
 
Fixed in 5.6 and up.