Issue Details (XML | Word | Printable)

Key: CORE-6167
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 22/Oct/19 08:26 AM   Updated: 22/Oct/19 03:23 PM
Component/s: Engine
Affects Version/s: 3.0.4, 3.0.5
Fix Version/s: None

QA Status: No test


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski made changes - 22/Oct/19 08:29 AM
Field Original Value New Value
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
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.


Sean Leyne made changes - 22/Oct/19 02:24 PM
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.
Sean Leyne added a comment - 22/Oct/19 02:29 PM
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)...

Karol Bieniaszewski added a comment - 22/Oct/19 03:23 PM
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.