[MDEV-11867] Invalid use of group function - Created: 2017-01-21  Updated: 2021-02-16  Resolved: 2017-02-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.3
Fix Version/s: 10.2.4

Type: Bug Priority: Major
Reporter: the6campbells Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15313 Error with aggregate function inside ... Stalled
relates to MDEV-22814 SIGSEGV in set_field_to_null_with_con... Stalled
relates to MDEV-24875 Nested GROUP_CONCAT in stored functio... Confirmed

 Description   

Following windowed aggregate cannot be expressed.

SELECT
 
    `C1`, 
    `C2`, 
    SUM(`C3`), 
    SUM(SUM(`C3`))
 
        OVER(
 
            PARTITION BY
 
                `C1`
 
        )
 
FROM
 
    `TOLAP` 
GROUP BY 
    `C1`, 
    `C2`
 
 
 
Error: Invalid use of group function
 
SQLState:  HY000
 
ErrorCode: 1111



 Comments   
Comment by Elena Stepanova [ 2017-01-23 ]

It's unrelated to window functions. You can't use an aggregate function as an argument for another aggregate function:

MariaDB [test]> select sum(1);
 
+--------+
 
| sum(1) |
 
+--------+
 
|      1 |
 
+--------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select sum(sum(1));
 
ERROR 1111 (HY000): Invalid use of group function

Comment by the6campbells [ 2017-01-23 ]

SQL-OLAP addendum provides for an aggregate input as shown.

Does this mean that your 10.2.x release will not support this style of aggregation by design which is supported in a large # of systems that support SQL-OLAP?

Vendors that will support this specification include: DB2 family, ORACLE, Postgres, Teradata, SQL Server, .....

C1 C2 3 4
AAA AA 25 50
AAA AB 25 50
BBB BB 20 20
CCC CC 30 30
DDD DD 40 40
<null> <null> 50 50

Comment by Elena Stepanova [ 2017-01-23 ]

cvicentiu, serg, your opinions?

Comment by the6campbells [ 2017-02-01 ]

So if MariaDB 10.2.x will not support grouped-window queries will it be added to another release in 2017?

In essence, one would be forced to apply a query transformation to push the grouped query into a derived table and apply the windowed aggregate in the outer block.

Comment by Igor Babaev [ 2017-02-01 ]

We are planning to fix it in 10.2 now.

Comment by Igor Babaev [ 2017-02-02 ]

The fix for this bug was pushed into the 10.2.tree.

Generated at Thu Feb 08 07:53:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.