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

Wrong result with aggregation function, used inside window function

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t (a INT);
      INSERT INTO t VALUES(1),(2),(3),(4),(4);
      SELECT a, SUM(a), SUM(a) OVER (ORDER BY SUM(a)) FROM t GROUP BY a;
      SELECT a, SUM(a), SUM(a) OVER (ORDER BY SUM(a)+1) FROM t GROUP BY a; #  expected result 1,3,6,10
      SELECT SUM(a) OVER (PARTITION BY SUM(a)) FROM t GROUP BY a;
      SELECT SUM(a) OVER (PARTITION BY SUM(a)+1) FROM t GROUP BY a; #  expected result 1,3,6,10
      

      MariaDB [test]> SELECT a, SUM(a), SUM(a) OVER (ORDER BY SUM(a)) FROM t GROUP BY a;
      +------+--------+-------------------------------+
      | a    | SUM(a) | SUM(a) OVER (ORDER BY SUM(a)) |
      +------+--------+-------------------------------+
      |    1 | 1      | 1                             |
      |    2 | 2      | 3                             |
      |    3 | 3      | 6                             |
      |    4 | 8      | 10                            |
      +------+--------+-------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT a, SUM(a), SUM(a) OVER (ORDER BY SUM(a)+1) FROM t GROUP BY a;
      +------+--------+---------------------------------+
      | a    | SUM(a) | SUM(a) OVER (ORDER BY SUM(a)+1) |
      +------+--------+---------------------------------+
      |    1 | 1      | 10                              |
      |    2 | 2      | 10                              |
      |    3 | 3      | 10                              |
      |    4 | 8      | 10                              |
      +------+--------+---------------------------------+
      4 rows in set (0.01 sec)
       
      MariaDB [test]> SELECT SUM(a) OVER (PARTITION BY SUM(a)) FROM t GROUP BY a;
      +-----------------------------------+
      | SUM(a) OVER (PARTITION BY SUM(a)) |
      +-----------------------------------+
      | 1                                 |
      | 2                                 |
      | 3                                 |
      | 4                                 |
      +-----------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT SUM(a) OVER (PARTITION BY SUM(a)+1) FROM t GROUP BY a;
      +-------------------------------------+
      | SUM(a) OVER (PARTITION BY SUM(a)+1) |
      +-------------------------------------+
      | 10                                  |
      | 10                                  |
      | 10                                  |
      | 10                                  |
      +-------------------------------------+
      4 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: