[MDEV-9941] Window functions: two functions, one (wrong) sorting is used Created: 2016-04-19  Updated: 2016-09-23  Resolved: 2016-09-23

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

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

Consider an example:

create table t1 (
 
  a int,
 
  b int,
 
  c int
 
);
 
 
 
insert into t1 values 
 (10, 1, 1),
 
 (10, 3, 10),
 
 (10, 1, 10),
 
 (10, 3, 100),
 
 (10, 5, 1000),
 
 (10, 1, 100);


explain format=json
 
select 
  a,b,c,
 
  row_number() over (partition by a),
 
  row_number() over (partition by a, b)
 
from t1;

The query plan is incorrect. We can't just sort by "a":

  "query_block": {
 
    "select_id": 1,
 
    "window_functions_computation": {
 
      "sorts": {
 
        "filesort": {
 
          "sort_key": "t1.a"
 
        }
 
      },
 
      "temporary_table": {
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "ALL",
 
          "rows": 6,
 
          "filtered": 100
 
        }
 
      }
 
    }
 
  }



 Comments   
Comment by Sergei Petrunia [ 2016-04-19 ]

And running the SELECT produces a wrong result (added "ORDER BY b" to the query so it's easier to understand): 

MariaDB [j81]> select    a,b,c,   row_number() over (partition by a),   row_number() over (partition by a, b) from t1 order by b;
 
+------+------+------+------------------------------------+---------------------------------------+
 
| a    | b    | c    | row_number() over (partition by a) | row_number() over (partition by a, b) |
 
+------+------+------+------------------------------------+---------------------------------------+
 
|   10 |    1 |   10 |                                  1 |                                     1 |
 
|   10 |    1 |    1 |                                  2 |                                     2 |
 
|   10 |    1 |   10 |                                  6 |                                     1 |
 
|   10 |    1 |  100 |                                  7 |                                     2 |
 
|   10 |    1 |    1 |                                 10 |                                     1 |
 
|   10 |    3 |  100 |                                  3 |                                     1 |
 
|   10 |    3 |   10 |                                  4 |                                     2 |
 
|   10 |    3 |  100 |                                  8 |                                     1 |
 
|   10 |    3 |   10 |                                 11 |                                     1 |
 
|   10 |    5 | 1000 |                                  5 |                                     1 |
 
|   10 |    5 | 1000 |                                  9 |                                     1 |
 
+------+------+------+------------------------------------+---------------------------------------+

Comment by Igor Babaev [ 2016-04-19 ]

Sergey,

It means only that you have to sort by (a,b). Choose the correct order list.

Comment by Igor Babaev [ 2016-09-23 ]

This bug apparently was fixed some time ago in 10.2
I added the test case for it into win.test.

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