Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
-
Linux
Description
this problem has probably been asked many times, because it's a point where mariadb differs from every other database.
CREATE TABLE `booksales` ( `country` varchar(35) DEFAULT NULL, `genre` enum('fiction','non-fiction') DEFAULT NULL, `dtime` datetime NOT NULL DEFAULT current_timestamp(), `sales` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
|
|
INSERT INTO booksales VALUES |
('Senegal','fiction','2022-10-07 20:20:01',12234), ('Senegal','fiction','2022-10-07 20:20:01',15647), |
('Senegal','non-fiction','2022-10-06 20:20:01',64980), ('Senegal','non-fiction','2022-10-06 20:20:01',78901), |
('Paraguay','fiction','2022-10-05 20:20:01',87970), ('Paraguay','fiction','2022-10-05 20:20:01',76940), |
('Paraguay','non-fiction','2022-10-04 20:20:01',8760), ('Paraguay','non-fiction','2022-10-04 20:20:01',9030); |
|
|
|
|
|
SELECT date(dtime), SUM(sales) FROM booksales GROUP BY date(dtime) WITH ROLLUP;
|
+-------------+------------+
|
| date(dtime) | SUM(sales) |
|
+-------------+------------+
|
| 2022-10-04 | 17790 |
|
| 2022-10-05 | 164910 |
|
| 2022-10-06 | 143881 |
|
| 2022-10-07 | 27881 |
|
| NULL | 354462 |
|
+-------------+------------+
|
the question is how to mask the NULL simply, without a subquery, using coalesce() or ifnull(), which works in any other database.
Can we fix this? I know you are pretty busy but makes so much sense that this would work.