Bug #4803 SHOW CREATE VIEW doesn't work when using view in subquery
Submitted: 29 Jul 2004 12:19 Modified: 25 Aug 2004 9:04
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.1 OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[29 Jul 2004 12:19] Georg Richter
Description:
When defining a view, which contains a 2nd view in subquery, show create view fails. 

How to repeat:
mysql> create table t1 (a int);  
Query OK, 0 rows affected (0.09 sec)  
  
mysql> create table t2 (a int);  
Query OK, 0 rows affected (0.09 sec)  
  
mysql> insert into t1 values (1),(2);  
Query OK, 2 rows affected (0.01 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
  
mysql> insert into t2 values (1);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> create view v1 as select a from t1;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select a from t2 where a in (select a from v1);  
+------+  
| a    |  
+------+  
|    1 |  
+------+  
1 row in set (0.01 sec)  
  
mysql> create view v2 as select a from t2 where a in (select a from v1);  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> show create view v2;  
ERROR 1054 (42S22): Unknown column 'test.t1.a' in 'field list'  
  

Suggested fix:
[6 Aug 2004 3:12] MySQL Verification Team
Verified against latest BK tree 5.0.
[24 Aug 2004 21:53] Oleksandr Byelkin
ChangeSet 
  1.1732 04/08/24 22:51:23 bell@sanja.is.com.ua +5 -0 
  new method to detect commands where all VIEWs should be temporary tables 
(BUG#4803)
[25 Aug 2004 9:03] Oleksandr Byelkin
Thank you for bugreport! Bugfix is pushed into our source repository.