[MDEV-19039] Wrong result from query, using window function and GROUP BY .. WITH ROLLUP Created: 2019-03-25  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (i int);
 
INSERT INTO t1 VALUES (1), (1), (2), (2), (3), (3);
 
 
 
SELECT i, SUM(i) OVER () FROM t1 GROUP BY i ;
 
SELECT i, SUM(i) OVER () FROM t1 GROUP BY i WITH ROLLUP;


MariaDB [test]> SELECT i, SUM(i) OVER () FROM t1 GROUP BY i;
 
+------+----------------+
 
| i    | SUM(i) OVER () |
 
+------+----------------+
 
|    1 |              6 |
 
|    2 |              6 |
 
|    3 |              6 |
 
+------+----------------+
 
3 rows in set (0.005 sec)
 
 
 
MariaDB [test]> SELECT i, SUM(i) OVER () FROM t1 GROUP BY i WITH ROLLUP;
 
+------+----------------+
 
| i    | SUM(i) OVER () |
 
+------+----------------+
 
|    1 |              9 |
 
|    2 |              9 |
 
|    3 |              9 |
 
| NULL |              9 |
 
+------+----------------+
 
4 rows in set (0.003 sec)



 Comments   
Comment by Alice Sherepa [ 2019-03-28 ] 

MariaDB [test]> CREATE TABLE t1(i int);
 
Query OK, 0 rows affected (0.036 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 VALUES  (1),(2),(3),(4),(5);
 
Query OK, 5 rows affected (0.008 sec)
 
Records: 5  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select i, sum(i) over (order by i) from t1 group by i;
 
+------+--------------------------+
 
| i    | sum(i) over (order by i) |
 
+------+--------------------------+
 
|    1 |                        1 |
 
|    2 |                        3 |
 
|    3 |                        6 |
 
|    4 |                       10 |
 
|    5 |                       15 |
 
+------+--------------------------+
 
5 rows in set (0.002 sec)
 
 
 
MariaDB [test]> select i, sum(i) over (order by i) from t1 group by i with rollup;
 
+------+--------------------------+
 
| i    | sum(i) over (order by i) |
 
+------+--------------------------+
 
| NULL |                        5 |
 
|    1 |                        6 |
 
|    2 |                        8 |
 
|    3 |                       11 |
 
|    4 |                       15 |
 
|    5 |                       20 |
 
+------+--------------------------+
 
6 rows in set (0.001 sec)

Comment by Varun Gupta (Inactive) [ 2020-07-10 ]

Here trying a query without window function with ROLLUP

MariaDB [test]> CREATE TABLE t1 (i int, j int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 VALUES (1,1), (1,1), (2,2), (2,2), (3,3), (3,3);
 
Query OK, 6 rows affected (0.01 sec)
 
Records: 6  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT i, i, SUM(i) FROM t1 GROUP BY i WITH ROLLUP;
 
+------+------+--------+
 
| i    | i    | SUM(i) |
 
+------+------+--------+
 
|    1 |    1 |      2 |
 
|    2 |    2 |      4 |
 
|    3 |    3 |      6 |
 
| NULL |    3 |     12 |
 
+------+------+--------+
 
4 rows in set (0.01 sec)

So even here the second column i returns the incorrect value. It should be NULL according to my understanding.

Comment by Varun Gupta (Inactive) [ 2020-07-10 ]

The above limitation is something that we can see with the window function too.

select i, sum(i) over (order by i) from t1 group by i WITH ROLLUP;

In the prepare phase of the query the elements in the ORDER BY list of the window function are added to the all_fields list. But currently there is no implementation to show that the same column i added from the ORDER BY list is part of the GROUP BY clause also.

Generated at Thu Feb 08 08:48:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.