[MDEV-19039] Wrong result from query, using window function and GROUP BY .. WITH ROLLUP - Jira

XML

Word

Printable

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6, 11.4, 11.8, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: 10.11, 11.4, 11.8
Component/s: Optimizer
Labels:
None

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)

is duplicated by

MDEV-27143 Different result when group by with correct function

relates to

MDEV-10253 select field group by field with rollup

MDEV-28536 Incorrect sequence in view column processing created as select with ROLLUP and repeated columns in GROUP BY

MDEV-29743 how to apply coalesce or ifnull to a groupby

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