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

Window functions: HAVING and GROUP BY

Details

    • 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

          is part of

          Task - A task that needs to be done. MDEV-6115 window functions as in the SQL standard

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            psergei Sergei Petrunia made changes -
            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)
            ```             		{noformat}
            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;
            {noformat}

            {noformat}
            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)
            {noformat}
            Now, add window function, and see that HAVING is not honored anymore:

            {noformat}
            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)
            {noformat}
            ratzpo Rasmus Johansson (Inactive) made changes -
            Fix Version/s 10.2 [ 14601 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.0-10 [ 46 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.0-10 [ 46 ] 10.2.0-10, 10.2.0-11 [ 46, 47 ]
            psergei Sergei Petrunia made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            Component/s Optimizer - Window functions [ 13502 ]
            Component/s Optimizer [ 10200 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 74653 ] MariaDB v4 [ 132820 ]

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              4 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.