Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.8.9
-
Linux x86_64, MariaDB 11.8.9 binary distribution
-
Not for Release Notes
Description
Replacing a grouping-column reference in the SELECT list with an equivalent CASE expression changes the values returned for super-aggregate rows when the query also contains a window function.
The CASE expression returns the last detail-row value instead of NULL in the ROLLUP rows. In addition, both queries return an incorrect window SUM.
CREATE TABLE t (a INT, c INT) ENGINE=InnoDB;
|
INSERT INTO t VALUES (7, -3), (8, -3);
|
|
|
SELECT c, a, SUM(c) OVER () AS total
|
FROM t
|
GROUP BY c, a WITH ROLLUP;
|
|
|
SELECT c, CASE WHEN 1 THEN a ELSE a END AS a,
|
SUM(c) OVER () AS total
|
FROM t
|
GROUP BY c, a WITH ROLLUP;
|
Actual result on 11.8.9-MariaDB:
First query:
c a total
|
-3 7 -12
|
-3 8 -12
|
-3 NULL -12
|
NULL NULL -12
|
Second query:
c a total
|
-3 7 -12
|
-3 8 -12
|
-3 8 -12
|
NULL 8 -12
|
Expected result for both queries, ignoring row order:
c a total
|
-3 7 -9
|
-3 8 -9
|
-3 NULL -9
|
NULL NULL -9
|
Attachments
Issue Links
- duplicates
-
-
- In Review
-