[MDEV-9787] Window functions: HAVING and GROUP BY Created: 2016-03-24  Updated: 2016-04-14  Resolved: 2016-04-10

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: 1
Labels: None

Issue Links:
PartOf
is part of MDEV-6115 window functions as in the SQL standard Closed
Sprint: 10.2.0-10, 10.2.0-11

 Description    

create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
create table t1 (a  int, b int, c int); 
insert into t1 select a,a,a from ten;


select b,max(a) as MX from t1 group by b having MX in (3,5,7);
 
+------+------+
 
| b    | MX   |
 
+------+------+
 
|    3 |    3 |
 
|    5 |    5 |
 
|    7 |    7 |
 
+------+------+
 
3 rows in set (0.00 sec)

Now, add window function, and see that HAVING is not honored anymore:

select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
 
+------+------+--------------------------+
 
| b    | MX   | rank() over (order by b) |
 
+------+------+--------------------------+
 
|    0 |    0 |                        1 |
 
|    1 |    1 |                        2 |
 
|    2 |    2 |                        3 |
 
|    3 |    3 |                        4 |
 
|    4 |    4 |                        5 |
 
|    5 |    5 |                        6 |
 
|    6 |    6 |                        7 |
 
|    7 |    7 |                        8 |
 
|    8 |    8 |                        9 |
 
|    9 |    9 |                       10 |
 
+------+------+--------------------------+
 
10 rows in set (0.00 sec)



 Comments   
Comment by Sergei Petrunia [ 2016-03-27 ]

Consider the query without the window function: 

select 
  b,
 
  max(a) as MX 
from t1 
group by b 
having MX in (3,5,7)

Operations:
1. scan table t1
the obtained rows are used for group-by operation
(the groups are interleaved)

2. call filesort
2.1 filesort reads data from temp. table
and applies the "MX in (3,5,6)" condition

Now, let's add a window function into the select list.

  • Window function should be computed after grouping
  • but before the sorting.

The problem is that sorting step also checks the HAVING clause.
According to the standard, HAVING must be checked before window functions are
computed.

Two possible options:

  • Make window function code check HAVING. The problem is, every cursor will need to check HAVING clause.
  • Create another temp.table which will only hold that have passed the HAVING.
Comment by Sergei Petrunia [ 2016-03-28 ]

Third option: so, subsequent sorting step also checks the HAVING clause. Let all window functions' filesort() calls check the HAVING clause, too.

I'm not sure if this has the best performance but it's easiest to implement.

Comment by Sergei Petrunia [ 2016-04-10 ]

This was fixed by d146c2cedc88e6d8728584b83861bd9b677a44a3

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