Bug #8528 View to a view created from a join crashes
Submitted: 15 Feb 2005 23:28 Modified: 12 May 2005 20:40
Reporter: Rev Joel Baclayon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha OS:Linux (Fedora 3)
Assigned to: Igor Babaev CPU Architecture:Any

[15 Feb 2005 23:28] Rev Joel Baclayon
Description:
MySQL 5.0.2-alpha allows me to create a view from a "join" of two tables. It also allows me to create a second view from the first. It crashes, however, when I try to do a SELECT from the second view. And yet, the resulting view definition that I obtained from INFORMATION_SCHEMA.VIEWS shows a perfectly valid syntax, and works when I cut and pasted it at the command line. 

 

How to repeat:
mysql> create table t1 (col int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert t1 values(101);
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 (c1 int,  c2 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert t2 values(101, 333);
Query OK, 1 row affected (0.01 sec)

mysql> create view v1 as select t1.col, t2.c2 from t1 
    -> join t2 on t2.c1 = t1.col;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
+------+------+
| col   |  c2   |
+------+------+
|  101 |  333 |
+------+------+
1 row in set (0.02 sec)

mysql> create view v2 as select * from v1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select view_definition from INFORMATION_SCHEMA.VIEWS
    -> where table_name = 'v2';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: Jolux

+--------------------------------------------------------------------------------+
| view_definition                                                                          |
+---------------------------------------------------------------------------------+
| select `v1`.`col` AS `col`,`v1`.`c2` AS `c2` from `Jolux`.`v1` |
+----------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> select `v1`.`col` AS `col`,`v1`.`c2` AS `c2` from `Jolux`.`v1`;
+------+------+
| col   | c2   |
+------+------+
|  101 |  333 |
+------+------+
1 row in set (0.00 sec)
[15 Feb 2005 23:37] Miguel Solorzano
Thank you for the bug report I was able to repeat the crash
with latest BK source tree.
[15 Apr 2005 1:19] Yoshiaki Tajika
I duplicated this bug also in 5.0.3-beta.
I'd like to use 'view on view' like this.
Could you please how long it will take until it's fixed?
[15 Apr 2005 1:22] Yoshiaki Tajika
Sorry.
Could you please how long...
--> Could you please tell me how long ...
[15 Apr 2005 11:28] David Axmark
Increase of priority since a customer is waiting for this.
[11 May 2005 17:26] Igor Babaev
This bug revealed a number of serious problems for single-table views defined on multi-table views.
To fix the flaw the representation of table nodes for single-table views was changed. It was made similar to taht for multi-table views.

The fix can be found in CS 1.1938.
It will appear in release 5.0.7.
[12 May 2005 20:40] Paul Dubois
Noted in 5.0.6 changelog. (Not 5.0.7)