[MDEV-11745] Wrong results upon using window function with min/max aggregation Created: 2017-01-08  Updated: 2017-02-10  Resolved: 2017-02-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: 10.2-rc

Issue Links:
Relates
relates to MDEV-9896 Testing for window functions Open

 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)



 Comments   
Comment by Igor Babaev [ 2017-02-08 ]

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.

Comment by Igor Babaev [ 2017-02-10 ]

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

Generated at Thu Feb 08 07:52:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.