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

[5 Apr 2006 14:57] Tobias Asplund
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;
[5 Apr 2006 15:13] Valeriy Kravchuk
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.
[11 Apr 2006 12:03] Tobias Asplund
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)
[12 Apr 2006 19:22] Evgeny Potemkin
Duplicate of bug#18265.
[2 Jan 2007 19:54] Hongliang Qiang
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.