Details

    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
      

      Attachments

        Issue Links

          Activity

            elenst Elena Stepanova added a comment - - edited

            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
            

            elenst Elena Stepanova added a comment - - edited 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
            the6campbells the6campbells added a comment -

            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

            the6campbells the6campbells added a comment - 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

            cvicentiu, serg, your opinions?

            elenst Elena Stepanova added a comment - cvicentiu , serg , your opinions?
            the6campbells the6campbells added a comment -

            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.

            the6campbells the6campbells added a comment - 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.
            igor Igor Babaev added a comment -

            We are planning to fix it in 10.2 now.

            igor Igor Babaev added a comment - We are planning to fix it in 10.2 now.
            igor Igor Babaev added a comment -

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

            igor Igor Babaev added a comment - The fix for this bug was pushed into the 10.2.tree.

            People

              igor Igor Babaev
              the6campbells the6campbells
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.