Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
Description
It appears that when both ORDER BY and HAVING are present, HAVING is treated as WHERE clause.
Note: I don't see anything about it in the standard, maybe it's not even a part of the standard at all, the behavior just does not look logical.
create table empsalary (depname varchar(32), empno smallint primary key, salary int); |
insert into empsalary values ('develop',1,5000),('develop',2,4000),('sales',3,'6000'),('sales',4,5000); |
|
No ORDER BY, no HAVING -- OK |
MariaDB [test]> SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; |
+---------+-------+--------+-----------------------------------------+ |
| depname | empno | salary | avg(salary) OVER (PARTITION BY depname) | |
+---------+-------+--------+-----------------------------------------+ |
| develop | 1 | 5000 | 4500.0000 |
|
| develop | 2 | 4000 | 4500.0000 |
|
| sales | 3 | 6000 | 5500.0000 |
|
| sales | 4 | 5000 | 5500.0000 |
|
+---------+-------+--------+-----------------------------------------+ |
|
ORDER BY, no HAVING -- OK |
MariaDB [test]> SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary order by depname; |
+---------+-------+--------+-----------------------------------------+ |
| depname | empno | salary | avg(salary) OVER (PARTITION BY depname) | |
+---------+-------+--------+-----------------------------------------+ |
| develop | 1 | 5000 | 4500.0000 |
|
| develop | 2 | 4000 | 4500.0000 |
|
| sales | 4 | 5000 | 5500.0000 |
|
| sales | 3 | 6000 | 5500.0000 |
|
+---------+-------+--------+-----------------------------------------+ |
|
HAVING, no ORDER BY -- WRONG |
MariaDB [test]> SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary having empno > 1;
|
+---------+-------+--------+-----------------------------------------+
|
| depname | empno | salary | avg(salary) OVER (PARTITION BY depname) |
|
+---------+-------+--------+-----------------------------------------+
|
| develop | 2 | 4000 | 4500.0000 |
|
| sales | 3 | 6000 | 5500.0000 |
|
| sales | 4 | 5000 | 5500.0000 |
|
+---------+-------+--------+-----------------------------------------+
|
3 rows in set (0.00 sec)
|
|
ORDER BY and HAVING -- OK |
MariaDB [test]> SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary having empno > 1 order by depname; |
+---------+-------+--------+-----------------------------------------+ |
| depname | empno | salary | avg(salary) OVER (PARTITION BY depname) | |
+---------+-------+--------+-----------------------------------------+ |
| develop | 2 | 4000 | 4000.0000 |
|
| sales | 4 | 5000 | 5500.0000 |
|
| sales | 3 | 6000 | 5500.0000 |
|
+---------+-------+--------+-----------------------------------------+ |
3 rows in set (0.00 sec) |
Attachments
Issue Links
- relates to
-
MDEV-9896 Testing for window functions
-
- Open
-