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
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.1
• 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 [e-mail] +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.