We're updating the issue view to help you get more done. 

Wrong result with GROUP BY ... WITH ROLLUP

Description

The following query returns different result between MYSQL and MARIADB (5.5.28):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 CREATE TABLE t(a int,b int,c int, amount1 int, amount2 int); INSERT INTO t (a, b, c, amount1, amount2) values (1,1,1,1,1); INSERT INTO t (a, b, c, amount1, amount2) values (2,2,2,2,1); INSERT INTO t (a, b, c, amount1, amount2) values (2,3,3,3,1); SELECT *, IF(`t_reorder`.`b` IS NULL, @ccount1:=amount1, @ccount1) AS `a_amount1`, IF(`t_reorder`.`b` IS NULL, @dcount1:=amount2, @dcount1) AS `a_amount2` FROM ( SELECT * FROM ( SELECT `a`, `b`,`c`, SUM(`amount1`) AS `amount1`, SUM(`amount2`) AS `amount2` FROM `t` WHERE (c IN (1,2,3)) AND ((`a` IS NOT NULL) AND (`b` IS NOT NULL)) GROUP BY `a`, `b` WITH ROLLUP) `t_rollup` ORDER BY `a`, `b` )`t_reorder`; mysql.exe Ver 14.14 Distrib 5.5.28, for Win32 (x86) (Correct) +------+------+------+---------+---------+-----------+-----------+ | a | b | c | amount1 | amount2 | a_amount1 | a_amount2 | +------+------+------+---------+---------+-----------+-----------+ | NULL | NULL | 3 | 6 | 3 | 6 | 3 | | 1 | NULL | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | NULL | 3 | 5 | 2 | 5 | 2 | | 2 | 2 | 2 | 2 | 1 | 5 | 2 | | 2 | 3 | 3 | 3 | 1 | 5 | 2 | +------+------+------+---------+---------+-----------+-----------+ mysql.exe Ver 15.1 Distrib 5.5.28-MariaDB, for Win32 (x86) (Wrong values on a_amount1 and a_amount2) ORDER BY `a`, `b` does not have any effect to this qyery +------+------+------+---------+---------+-----------+-----------+ | a | b | c | amount1 | amount2 | a_amount1 | a_amount2 | +------+------+------+---------+---------+-----------+-----------+ | 1 | 1 | 1 | 1 | 1 | 6 | 3 | | 1 | NULL | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 1 | 1 | 1 | | 2 | 3 | 3 | 3 | 1 | 1 | 1 | | 2 | NULL | 3 | 5 | 2 | 5 | 2 | | NULL | NULL | 3 | 6 | 3 | 6 | 3 | +------+------+------+---------+---------+-----------+-----------+

MariaDB seems to ignore ORDER BY statement after subquery having GROUP BY ... WITH ROLLUP.

Optimizer_switch settings as per default.

Environment

Windows server 2003, 64-bit

Status

Assignee

Unassigned

Reporter

P Ka

Labels

None

External issue ID

None

External issue ID

None

Affects versions

5.5.28a

Priority

Major