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

Incorrect message "Cannot use an aggregate or window function in a GROUP BY clause." when field is from outside context [CORE6167] #6415

Open
firebird-automations opened this issue Oct 22, 2019 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

do not analyse logic here, this is only a sample on system tables

-----------------------
SELECT
R.RDB$RELATION_NAME,
(
SELECT
SUM(RF.RDB$FIELD_ID)
FROM
RDB$RELATION_FIELDS RF
WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME
GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */
)
FROM
RDB$RELATIONS R

-----------------------

SQL error code = -104
Cannot use an aggregate or window function in a GROUP BY clause.
---------------------------

Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate.

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: do nota analyse logic here, this is only a sample on system tables

-----------------------
SELECT
R.RDB$RELATION_NAME,
(
SELECT
SUM(RF.RDB$FIELD_ID)
FROM
RDB$RELATION_FIELDS RF
WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME
GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */
)
FROM
RDB$RELATIONS R

-----------------------

SQL error code = -104
Cannot use an aggregate or window function in a GROUP BY clause.
---------------------------

Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate.

=>

do not analyse logic here, this is only a sample on system tables

-----------------------
SELECT
R.RDB$RELATION_NAME,
(
SELECT
SUM(RF.RDB$FIELD_ID)
FROM
RDB$RELATION_FIELDS RF
WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME
GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */
)
FROM
RDB$RELATIONS R

-----------------------

SQL error code = -104
Cannot use an aggregate or window function in a GROUP BY clause.
---------------------------

Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate.

summary: Incorrect message "Cannot use an aggregate or window function in a GROUP BY clause." when field if from outside context => Incorrect message "Cannot use an aggregate or window function in a GROUP BY clause." when field is from outside context

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: do not analyse logic here, this is only a sample on system tables

-----------------------
SELECT
R.RDB$RELATION_NAME,
(
SELECT
SUM(RF.RDB$FIELD_ID)
FROM
RDB$RELATION_FIELDS RF
WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME
GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */
)
FROM
RDB$RELATIONS R

-----------------------

SQL error code = -104
Cannot use an aggregate or window function in a GROUP BY clause.
---------------------------

Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate.

=>

do not analyse logic here, this is only a sample on system tables

-----------------------
SELECT
R.RDB$RELATION_NAME,
(
SELECT
SUM(RF.RDB$FIELD_ID)
FROM
RDB$RELATION_FIELDS RF
WHERE
RF.RDB$RELATION_NAME=R.RDB$RELATION_NAME
GROUP BY R.RDB$RELATION_NAME /* here should be RF not R */
)
FROM
RDB$RELATIONS R

-----------------------

SQL error code = -104
Cannot use an aggregate or window function in a GROUP BY clause.
---------------------------

Message should be maybe "cannot 'group by' by outside context" or something else but current one is not appropriate.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Karol,

IMO, the message is correct.

The sub-query is evaluated on it's own, as such it is invalid -- in order to GROUP BY a column, the column must in the return set (i.e. the sub-query should read SELECT R.RDB$RELATION_NAME, SUM(RF.RDB$FIELD_ID)...

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Sean, maybe my understanding of English is to low.

I understand it as:
"Cannot use an aggregate"
"Cannot use a window function"

"R.RDB$RELATION_NAME" is not an aggregate i understand aggreagate by Sum, AVG ...
If the message here will be "cannot group by "R.RDB$RELATION_NAME"" then yes, i will understand it clearly, but now the message is confusing.

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

No branches or pull requests

1 participant