MySQL Bugs: #27418: DROP TABLE should drop views too

Bug #27418	DROP TABLE should drop views too
Submitted:	24 Mar 2007 17:31	Modified:	27 Apr 2007 13:00
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.0.24	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	drop table, drop view, qc, VIEW

Description:
If views and tables are the same thing, as explained in bug #11551, then DROP TABLE should drop views too.  But it does not.  The behavior of CREATE/DROP VIEW/TABLE is not always consistent in this regard.

I am reporting on an older version but I don't think this has changed in newer versions.

How to repeat:
mysql> create view foo as select * from mysql.user;
Query OK, 0 rows affected (0.05 sec)

mysql> create table foo (a int);
ERROR 1050 (42S01): Table 'foo' already exists
mysql> drop table foo;
ERROR 1051 (42S02): Unknown table 'foo'

Suggested fix:
Either treat tables and views separately in all case, or the same in all cases (I think the preference is to treat them the same, from other bug reports I have read).

Another possibility is to emit different error messages to reduce the confusion, as requested in bug #11551.  In this case, I see two ways to do it: make the error specific (so it says "view foo already exists" instead of "table foo already exists), or make the error generic, as in "Table or view 'foo' already exists."

Thank you for a feature request. As we have a separate DROP VIEW (and SHOW CREATE TABLE works for views and clearly shouws that "table" is really a view), DROP TABLE should NOT drop views. But when you create table with the name used for view, I think, more correct error message is needed. Something similar to Oracle's:

ORA_00955: name is already used by an existing object