MySQL Bugs: #8921: Views: Creating a temporary table with the same name as an existing view allowed

Bug #8921	Views: Creating a temporary table with the same name as an existing view allowed
Submitted:	3 Mar 2005 14:18	Modified:	28 Apr 2005 23:44
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	Windows (Windows Server 2003)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
We are permitted to create a View and a temporary table with the same name whereas, creating a temporary table with the same  name as that of an existing view should not be allowed as it creates conflicts while referencing the view/table. 

How to repeat:
Repro Steps:

1. Delimiter        //
2. Create database test1//
3. Use test1 //
4. Create table T1 (f1 char(20), f2 TEXT(25), f3 DATE, f4 int)//
5. Insert into T1 ('ABC','XYZ','1990-05-07',100)//
6. Create Temporary Table VTAB (f1 char(4),Tempf2 char(8)) //
7. Insert into VTAB values ('AAA','BBB') //
8. Select * from VTAB//
9. Create View VTAB AS select * from T1//
10.Select * from vtab//

			
Expected Results :     Creation of a temporary table and view with the same name should not be allowed
    
Actual Results :  Temporary Table and View with the same name can be created

Additional Info:  If we try to create a normal table and view with the same name, table/view creation fails with an appropriate error.

Changed synopsis and Severity

Verified using 5.0.3 from bk

THanks for your bug report

Additional information: If there is a temporary table with 
the same name as a view, SHOW CREATE VIEW gets
confused as long as the temporary table exists:

mysql> create temporary table tmp (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view tmp as select * from t;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view tmp;
ERROR 1347 (HY000): 'test.tmp' is not VIEW

mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view tmp;
+------+----------------------------------------------------------------------------------------------+
| View | Create View
|
+------+----------------------------------------------------------------------------------------------+        
| tmp  | CREATE ALGORITHM=UNDEFINED VIEW `test`.`tmp` AS select `test`.`t`.`v`
AS `v` from `test`.`t` |          
+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

There is also another problem: If the temporary table exists,
"SELECT * FROM tmp" is ambiguous.  What should it mean?

http://dev.mysql.com/doc/mysql/en/create-table.html says:

"... two different connections can use the same temporary table name without
conflicting with each other or with an existing non-TEMPORARY table of the
same name. (The existing table is hidden until the temporary table is
dropped.) "

The current implmentation exends this naturally onto views: Suppose we have
a view named V. If the current connection has a temporary table named V,
then view V is not visible at all, and all commands refer to the temporary
table V. 

create temporary table V as select 'this is temp table' A;
create view V as select 'this is' A, 'a view' B;

select * from V;
show create table V\G
show create view V\G;

drop temporary table V;

select * from V;
show create table V\G
show create view V\G;

drop table V;

drop view V;

Here are the queries from the above comments with query results and my interpretation:

mysql> create temporary table V as select 'this is temp table' A;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create view V as select 'this is' A, 'a view' B;
Query OK, 0 rows affected (0.00 sec)

----- at this point V is only visible as a table ------

mysql> select * from V;
+--------------------+
| A                  |
+--------------------+
| this is temp table |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table V\G
*************************** 1. row ***************************
       Table: V
Create Table: CREATE TEMPORARY TABLE `V` (
  `A` varchar(18) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create view V\G;
ERROR 1347 (HY000): 'ttemporary.V' is not VIEW

mysql> drop temporary table V;
Query OK, 0 rows affected (0.00 sec)

----- Now the temporary table is gone and we see the view V ------
mysql> select * from V;
+---------+--------+
| A       | B      |
+---------+--------+
| this is | a view |
+---------+--------+
1 row in set (0.00 sec)

mysql> show create table V\G
*************************** 1. row ***************************
       View: V
Create View: CREATE ALGORITHM=UNDEFINED VIEW `ttemporary`.`V` AS select _latin1'this is' AS `A`,_latin1'a view' AS `B`
1 row in set (0.01 sec)

mysql> show create view V\G;
*************************** 1. row ***************************
       View: V
Create View: CREATE ALGORITHM=UNDEFINED VIEW `ttemporary`.`V` AS select _latin1'this is' AS `A`,_latin1'a view' AS `B`
1 row in set (0.00 sec)

mysql> drop table V;
ERROR 1051 (42S02): Unknown table 'V'
mysql> 
mysql> drop view V;
Query OK, 0 rows affected (0.00 sec)

The above mentioned rule doesn't hold for DROP VIEW command though. Consider this:

mysql> create temporary table V2 as select 'this is temp table' A;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create view V2 as select 'this is' A, 'a view' B;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table V2\G;
*************************** 1. row ***************************
       Table: V2
Create Table: CREATE TEMPORARY TABLE `V2` (
  `A` varchar(18) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create view V2\G;
ERROR 1347 (HY000): 'ttemporary.V2' is not VIEW
----- ^^ we cannot access view V2 with SHOW command ----

mysql> drop view V2;
Query OK, 0 rows affected (0.00 sec)
----- ^^ but we can do it with DROP VIEW, the next query ----
-----    confirms we've dropped the view.

mysql> show create table V2\G;
*************************** 1. row ***************************
       Table: V2
Create Table: CREATE TEMPORARY TABLE `V2` (
  `A` varchar(18) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24266

According to Peter Gulutzan: 
The original complaint is "not a bug". As you noted, creating
a temporary table with the same name as the view should be allowed.

Changing SHOW CREATE VIEW, so that it shows the view, would be good.

Pushed into 5.0.6 tree.

Noted in 5.0.6 changelog.

I found the same problem when taking with MySQL 5.5.17. The strategy I used was to DROP TABLE <view_name> cause I didn't find the VIEW register in INFORMATION_SCHEMA.VIEWS and then recreate the VIEW using. It worked this way.