MySQL Bugs: #18813: WITH ROLLUP displays empty string instead of NULL if string columns grouped

Bug #18813	WITH ROLLUP displays empty string instead of NULL if string columns grouped
Submitted:	5 Apr 2006 14:57	Modified:	12 Apr 2006 19:22
Reporter:	Tobias Asplund	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21-BK, 5.0.19	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
GROUP BY ... WITH ROLLUP is inconsistent depending on if the column being grouped upon is a number or a string.

mysql> CREATE TABLE bugtest ( a INT, b INT, c INT);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO bugtest VALUES (1, 1, 1), (1, 1, 2), (2, 1, 1);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT a, b, SUM(c) FROM bugtest GROUP BY a, b WITH ROLLUP;
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| 1    | 1    | 3      |
| 1    | NULL | 3      |
| 2    | 1    | 1      |
| 2    | NULL | 1      |
| NULL | NULL | 4      |
+------+------+--------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE bugtest MODIFY a CHAR(1), MODIFY b CHAR(1);
Query OK, 3 rows affected (0.44 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT a, b, SUM(c) FROM bugtest GROUP BY a, b WITH ROLLUP;
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| 1    | 1    | 3      |
| 1    |      | 3      |
| 2    | 1    | 1      |
| 2    |      | 1      |
|      |      | 4      |
+------+------+--------+
5 rows in set (0.00 sec)

How to repeat:
CREATE TABLE bugtest ( a INT, b INT, c INT);

INSERT INTO bugtest VALUES (1, 1, 1), (1, 1, 2), (2, 1, 1);

SELECT a, b, SUM(c) FROM bugtest GROUP BY a, b WITH ROLLUP;

ALTER TABLE bugtest MODIFY a CHAR(1), MODIFY b CHAR(1);

SELECT a, b, SUM(c) FROM bugtest GROUP BY a, b WITH ROLLUP;

Thank you for a problem report. Verified just as described with 5.0.21-BK (ChangeSet@1.2135, 2006-04-04 15:40:42+05:00) on Linux.

Seems to just be NULL for string datatypes that somehow are displayed as ''.

mysql> SELECT CONCAT(NULL);
+--------------+
| CONCAT(NULL) |
+--------------+
|              |
+--------------+
1 row in set (0.00 sec)

Duplicate of bug#18265.

The same issue also occurs if the column is of date type. It is not exactly the same as the bug in bug#18265, and is not fixed as of version 5.0.30.