Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10859

Wrong result of aggregate window function in query with HAVING and no ORDER BY

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration