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

Wrong result upon using FIRST_VALUE with a window frame

    XMLWordPrintable

    Details

      Description

      Note: results below are from 10.2 348ccb6f038a6c1

      create table t1 (i int);
      insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
      

      MariaDB [test]> select i, first_value(i) OVER ( order by i rows between current row and current row ) from t1;
      +------+-----------------------------------------------------------------------------+
      | i    | first_value(i) OVER ( order by i rows between current row and current row ) |
      +------+-----------------------------------------------------------------------------+
      |    1 |                                                                           1 |
      |    2 |                                                                           1 |
      |    3 |                                                                           1 |
      |    4 |                                                                           1 |
      |    5 |                                                                           1 |
      |    6 |                                                                           1 |
      |    7 |                                                                           1 |
      |    8 |                                                                           1 |
      |    9 |                                                                           1 |
      |   10 |                                                                           1 |
      +------+-----------------------------------------------------------------------------+
      10 rows in set (0.00 sec)
      

      NTH_VALUE works fine:

      MariaDB [test]> select i, nth_value(i,1) OVER ( order by i rows between current row and current row ) from t1;
      +------+-----------------------------------------------------------------------------+
      | i    | nth_value(i,1) OVER ( order by i rows between current row and current row ) |
      +------+-----------------------------------------------------------------------------+
      |    1 |                                                                           1 |
      |    2 |                                                                           2 |
      |    3 |                                                                           3 |
      |    4 |                                                                           4 |
      |    5 |                                                                           5 |
      |    6 |                                                                           6 |
      |    7 |                                                                           7 |
      |    8 |                                                                           8 |
      |    9 |                                                                           9 |
      |   10 |                                                                          10 |
      +------+-----------------------------------------------------------------------------+
      10 rows in set (0.00 sec)
      

      PostgreSQL 9.4 returns the same result for both queries:

      postgres=# select i, first_value(i) OVER ( order by i rows between current row and current row ) from t1;
       i  | first_value 
      ----+-------------
        1 |           1
        2 |           2
        3 |           3
        4 |           4
        5 |           5
        6 |           6
        7 |           7
        8 |           8
        9 |           9
       10 |          10
      (10 rows)
      

      postgres=# select i, nth_value(i,1) OVER ( order by i rows between current row and current row ) from t1;
       i  | nth_value 
      ----+-----------
        1 |         1
        2 |         2
        3 |         3
        4 |         4
        5 |         5
        6 |         6
        7 |         7
        8 |         8
        9 |         9
       10 |        10
      (10 rows)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration