Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
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)
|
Attachments
Issue Links
- is part of
-
-
- Closed
-
Activity
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.
Consider the query without the window function:
selectb,max(a) as MXfrom t1group by bhaving 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.
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: