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

Window functions: HAVING and GROUP BY

    Details

    • Sprint:
      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

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: