[MDEV-29743] how to apply coalesce or ifnull to a groupby Created: 2022-10-08  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Philip orleans Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Philip orleans [ 2022-10-08 ]

In SQL Server the output is correctr:

  SELECT isnull(cast(CAST( dtime AS Date ) as varchar),'-----') as date, SUM(sales) FROM booksales GROUP BY CAST( dtime AS Date ) WITH ROLLUP;
 
date                           
------------------------------ -----------
 
2022-10-04                     17790
 
2022-10-05                     164910
 
2022-10-06                     143881
 
2022-10-07                     27881
 
-----                          354462

while in Mariadb this is wrong:

SELECT ifnull(cast(CAST( dtime AS Date ) as char),'-----') as date, SUM(sales) FROM booksales GROUP BY CAST( dtime AS Date ) WITH ROLLUP;
 
2022-10-04	17790
 
2022-10-05	164910
 
2022-10-06	143881
 
2022-10-07	27881
 
2022-10-07	354462 <------NOT RIGHT

Generated at Thu Feb 08 10:10:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.