You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
"Cannot use non GROUP BY column in query results without an aggregate function" when using aggregate function in both HAVING and ORDER BY clauses.
#6624
Closed
monetdb-team opened this issue
Nov 30, 2020
· 0 comments
Date: 2018-07-11 16:30:47 +0200
From: Chris Bing <<chris.bing>>
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)
CC: @njnes
Last updated: 2018-08-31 13:23:19 +0200
Comment 26543
Date: 2018-07-11 16:30:47 +0200
From: Chris Bing <<chris.bing>>
We are getting a parser error with what we believe to be valid SQL.
This is a minimal repro using the standard sys.columns table, based on a much more complicated SQL query on our dataset.
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type HAVING COUNT(id)>10 ORDER BY COUNT(id) DESC;
Cannot use non GROUP BY column 'type' in query results without an aggregate function
This was discovered in a build based on Mar2018 just before the SP1 release. I have tested that the behaviour is the same on the latest (to revision 66818) Mar2018. It does not occur on the Jun2016 release, we haven't tested further.
Replace the expression in the order by with the column number, and it works:
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type HAVING COUNT(id)>10 ORDER BY 2 DESC;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| smallint | 70 |
| bigint | 57 |
| clob | 39 |
| boolean | 28 |
| timestamp | 26 |
| decimal | 18 |
+-----------+------+
8 tuples
Remove the HAVING clause, and it works:
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type ORDER BY COUNT(id) DESC;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| smallint | 70 |
| bigint | 57 |
| clob | 39 |
| boolean | 28 |
| timestamp | 26 |
| decimal | 18 |
| tinyint | 10 |
| char | 5 |
| oid | 4 |
| hugeint | 1 |
+-----------+------+
12 tuples
Remove the ORDER BY clause and it works:
sql>SELECT type,COUNT(id) from sys.columns GROUP BY type HAVING COUNT(id)>10;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| boolean | 28 |
| bigint | 57 |
| smallint | 70 |
| clob | 39 |
| timestamp | 26 |
| decimal | 18 |
+-----------+------+
8 tuples
We are investigating whether we can apply the first workaround, but as this is a very complicated query generated by code it may be tricky, and to my mind this is a clear SQL parser bug.
Date: 2018-07-11 16:30:47 +0200
From: Chris Bing <<chris.bing>>
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)
CC: @njnes
Last updated: 2018-08-31 13:23:19 +0200
Comment 26543
Date: 2018-07-11 16:30:47 +0200
From: Chris Bing <<chris.bing>>
We are getting a parser error with what we believe to be valid SQL.
This is a minimal repro using the standard sys.columns table, based on a much more complicated SQL query on our dataset.
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type HAVING COUNT(id)>10 ORDER BY COUNT(id) DESC;
Cannot use non GROUP BY column 'type' in query results without an aggregate function
This was discovered in a build based on Mar2018 just before the SP1 release. I have tested that the behaviour is the same on the latest (to revision 66818) Mar2018. It does not occur on the Jun2016 release, we haven't tested further.
Replace the expression in the order by with the column number, and it works:
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type HAVING COUNT(id)>10 ORDER BY 2 DESC;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| smallint | 70 |
| bigint | 57 |
| clob | 39 |
| boolean | 28 |
| timestamp | 26 |
| decimal | 18 |
+-----------+------+
8 tuples
Remove the HAVING clause, and it works:
sql>SELECT type,COUNT(id) FROM sys.columns GROUP BY type ORDER BY COUNT(id) DESC;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| smallint | 70 |
| bigint | 57 |
| clob | 39 |
| boolean | 28 |
| timestamp | 26 |
| decimal | 18 |
| tinyint | 10 |
| char | 5 |
| oid | 4 |
| hugeint | 1 |
+-----------+------+
12 tuples
Remove the ORDER BY clause and it works:
sql>SELECT type,COUNT(id) from sys.columns GROUP BY type HAVING COUNT(id)>10;
+-----------+------+
| type | L16 |
+===========+======+
| int | 232 |
| varchar | 195 |
| boolean | 28 |
| bigint | 57 |
| smallint | 70 |
| clob | 39 |
| timestamp | 26 |
| decimal | 18 |
+-----------+------+
8 tuples
We are investigating whether we can apply the first workaround, but as this is a very complicated query generated by code it may be tricky, and to my mind this is a clear SQL parser bug.
Comment 26549
Date: 2018-07-18 17:58:29 +0200
From: @njnes
add handling of the group by expressions in the order by part
Comment 26550
Date: 2018-07-18 18:37:21 +0200
From: MonetDB Mercurial Repository <>
Changeset 4ddcff76a52a made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=4ddcff76a52a
Changeset description:
Comment 26556
Date: 2018-07-19 11:12:45 +0200
From: Chris Bing <<chris.bing>>
Thanks Niels, our original query now runs without the error.
The text was updated successfully, but these errors were encountered: