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

Wrong result upon using FIRST_VALUE with a window frame

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

            cvicentiu Vicențiu Ciorbaru added a comment - Fixed with: http://lists.askmonty.org/pipermail/commits/2017-February/010632.html
            cvicentiu Vicențiu Ciorbaru added a comment - Pushed commits: https://github.com/mariadb/server/commit/57341852b5b1e40b1f92e248d84f95de988022c0 https://github.com/mariadb/server/commit/5bf338435aae358b2661ce2dc04b5a70d3d0c783

            People

              cvicentiu Vicențiu Ciorbaru
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.