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 added a comment -

            We also have the following problem with window functions using min/max aggregations:

            create table t1 (i int, b int);
            insert into t1 values
              (1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8);
            MariaDB [test]> select b, min(i+10) over (partition by b) as f 
                ->   from t1 as tt
                -> order by i;
            +------+------+
            | b    | f    |
            +------+------+
            |    1 |   20 |
            |    1 |   20 |
            |    1 |   20 |
            |    4 |   20 |
            |    4 |   20 |
            |    4 |   20 |
            |    8 |   20 |
            |    8 |   20 |
            |    8 |   20 |
            |    8 |   20 |
            +------+------+
            

            The cause of both problems is the same: splitting arguments of aggregate functions used in window functions has not been done
            correctly unless the argument is just a field. In our cases the argument either an Item_direct_ref or Item_func_plus.

            igor Igor Babaev added a comment - We also have the following problem with window functions using min/max aggregations: create table t1 (i int, b int); insert into t1 values (1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8); MariaDB [test]> select b, min(i+10) over (partition by b) as f -> from t1 as tt -> order by i; +------+------+ | b | f | +------+------+ | 1 | 20 | | 1 | 20 | | 1 | 20 | | 4 | 20 | | 4 | 20 | | 4 | 20 | | 8 | 20 | | 8 | 20 | | 8 | 20 | | 8 | 20 | +------+------+ The cause of both problems is the same: splitting arguments of aggregate functions used in window functions has not been done correctly unless the argument is just a field. In our cases the argument either an Item_direct_ref or Item_func_plus.
            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 added a comment -

            The fix for this bug was pushed into the 10.2 tree.

            igor Igor Babaev added a comment - The fix for this bug was pushed into the 10.2 tree.
            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.