[MDEV-9310] IFNULL() does not work well WITH ROLLUP Created: 2015-12-21  Updated: 2015-12-26  Resolved: 2015-12-26

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: 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.

 Comments   
Comment by Elena Stepanova [ 2015-12-26 ]

f_razzoli,

I don't see any flaw in the logic above, it seems to work strictly according to specification.

A function cannot possibly be applied after ROLLUP, but that's not the point and not the reason of the result you are getting. I think it's possible that your query is not quite what you meant it to be.

Let's forget ROLLUP for a moment.
You have the table 

+------+------+
 
| a    | b    |
 
+------+------+
 
|    1 |    1 |
 
|    1 |    2 |
 
|    2 |    1 |
 
| NULL |    1 |
 
+------+------+

and you are running the query

MariaDB [test]> SELECT IFNULL(a, 1), COUNT(*) FROM t GROUP BY a;
 
+--------------+----------+
 
| IFNULL(a, 1) | COUNT(*) |
 
+--------------+----------+
 
|            1 |        1 |
 
|            1 |        2 |
 
|            2 |        1 |
 
+--------------+----------+
 
3 rows in set (0.00 sec)

Is it the result you want to get?
Hopefully not, because it does not really make much sense. Your query is doing GROUP BY using the field which is not in the select list. This GROUP BY is usually meaningless indeed. What you most likely wanted to do was to group by the already converted a value:

MariaDB [test]> SELECT IFNULL(a, 1) AS ifn, COUNT(*) FROM t GROUP BY ifn;
 
+-----+----------+
 
| ifn | COUNT(*) |
 
+-----+----------+
 
|   1 |        3 |
 
|   2 |        1 |
 
+-----+----------+
 
2 rows in set (0.00 sec)

Now, that makes much more sense. And back to ROLLUP, it will now work just as expected:

MariaDB [test]> SELECT IFNULL(a, 1) AS ifn, COUNT(*) FROM t GROUP BY ifn WITH ROLLUP; 
+------+----------+
 
| ifn  | COUNT(*) |
 
+------+----------+
 
|    1 |        3 |
 
|    2 |        1 |
 
| NULL |        4 |
 
+------+----------+
 
3 rows in set (0.00 sec)

It is still possible however, that you wanted to group by the initial a value. But doing it the way your query does is useless, because you won't know which value the count belongs to – NULL or 1. If you want to see the initial a value, you need to add it to your select list:

MariaDB [test]> SELECT a, IFNULL(a, 1) AS ifn, COUNT(*) FROM t GROUP BY a;
 
+------+-----+----------+
 
| a    | ifn | COUNT(*) |
 
+------+-----+----------+
 
| NULL |   1 |        1 |
 
|    1 |   1 |        2 |
 
|    2 |   2 |        1 |
 
+------+-----+----------+
 
3 rows in set (0.00 sec)

And then, ROLLUP will again make sense:

MariaDB [test]> SELECT a, IFNULL(a, 1) AS ifn, COUNT(*) FROM t GROUP BY a WITH ROLLUP;
 
+------+------+----------+
 
| a    | ifn  | COUNT(*) |
 
+------+------+----------+
 
| NULL |    1 |        1 |
 
|    1 |    1 |        2 |
 
|    2 |    2 |        1 |
 
| NULL |    1 |        4 |
 
+------+------+----------+

If neither of these variants works for you and you wanted to achieve something different, please elaborate on that.

Comment by Federico Razzoli [ 2015-12-26 ]

Sorr for the bad example (also using numbers instead of meaningful values didn't help).

Yes, your last 2 queries are what I meant. Selecting both a and IFNULL(a, 1) solves the problem.

Generated at Thu Feb 08 07:33:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.