MySQL Bugs: #78183: Similar query with aggregate functions returns inconsistent result

Bug #78183	Similar query with aggregate functions returns inconsistent result
Submitted:	24 Aug 2015 9:01	Modified:	1 Sep 2015 8:37
Reporter:	Su Dylan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=====================
mysql> drop table if exists t1; create table t1(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1),(1),(2),(2),(2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(c1) from t1 group by c1 having count(count(c1)) > 1;
ERROR 1111 (HY000): Invalid use of group function
mysql> select count(c1) c2 from t1 group by c1 having count(c2) > 1;
+----+
| c2 |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.00 sec)

mysql> select count(c1) from t1 group by c1 having max(count(c1)) > 1;
ERROR 1111 (HY000): Invalid use of group function
mysql> select count(c1) c2 from t1 group by c1 having max(c2) > 1;
Empty set (0.00 sec)

mysql>
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Questions:
=========================
1. SInce the following two SQLs have the same function. Why do they have different result?  One fails with error and the other succeeds.

mysql> select count(c1) from t1 group by c1 having count(count(c1)) > 1;
ERROR 1111 (HY000): Invalid use of group function
mysql> select count(c1) c2 from t1 group by c1 having count(c2) > 1;
+----+
| c2 |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.00 sec)

2. For the following SQL, the following output is expected, since max(c2) which is 3 matches max(c2> > 1. Is this a bug?
+----+
| c2 |
+----+
|  2 |
|  3 |
+----+

mysql> select count(c1) c2 from t1 group by c1 having max(c2) > 1;
Empty set (0.00 sec)

How to repeat:
drop table if exists t1; create table t1(c1 int);
insert into t1 values(1),(1),(2),(2),(2);
select count(c1) from t1 group by c1 having count(count(c1)) > 1;
select count(c1) c2 from t1 group by c1 having count(c2) > 1;
select count(c1) from t1 group by c1 having max(count(c1)) > 1;
select count(c1) c2 from t1 group by c1 having max(c2) > 1;

Suggested fix:
1. The following 2 SQLs have the same result:
select count(c1) from t1 group by c1 having count(count(c1)) > 1;
select count(c1) c2 from t1 group by c1 having count(c2) > 1;

2. " select count(c1) c2 from t1 group by c1 having max(c2) > 1 " should return 

+----+
| c2 |
+----+
|  2 |
|  3 |
+----+

Hi Su Dylan,

Thank you for the report.
Imho this is by design and expected behavior that you cannot nest aggregate functions.

[root@cluster-repo ~]# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1; create table t1(c1 int);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into t1 values(1),(1),(2),(2),(2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(c1) from t1 group by c1 having count(count(c1)) > 1;
ERROR 1111 (HY000): Invalid use of group function
mysql> select count(c1) c2 from t1 group by c1 having count(c2) > 1;
+----+
| c2 |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.00 sec)

mysql>

###
Postgres also complains:

-bash-4.2$ psql
psql (9.4.0)
Type "help" for help.

postgres=# drop table t1;
DROP TABLE
postgres=# create table t1(c1 int);
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# insert into t1 values(2);
INSERT 0 1
postgres=# insert into t1 values(2);
INSERT 0 1
postgres=# insert into t1 values(2);
INSERT 0 1
postgres=# select * from t1;
 c1 
----
  1
  1
  2
  2
  2
(5 rows)

postgres=# select count(c1) from t1 group by c1 having count(count(c1)) > 1;
ERROR:  aggregate function calls cannot be nested
LINE 1: select count(c1) from t1 group by c1 having count(count(c1))...
                                                          ^
postgres=# select count(c1) as c2 from t1 group by c1 having count(c1) > 1;
 c2 
----
  2
  3
(2 rows)

Thanks,
Umesh

Hi Umesh,
Thanks for the feedback. You answers the first question well.

How about the second question?
=====
2. For the following SQL, the following output is expected, since max(c2) which is 3 matches max(c2> > 1. Is this a bug?
+----+
| c2 |
+----+
|  2 |
|  3 |
+----+

mysql> select count(c1) c2 from t1 group by c1 having max(c2) > 1;
Empty set (0.00 sec)

Thanks!