| Bug #78785 | Grouping on aggregated results not always rejected | ||
|---|---|---|---|
| Submitted: | 9 Oct 2015 12:54 | Modified: | 21 Jun 2017 16:35 |
| Reporter: | Knut Anders Hatlen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Jun 2017 16:35]
Paul DuBois
Posted by developer: Fixed in 8.0.3. MySQL accepted a reference to an alias of an aggregated expression defined in an outer query block even when the reference occurred within a GROUP BY subquery where the reference was meaningless.
Description: In general MySQL doesn't allow grouping on aggregated results. This makes sense, since grouping happens before aggregation, so the aggregated values are not available during grouping. For example SELECT a, COUNT(*) AS c FROM t GROUP BY a, c fails with ERROR 42000: Can't group on 'c' Also, if the reference to the aggregated result is hidden inside a subquery in the GROUP BY clause, it is detected: SELECT a, COUNT(*) AS c FROM t GROUP BY a, (SELECT c) fails with ERROR 42S22: Reference 'c' not supported (reference to group function) However, if the reference is hidden inside a HAVING clause in a subquery in the GROUP BY clause, it is not detected. How to repeat: CREATE TABLE t (a CHAR(1) NOT NULL); INSERT INTO t VALUES ('0'), ('1'), ('1'), ('2'); SELECT a, COUNT(*) AS c FROM t GROUP BY a, (SELECT 1 HAVING c); The SELECT statement above should have been rejected, since it's at least as meaningless as the statements that are already rejected. Instead it returns a result.