MySQL Bugs: #59005: error 1356 using coalesce or ifnull in order to customize grandtotal text

Bug #59005	error 1356 using coalesce or ifnull in order to customize grandtotal text
Submitted:	17 Dec 2010 18:58	Modified:	10 Jan 2013 14:48
Reporter:	nick rulez	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1.36, 5.5.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using group by with rollup inside a view when I try to replace NULL with another text in the grandtotal record (both with coalesce than with ifnull) the view is created but when I call it I get this error:

Error Code : 1356
View 'database_name.report' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

How to repeat:
create table names (
id_name tinyint not null auto_increment primary key,
fname varchar(20))
engine = myisam;

insert into names (fname) values ('John'),('Frank'),('Mary'),('Jack');

create table money (
id int not null auto_increment primary key,
id_name tinyint,
op_date date,
val tinyint)
engine = myisam;

insert into money (id_name,op_date,val) values
(1,'2010-01-01',10),
(1,'2010-01-05',5),
(1,'2010-02-01',10),
(2,'2010-01-17',10),
(4,'2010-01-01',20),
(4,'2010-02-01',30);

create view report as 
select coalesce(n.fname,'Total') as fname,
sum(if(month(op_date)=1,m.val,0)) as jan,
sum(if(month(op_date)=2,m.val,0)) as feb,
sum(coalesce(m.val,0)) as total
from names as n
left join money as m
on n.id_name = m.id_name
group by n.fname
with rollup; 

select * from report;

Verified also with MySQL 5.5.9:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table names (
    -> id_name tinyint not null auto_increment primary key,
    -> fname varchar(20))
    -> engine = myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into names (fname) values ('John'),('Frank'),('Mary'),('Jack');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table money (
    -> id int not null auto_increment primary key,
    -> id_name tinyint,
    -> op_date date,
    -> val tinyint)
    -> engine = myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into money (id_name,op_date,val) values
    -> (1,'2010-01-01',10),
    -> (1,'2010-01-05',5),
    -> (1,'2010-02-01',10),
    -> (2,'2010-01-17',10),
    -> (4,'2010-01-01',20),
    -> (4,'2010-02-01',30);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create view report as 
    -> select coalesce(n.fname,'Total') as fname,
    -> sum(if(month(op_date)=1,m.val,0)) as jan,
    -> sum(if(month(op_date)=2,m.val,0)) as feb,
    -> sum(coalesce(m.val,0)) as total
    -> from names as n
    -> left join money as m
    -> on n.id_name = m.id_name
    -> group by n.fname
    -> with rollup; 
Query OK, 0 rows affected (0.11 sec)

mysql> select * from report;
ERROR 1356 (HY000): View 'test.report' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

One may say that this is not a bug formally, as the manual (http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html) says:

"Because the NULL values in the super-aggregate rows are placed into the result set at such a late stage in query processing, you cannot test them as NULL values within the query itself. For example, you cannot add HAVING product IS NULL to the query to eliminate from the output all but the super-aggregate rows."

But still SELECT itself works:

mysql> select coalesce(n.fname,'Total') as fname,
    -> sum(if(month(op_date)=1,m.val,0)) as jan,
    -> sum(if(month(op_date)=2,m.val,0)) as feb,
    -> sum(coalesce(m.val,0)) as total
    -> from names as n
    -> left join money as m
    -> on n.id_name = m.id_name
    -> group by n.fname
    -> with rollup; 
+-------+------+------+-------+
| fname | jan  | feb  | total |
+-------+------+------+-------+
| Frank |   10 |    0 |    10 |
| Jack  |   20 |   30 |    50 |
| John  |   15 |   10 |    25 |
| Mary  |    0 |    0 |     0 |
| Total |   45 |   40 |    85 |
+-------+------+------+-------+
5 rows in set (0.00 sec)

so we clearly have a problem with view here.

Fixed as a duplicate of Bug#60295