Bug #35869 Defining a view over an aggregate view renders the view definition invalid
Submitted: 7 Apr 2008 9:12 Modified: 7 Apr 2008 10:58
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0/5.1/6.0 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[7 Apr 2008 9:12] Konstantin Osipov
Description:
When I create a view that selects from an aggregate view, the new view
definition is incorrect. If later on the aggregate view is replaced with a table,
the original view is rendered invalid.

mysql> drop view if exists v1, v2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (a) values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 (a) values (4), (5), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create view v2 as select max(a) from t1;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from v1;
+--------+
| max(a) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> drop view v2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table v2 as select * from t2;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select max(a) from v2;
+--------+
| max(a) |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

mysql> select * from v1;
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> show create view v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`kostja`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `v2`.`max(a)` AS `max(a)` from `v2`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set, 1 warning (0.00 sec)

How to repeat:
drop view if exists v1, v2;
drop table if exists t1, t2;
create table t1 (a int);
insert into t1 (a) values (1), (2), (3);
create table t2 like t1;
insert into t2 (a) values (4), (5), (6);
create view v2 as select max(a) from t1;
create view v1 as select * from v2;
select * from v1;
drop view v2;
create table v2 as select * from t2;
select max(a) from v2;
select * from v1;
-- here we get an error, even though doing nothing wrong
show create view v1\G
-- here you can see that the view definition is invalid
[7 Apr 2008 10:58] Miguel Solorzano
Thank you for the bug report. Verified as described.
[8 Apr 2008 20:33] Omer Barnir
workaround: drop and recreate the views (solution in some cases)