Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"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

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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:

fixes for bug #6624, ie handle orderby with aggregate expressions

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant