[MDEV-9780] Window functions: interplay between window function and other constructs Created: 2016-03-23  Updated: 2016-10-03  Resolved: 2016-10-03

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

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
Sprint: 10.2.0-9, 10.2.0-10, 10.2.0-11

 Description   

Study (and fix) the interplay between window functions and other SQL constructs, like ORDER BY ... LIMIT, DISTINCT, etc.

Known things:

  • LIMIT should be applied AFTER the window functions are computed (that is, window function computation should see rows that are cut off by LIMIT)
  • DISTINCT must not be converted into GROUP BY when window functions are present
  • need to check window functions inside derived table and/or union. Item_window_func::update_field() is not implemented, is this ok?

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

Example from Peter:

create table t1 (s1 int, s2 char(5));
 
insert into t1 values (1,'a');
 
insert into t1 values (null,null);
 
insert into t1 values (1,null);
 
insert into t1 values (null,'a');
 
insert into t1 values (2,'b');
 
insert into t1 values (-1,'');


select *,
 
         row_number() over (order by s1)
 
         - row_number() over (order by s1) as X from t1;

This crashes, because "split_sum_func" process is not done correctly for window functions.

  • The fact that split_sum_func is not invoked for the Item_func_minus is a trivial typo.
  • But if I make it to be invoked, it does nothing
  • What should Item::split_sum_func do when it is invoked for a Item_window_func that's an argument of an expression? Should we create an Item_direct_ref? Or just inject the item into the select list?
Comment by Sergei Petrunia [ 2016-04-10 ]

The above is now resolved.

Comment by Sergei Petrunia [ 2016-04-10 ]

The problem with DISTINCT being converted into GROUP BY is resolved

Comment by Sergei Petrunia [ 2016-04-10 ]

We seem to still have a problem with queries that have aggregates but do not have GROUP BY clause.

This is not critical because aggregages w/o GROUP BY means the query output is just one row. It is not very meaningful to compute window functions over a resultset that has just one row (although this is allowed and should be fixed).

Comment by Sergei Petrunia [ 2016-09-28 ]

Marking as fixed (in 10.2.1 version)

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