Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0, 10.1
-
None
Description
Look at this test case:
MariaDB [test]> CREATE OR REPLACE TABLE t (a INT, b INT) ENGINE = InnoDB; |
Query OK, 0 rows affected (0.34 sec) |
|
|
MariaDB [test]> INSERT INTO t VALUES |
-> (1, 1),
|
-> (1, 2),
|
-> (2, 1),
|
-> (NULL, 1); |
Query OK, 4 rows affected (0.09 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT a, COUNT(*) |
-> FROM t |
-> GROUP BY a |
-> WITH ROLLUP; |
+------+----------+ |
| a | COUNT(*) | |
+------+----------+ |
| NULL | 1 | |
| 1 | 2 |
|
| 2 | 1 |
|
| NULL | 4 | |
+------+----------+ |
4 rows in set (0.00 sec) |
|
|
MariaDB [test]> SELECT IFNULL(a, 1), COUNT(*) |
-> FROM t |
-> GROUP BY a |
-> WITH ROLLUP; |
+--------------+----------+ |
| IFNULL(a, 1) | COUNT(*) | |
+--------------+----------+ |
| 1 | 1 |
|
| 1 | 2 |
|
| 2 | 1 |
|
| 1 | 4 |
|
+--------------+----------+ |
4 rows in set (0.00 sec) |
The first result is what one expects, the second result is not. I'm not sure if it is a bug or an expected behavior, but it makes the query useless.
Since WITH ROLLUP adds rows with NULL, the IFNULL() function in the SELECT clause should be applied after WITH ROLLUP.