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

          relates to

          Task - A task that needs to be done. MDEV-9896 Testing for window functions

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            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 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.

            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.