Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.28a
-
None
-
None
-
None
-
Windows server 2003, 64-bit
Description
The following query returns different result between MYSQL and MARIADB (5.5.28):
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.
Attachments
Issue Links
- duplicates
-
MDEV-13055 from select statement not normal result
- Closed
- relates to
-
MDEV-5007 wrong order by moving from mysql to mariadb
- Closed