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

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

          relates to

          Task - A task that needs to be done. MDEV-9896 Testing for window functions

          • Major - Major loss of function.
          • Open

          Activity

            Transition Time In Source Status Execution Times
            Vicențiu Ciorbaru made transition -
            Open In Progress
            		138d 21h 47m 1
            Vicențiu Ciorbaru made transition -
            In Progress In Review
            		6d 21h 13m 1
            Vicențiu Ciorbaru made transition -
            In Review Stalled
            		1d 36m 1
            Vicențiu Ciorbaru made transition -
            Stalled Closed
            		1m 12s 1

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.