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
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