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)

    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

              • Assignee:
                varun Varun Gupta
                Reporter:
                alice Alice Sherepa
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: