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

Wrong results upon using window function with min/max aggregation

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

            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Summary Wrong result while using aggregate window function on subquery Wrong result upon using aggregate window function selecting from subquery
            elenst Elena Stepanova made changes -
            Labels 10.2-rc
            igor Igor Babaev made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Igor Babaev [ igor ]
            igor Igor Babaev made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            igor Igor Babaev made changes -
            Summary Wrong result upon using aggregate window function selecting from subquery Wrong results upon using window function withmin/max aggregation
            igor Igor Babaev made changes -
            Summary Wrong results upon using window function withmin/max aggregation Wrong results upon using window function with min/max aggregation
            igor Igor Babaev made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            igor Igor Babaev made changes -
            Fix Version/s 10.2.4 [ 22116 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79127 ] MariaDB v4 [ 151502 ]

            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.