MySQL Bugs: #47138: error 'table exists' when it is a view that does

Bug #47138	error 'table exists' when it is a view that does
Submitted:	4 Sep 2009 13:21	Modified:	4 Sep 2009 15:17
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Error messages in MySQL are not always very clear. Here is a small annoyance that should be easy to fix.

How to repeat:
CREATE TABLE t (id INT);
CREATE VIEW t AS SELECT * FROM t; 
-- Error Code : 1050
-- Table 't' already exists

CREATE VIEW v AS SELECT * FROM t; 
CREATE TABLE v (id INT);
-- Error Code : 1050
-- Table 't' already exists

Suggested fix:
Adjust error message to 'table or view <name> already exists'

I am NOT sure we need to change anything. Even if you assume 'v' is a table, we can double check:

mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE v (id INT);
ERROR 1050 (42S01): Table 'v' already exists
mysql> show create table v\G
*************************** 1. row ***************************
                View: v
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `v` AS select `t`.`id` AS `id` from `t`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.17 sec)

So, why change anything...

Of course you can double-check by SHOW CREATE TABLE or SHOW FULL TABLES.

But I think the error message is incorrect in 50% of cases. I tend to forget that 'table exists' sometimes means that a view exists instead. It confuses and sometimes wastes a little time.

Views are (virtual) tables. Remember Codd rules also :) So, I see no real need to fix anything.