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:Microsoft Windows (Windows Server 2003)
Assigned to: Sergey Petrunya CPU Architecture:Any

[3 Mar 2005 14:18] Disha
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.
[3 Mar 2005 14:21] Disha
Changed synopsis and Severity
[3 Mar 2005 15:11] Jorge del Conde
Verified using 5.0.3 from bk

THanks for your bug report
[16 Mar 2005 16:11] Paul DuBois
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?
[23 Apr 2005 2:15] Sergey Petrunya
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;
[23 Apr 2005 2:20] Sergey Petrunya
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)
[23 Apr 2005 2:26] Sergey Petrunya
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)
[24 Apr 2005 23:56] Bugs System
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
[24 Apr 2005 23:57] Sergey Petrunya
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.
[28 Apr 2005 23:35] Sergey Petrunya
Pushed into 5.0.6 tree.
[28 Apr 2005 23:44] Paul DuBois
Noted in 5.0.6 changelog.
[2 Dec 2011 11:37] Wagner Bianchi
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.