Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19064

Inconsistent behavior while using nested window functions (first_value, last_value, lead,lag)

    XMLWordPrintable

Details

    Description

      1))

       
      MariaDB [test]> create table t1 (a int, b int);
      Query OK, 0 rows affected (0.048 sec)
       
      MariaDB [test]> insert into t1 values (1,3), (2,2), (3,1), (1,3), (2,2), (3,1);
      Query OK, 6 rows affected (0.017 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select  first_value(sum(a)) over () from t1;
      ERROR 1111 (HY000): Invalid use of group function
       
      MariaDB [test]> select  first_value(sum(a) over ()) over () from t1; 
      +-------------------------------------+
      | first_value(sum(a) over ()) over () |
      +-------------------------------------+
      |                                  12 |
      |                                  12 |
      |                                  12 |
      |                                  12 |
      |                                  12 |
      |                                  12 |
      +-------------------------------------+
      6 rows in set (0.002 sec)
       
      MariaDB [test]> select  first_value(sum(a) over (order by null)) over () from t1;
      +--------------------------------------------------+
      | first_value(sum(a) over (order by null)) over () |
      +--------------------------------------------------+
      |                                             NULL |
      |                                               12 |
      |                                               12 |
      |                                               12 |
      |                                               12 |
      |                                               12 |
      +--------------------------------------------------+
      6 rows in set (0.002 sec)
       
      MariaDB [test]> select  first_value(sum(a) over (order by a)) over () from t1;
      ERROR 2013 (HY000): Lost connection to MySQL server during query         ###MDEV MDEV-15180
      
      


      Postgres returns error " window function calls cannot be nested "

      2))

      create table t1 (a int);
      insert into t1 values (1), (2), (3);
       
      select  first_value(avg(a)) over () from t1;
      

      MariaDB [test]> select  first_value(avg(a)) over () from t1;
      ERROR 1111 (HY000): Invalid use of group function
      

      Mysql 8.0/Postgres 9.6:

      mysql> select  first_value(avg(a)) over () from t1;
      +-----------------------------+
      | first_value(avg(a)) over () |
      +-----------------------------+
      |                      2.0000 |
      +-----------------------------+
      1 row in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.