MySQL Bugs: #78360: More than 1 row is returned when using "having count(alias)"

Bug #78360	More than 1 row is returned when using "having count(alias)"
Submitted:	8 Sep 2015 6:14	Modified:	8 Sep 2015 12:15
Reporter:	Su Dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.22, 5.6.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

Result:
=======
mysql> drop table if exists t1; create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)
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) alias from t1 group by c1 having count(alias) > 1;
+-------+
| alias |
+-------+
|     2 |
|     3 |
+-------+
2 rows in set (0.00 sec)

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

Problem:
========
Since there is no group by on "alias" and "having count(alias)" is used , 1 row is expected to be returned.

Similar to the following result:
mysql> select c1+1 alias from t1 having count(alias) > 1;
+-------+
| alias |
+-------+
|     2 |
+-------+
1 row in 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) alias from t1 group by c1 having count(alias) > 1;

Suggested fix:
No more than 1 row is returned for the following SQL:
select count(c1) alias from t1 group by c1 having count(alias) > 1;

Hello Su Dylan,

Thank you for the report.

Thanks,
Umesh

Hello. Query's result is correct.
select count(c1) alias from t1 group by c1 having count(alias) > 1;
does this, per the SQL Standard:
- partition the rows of t1 into sets, each set having rows with the same value of t1; that makes two sets: {1,1} and {2,2,2}.
- collapse every group into one row, generating the count of each group; result is two rows, having {2},{3} as values for column "alias". You can see this temporary result if you run your query without HAVING.
- apply HAVING; 2>1 and 3>1 are satisfied, both rows are returned.

On the other hand, this one:
select c1+1 alias from t1 having count(alias) > 1;
returns one row because HAVING, if no GROUP BY, collapses all rows into one (like if there was an implicit "GROUP BY some_constant").

Looks like I need a new pair of glasses. Please ignore my post above; I read your query too fast, thinking it had "HAVING alias>1" :-/
The problematic query has "HAVING COUNT(alias)>1"
which means COUNT(COUNT(c1)), and this double aggregation should not be allowed by MySQL.
So, it is a bug. An error should be emitted.

So, to recap:
the correct behaviour is to reject
select count(c1) alias from t1 group by c1 having count(alias) > 1;

Observed behaviours:
- 5.6 default: query accepted
- 5.6 + only_full_group_by: query rejected, the cause of rejection is
that only_full_group_by forbids any alias in HAVING anyway
- 5.7 with or without only_full_group_by: query accepted (not so
surprising as only_full_group_by has been changed to accept aliases in
HAVING in 5.7).
- No version properly detects that this is count(count).