Bug #23106 Invalid View definition error is too vague
Submitted: 9 Oct 2006 13:32 Modified: 18 Jan 2007 0:17
Reporter: Mark Leith Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version: OS:
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: error messages, errors, Views

[9 Oct 2006 13:32] Mark Leith
Description:
Selecting from a view which has an invalid definition due to underlying object changes results in a very vague error message. This makes the actual cause of the error hard to track down from the outset. 

The error message being:

ERROR 1356 (HY000): View 'x.x' 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 t1 (i int, j int);
create view v1 as select i, j from t1;
select * from v1;
alter table t1 drop column j;
select * from v1;
drop table t1;
select * from v1;

Suggested fix:
Report exactly where the error is:

o If a column is missing, report which column is missing with a specific error
o If a table is missing, report which table is missing with a specific error
... etc.
[19 Oct 2006 19:20] Chad MILLER
We discussed this in the support/development conference-call today.  Here are my opinions:

If we intend to make a statement about the existence of a table, view, column, et c., then we should go through the same permission mechanism as the mundane test of its existence.  That is, abstracting a query behind a view MUST NOT confer any new privileges that the DEFINER/INVOKER would not normally have, and we MUST NOT report error messages that the INVOKER could not normally see (and to chase down the difference between what this invoker should be able to see versus what the definer should be able to see could be difficult and expensive (imagine daisy-chained views)).  

For SQL SECURITY DEFINER views, the vague error message is not a bug.  For SQL SECURITY INVOKER views, the error message technically could perhaps be better, but would have the side-effect of making the error messages a user should expect  depend on SQL SECURITY setting of the view(s).