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

Wrong results upon using window function with min/max aggregation

    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);
      select i, Min(i) OVER (PARTITION BY i) as f from (select * from t1) as tt order by i;
      

      Actual result

      +------+------+
      | i    | f    |
      +------+------+
      |    1 |   10 |
      |    2 |   10 |
      |    3 |   10 |
      |    4 |   10 |
      |    5 |   10 |
      |    6 |   10 |
      |    7 |   10 |
      |    8 |   10 |
      |    9 |   10 |
      |   10 |   10 |
      +------+------+
      10 rows in set (0.00 sec)
      

      Without the subquery, the result is different:

      MariaDB [test]> select i, Min(i) OVER (PARTITION BY i) as f from t1 as tt order by i;
      +------+------+
      | i    | f    |
      +------+------+
      |    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.01 sec)
      

      PostgreSQL 9.4 returns the same for both queries:

      postgres=# select i, Min(i) OVER (PARTITION BY i) as f from t1 as tt order by i;
       i  | f  
      ----+----
        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, Min(i) OVER (PARTITION BY i) as f from (select * from t1) as tt order by i;
       i  | f  
      ----+----
        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

              igor Igor Babaev
              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.