[MDEV-9877] Window functions: wrong sort criteria is used Created: 2016-04-06  Updated: 2016-04-14  Resolved: 2016-04-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-6115 window functions as in the SQL standard Closed

 Description    

create table t1 (a int, b int, c int);
 
insert into t1 values (1,3,1),(2,2,1),(3,1,1);


explain format=json
 
select 
  rank() over (partition by c order by a),
 
  count(*) over (partition by c)
 
from t1;

EXPLAIN shows:

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

In debugger I put a breakpoint into the loop in order_window_funcs_by_window_specs and see:

(gdb) p dbug_print_item(prev)
 
  $103 = 0x555556d7e280 "count(*) over (partition by c)"
 
(gdb) p prev->marker
 
  $104 = 7
 
(gdb) p dbug_print_item(curr)
 
  $105 = 0x555556d7e280 "rank() over (partition by c order by a)"
 
(gdb) p curr->marker
 
  $106 = 0

Sorting on just t1.c is wrong. RANK() function needs sorting by t1.c, t1.a.



 Comments   
Comment by Igor Babaev [ 2016-04-06 ]

Sergey,

This is not a bug.
The sort order for the sub-sequence of window functions starting from the element marked by SORTORDER_CHANGE_FLAG
up to the next element marked by SORTORDER_CHANGE_FLAG must be taken from the last element of the
sub-sequence (not from the first one).

Comment by Sergei Petrunia [ 2016-04-06 ]

Ok, I was using the list incorrectly, then. Will fix now.

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