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
-
Activity
| Labels | 10.2-rc |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Summary | Wrong result of aggregate window function in query with ORDER BY and HAVING | Wrong result of aggregate window function in query with HAVING and no ORDER BY |
| 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._ {code:sql} 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); {code} {code:sql|title=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 | +---------+-------+--------+-----------------------------------------+ {code} {code:sql|title=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 | +---------+-------+--------+-----------------------------------------+ {code} {code:title=HAVING, no ORDER BY -- OK} 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) {code} {code:sql|title=ORDER BY and HAVING -- wrong?} 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) {code} |
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._ {code:sql} 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); {code} {code:sql|title=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 | +---------+-------+--------+-----------------------------------------+ {code} {code:sql|title=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 | +---------+-------+--------+-----------------------------------------+ {code} {code:title=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) {code} {code:sql|title=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) {code} |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.4 [ 22116 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 77168 ] | MariaDB v4 [ 150945 ] |