drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; drop database if exists mysqltest; use test; create view v1 (c,d) as select a,b from t1; ERROR 42S02: Table 'test.t1' doesn't exist create temporary table t1 (a int, b int); create view v1 (c) as select b+1 from t1; ERROR HY000: View's SELECT refers to a temporary table 't1' drop table t1; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; ERROR HY000: View's SELECT contains a variable or parameter create view v1 (c,d) as select a,b from t1 where a = @@global.max_user_connections; ERROR HY000: View's SELECT contains a variable or parameter create view v1 (c) as select b+1 from t1; select c from v1; c 3 4 5 6 11 create temporary table t1 (a int, b int); select * from t1; a b select c from v1; c 3 4 5 6 11 show create table v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` show create view t1; ERROR HY000: 'test.t1' is not VIEW drop table t1; select a from v1; ERROR 42S22: Unknown column 'a' in 'field list' select v1.a from v1; ERROR 42S22: Unknown column 'v1.a' in 'field list' select b from v1; ERROR 42S22: Unknown column 'b' in 'field list' select v1.b from v1; ERROR 42S22: Unknown column 'v1.b' in 'field list' explain extended select c from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1` create algorithm=temptable view v2 (c) as select b+1 from t1; show create view v2; View Create View v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t1`.`b` + 1) AS `c` from `t1` select c from v2; c 3 4 5 6 11 explain extended select c from v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 2 DERIVED t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select `v2`.`c` AS `c` from `test`.`v2` create view v3 (c) as select a+1 from v1; ERROR 42S22: Unknown column 'a' in 'field list' create view v3 (c) as select b+1 from v1; ERROR 42S22: Unknown column 'b' in 'field list' create view v3 (c) as select c+1 from v1; select c from v3; c 4 5 6 7 12 explain extended select c from v3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1` create algorithm=temptable view v4 (c) as select c+1 from v2; select c from v4; c 4 5 6 7 12 explain extended select c from v4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 3 DERIVED t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select `v4`.`c` AS `c` from `test`.`v4` create view v5 (c) as select c+1 from v2; select c from v5; c 4 5 6 7 12 explain extended select c from v5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 3 DERIVED t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2` create algorithm=temptable view v6 (c) as select c+1 from v1; select c from v6; c 4 5 6 7 12 explain extended select c from v6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 2 DERIVED t1 ALL NULL NULL NULL NULL 5 Warnings: Note 1003 select `v6`.`c` AS `c` from `test`.`v6` show tables; Tables_in_test t1 v1 v2 v3 v4 v5 v6 show full tables; Tables_in_test Table_type t1 BASE TABLE v1 VIEW v2 VIEW v3 VIEW v4 VIEW v5 VIEW v6 VIEW show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 5 9 45 # 1024 0 NULL # # NULL latin1_swedish_ci NULL v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW v2 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW v3 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW v4 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW v5 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW v6 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW drop view v1,v2,v3,v4,v5,v6; create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; create view v2 as select c, d from v1; select * from v1; c d e f 1 2 0 0 1 3 0 0 2 4 0 0 2 5 0 0 3 10 1 0 select * from v2; c d 1 2 1 3 2 4 2 5 3 10 create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; ERROR 42S01: Table 'v1' already exists create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; drop view v2; alter view v2 as select c, d from v1; ERROR 42S02: Table 'test.v2' doesn't exist create or replace view v2 as select c, d from v1; alter view v1 (c,d) as select a,max(b) from t1 group by a; select * from v1; c d 1 3 2 5 3 10 select * from v2; c d 1 3 2 5 3 10 drop view v100; ERROR 42S02: Unknown table 'v100' drop view t1; ERROR HY000: 'test.t1' is not VIEW drop table v1; ERROR 42S02: Unknown table 'v1' drop view v1,v2; drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3); create view v1 (a) as select a+1 from t1; create view v2 (a) as select a-1 from t1; select * from t1 natural left join v1; a 1 2 3 select * from v2 natural left join t1; a 0 1 2 select * from v2 natural left join v1; a 0 1 2 drop view v1, v2; drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3), (1), (2), (3); create view v1 as select distinct a from t1; select * from v1; a 1 2 3 explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 2 DERIVED t1 ALL NULL NULL NULL NULL 6 Using temporary select * from t1; a 1 2 3 1 2 3 drop view v1; drop table t1; create table t1 (a int); create view v1 as select distinct a from t1 WITH CHECK OPTION; ERROR HY000: CHECK OPTION on non-updatable view 'test.v1' create view v1 as select a from t1 WITH CHECK OPTION; create view v2 as select a from t1 WITH CASCADED CHECK OPTION; create view v3 as select a from t1 WITH LOCAL CHECK OPTION; drop view v3 RESTRICT; drop view v2 CASCADE; drop view v1; drop table t1; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1 (c) as select b+1 from t1; select test.c from v1 test; c 3 4 5 6 11 create algorithm=temptable view v2 (c) as select b+1 from t1; select test.c from v2 test; c 3 4 5 6 11 select test1.* from v1 test1, v2 test2 where test1.c=test2.c; c 3 4 5 6 11 select test2.* from v1 test1, v2 test2 where test1.c=test2.c; c 3 4 5 6 11 drop table t1; drop view v1,v2; create table t1 (a int); insert into t1 values (1), (2), (3), (4); create view v1 as select a+1 from t1 order by 1 desc limit 2; select * from v1; a+1 5 4 explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3), (4); create view v1 as select a+1 from t1; create table t2 select * from v1; show columns from t2; Field Type Null Key Default Extra a+1 bigint(12) YES NULL select * from t2; a+1 2 3 4 5 drop view v1; drop table t1,t2; create table t1 (a int, b int, primary key(a)); insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; update v1 set c=a+c; ERROR HY000: Column 'c' is not updatable update v2 set a=a+c; ERROR HY000: The target table v2 of the UPDATE is not updatable update v1 set a=a+c; select * from v1; a c 13 3 24 4 35 5 46 6 61 11 select * from t1; a b 13 2 24 3 35 4 46 5 61 10 drop table t1; drop view v1,v2; create table t1 (a int, b int, primary key(a)); insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create table t2 (x int); insert into t2 values (10), (20); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; ERROR HY000: Column 'c' is not updatable update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; ERROR HY000: The target table v2 of the UPDATE is not updatable update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; select * from v1; a c 13 3 24 4 30 5 40 6 50 11 select * from t1; a b 13 2 24 3 30 4 40 5 50 10 drop table t1,t2; drop view v1,v2; create table t1 (a int, b int, primary key(b)); insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100); create view v1 (c) as select b from t1 where a<3; select * from v1; c 20 30 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where Warnings: Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3) update v1 set c=c+1; select * from t1; a b 1 21 2 31 3 40 4 50 5 100 create view v2 (c) as select b from t1 where a>=3; select * from v1, v2; c c 21 40 31 40 21 50 31 50 21 100 31 100 drop view v1, v2; drop table t1; create table t1 (a int, b int, primary key(a)); insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; delete from v2 where c < 4; ERROR HY000: The target table v2 of the DELETE is not updatable delete from v1 where c < 4; select * from v1; a c 2 4 3 5 4 6 5 11 select * from t1; a b 2 3 3 4 4 5 5 10 drop table t1; drop view v1,v2; create table t1 (a int, b int, primary key(a)); insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); create table t2 (x int); insert into t2 values (1), (2), (3), (4); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; delete v2 from t2,v2 where t2.x=v2.a; ERROR HY000: The target table v2 of the DELETE is not updatable delete v1 from t2,v1 where t2.x=v1.a; select * from v1; a c 5 11 select * from t1; a b 5 10 drop table t1,t2; drop view v1,v2; create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5); create view v1 (x,y) as select a, b from t1; create view v2 (x,y) as select a, c from t1; set updatable_views_with_limit=NO; update v1 set x=x+1; update v2 set x=x+1; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; ERROR HY000: The target table v2 of the UPDATE is not updatable set updatable_views_with_limit=YES; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; Warnings: Note 1355 View being updated does not have complete key of underlying table in it set updatable_views_with_limit=DEFAULT; show variables like "updatable_views_with_limit"; Variable_name Value updatable_views_with_limit YES select * from t1; a b c 15 2 -1 22 3 -2 32 4 -3 42 5 -4 52 10 -5 drop table t1; drop view v1,v2; create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2); create view v1 (x,y,z) as select c, b, a from t1; create view v2 (x,y) as select b, a from t1; create view v3 (x,y,z) as select b, a, b from t1; create view v4 (x,y,z) as select c+1, b, a from t1; create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; insert into v3 values (-60,4,30); ERROR HY000: The target table v3 of the INSERT is not insertable-into insert into v4 values (-60,4,30); ERROR HY000: The target table v4 of the INSERT is not insertable-into insert into v5 values (-60,4,30); ERROR HY000: The target table v5 of the INSERT is not insertable-into insert into v1 values (-60,4,30); insert into v1 (z,y,x) values (50,6,-100); insert into v2 values (5,40); select * from t1; a b c 10 2 -1 20 3 -2 30 4 -60 50 6 -100 40 5 NULL drop table t1; drop view v1,v2,v3,v4,v5; create table t1 (a int, b int, c int, primary key(a,b)); insert into t1 values (10,2,-1), (20,3,-2); create table t2 (a int, b int, c int, primary key(a,b)); insert into t2 values (30,4,-60); create view v1 (x,y,z) as select c, b, a from t1; create view v2 (x,y) as select b, a from t1; create view v3 (x,y,z) as select b, a, b from t1; create view v4 (x,y,z) as select c+1, b, a from t1; create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1; insert into v3 select c, b, a from t2; ERROR HY000: The target table v3 of the INSERT is not insertable-into insert into v4 select c, b, a from t2; ERROR HY000: The target table v4 of the INSERT is not insertable-into insert into v5 select c, b, a from t2; ERROR HY000: The target table v5 of the INSERT is not insertable-into insert into v1 select c, b, a from t2; insert into v1 (z,y,x) select a+20,b+2,-100 from t2; insert into v2 select b+1, a+10 from t2; select * from t1; a b c 10 2 -1 20 3 -2 30 4 -60 50 6 -100 40 5 NULL drop table t1, t2; drop view v1,v2,v3,v4,v5; create table t1 (a int, primary key(a)); insert into t1 values (1), (2), (3); create view v1 (x) as select a from t1 where a > 1; select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x); a x 1 NULL 2 2 3 3 drop table t1; drop view v1; create table t1 (a int, primary key(a)); insert into t1 values (1), (2), (3), (200); create view v1 (x) as select a from t1 where a > 1; create view v2 (y) as select x from v1 where x < 100; select * from v2; y 2 3 drop table t1; drop view v1,v2; create table t1 (a int, primary key(a)); insert into t1 values (1), (2), (3), (200); create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; create view v2 (y) as select x from v1; update v2 set y=10 where y=2; ERROR HY000: The target table v2 of the UPDATE is not updatable drop table t1; drop view v1,v2; create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b)); create view v1 (x) as select b from t1; insert into v1 values (1); select last_insert_id(); last_insert_id() 0 insert into t1 (b) values (2); select last_insert_id(); last_insert_id() 2 select * from t1; a b 1 1 2 2 drop view v1; drop table t1; set sql_mode='ansi'; create table t1 ("a*b" int); create view v1 as select "a*b" from t1; show create view v1; View Create View v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" drop view v1; drop table t1; set sql_mode=default; create table t1 (t_column int); create view v1 as select 'a'; select * from v1, t1; a t_column drop view v1; drop table t1; create table `t1a``b` (col1 char(2)); create view v1 as select * from `t1a``b`; select * from v1; col1 describe v1; Field Type Null Key Default Extra col1 char(2) YES NULL drop view v1; drop table `t1a``b`; create table t1 (col1 char(5),col2 char(5)); create view v1 as select * from t1; drop table t1; create table t1 (col1 char(5),newcol2 char(5)); insert into v1 values('a','aa'); ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop table t1; select * from v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v1; create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' create table t1 (col1 int,col2 char(22)); insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; create view v2 as select * from v1; update v2 set col2='Hello, view world'; select * from t1; col1 col2 5 Hello, view world drop view v2, v1; drop table t1; create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; select b from v1 use index (some_index) where b=1; ERROR HY000: Key 'some_index' doesn't exist in table 'v1' drop view v1; drop table t1; create table t1 (col1 char(5),col2 char(5)); create view v1 (col1,col2) as select col1,col2 from t1; insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4'); select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); col2 p1 p2 p4 select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); col2 p1 p2 p4 drop view v1; drop table t1; create table t1 (a int); create view v1 as select a from t1; insert into t1 values (1); SET @v0 = '2'; PREPARE stmt FROM 'UPDATE v1 SET a = ?'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; SET @v0 = '3'; PREPARE stmt FROM 'insert into v1 values (?)'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; SET @v0 = '4'; PREPARE stmt FROM 'insert into v1 (a) values (?)'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; select * from t1; a 2 3 4 drop view v1; drop table t1; CREATE VIEW v02 AS SELECT * FROM DUAL; ERROR HY000: No tables used SHOW TABLES; Tables_in_test CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2); select * from v1; EXISTS (SELECT 1 UNION SELECT 2) 1 drop view v1; create table t1 (col1 int,col2 char(22)); create view v1 as select * from t1; create index i1 on v1 (col1); ERROR HY000: 'test.v1' is not BASE TABLE drop view v1; drop table t1; CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select connection_id() AS `f1`,pi() AS `f2`,current_user() AS `f3`,version() AS `f4` drop view v1; create table t1 (s1 int); create table t2 (s2 int); insert into t1 values (1), (2); insert into t2 values (2), (3); create view v1 as select * from t1,t2 union all select * from t1,t2; select * from v1; s1 s2 1 2 2 2 1 3 2 3 1 2 2 2 1 3 2 3 drop view v1; drop tables t1, t2; create table t1 (col1 int); insert into t1 values (1); create view v1 as select count(*) from t1; insert into t1 values (null); select * from v1; count(*) 2 drop view v1; drop table t1; create table t1 (a int); create table t2 (a int); create view v1 as select a from t1; create view v2 as select a from t2 where a in (select a from v1); show create view v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) drop view v2, v1; drop table t1, t2; CREATE VIEW `v 1` AS select 5 AS `5`; show create view `v 1`; View Create View v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` drop view `v 1`; create database mysqltest; create table mysqltest.t1 (a int, b int); create view mysqltest.v1 as select a from mysqltest.t1; alter view mysqltest.v1 as select b from mysqltest.t1; alter view mysqltest.v1 as select a from mysqltest.t1; drop database mysqltest; CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)); insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer'); select * from t1 WHERE match (c2) against ('Beer'); c1 c2 1 real Beer 7 almost real Beer CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); select * from v1; c1 c2 1 real Beer 7 almost real Beer drop view v1; drop table t1; create table t1 (a int); insert into t1 values (1),(1),(2),(2),(3),(3); create view v1 as select a from t1; select distinct a from v1; a 1 2 3 select distinct a from v1 limit 2; a 1 2 select distinct a from t1 limit 2; a 1 2 prepare stmt1 from "select distinct a from v1 limit 2"; execute stmt1; a 1 2 execute stmt1; a 1 2 deallocate prepare stmt1; drop view v1; drop table t1; create table t1 (tg_column bigint); create view v1 as select count(tg_column) as vg_column from t1; select avg(vg_column) from v1; avg(vg_column) 0.0000 drop view v1; drop table t1; create table t1 (col1 bigint not null, primary key (col1)); create table t2 (col1 bigint not null, key (col1)); create view v1 as select * from t1; create view v2 as select * from t2; insert into v1 values (1); insert into v2 values (1); create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1; select * from v3; a b 1 1 show create view v3; View Create View v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from (`v1` join `v2`) where (`v1`.`col1` = `v2`.`col1`) drop view v3, v2, v1; drop table t2, t1; create function `f``1` () returns int return 5; create view v1 as select test.`f``1` (); show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` select * from v1; test.`f``1` () 5 drop view v1; drop function `f``1`; create function x () returns int return 5; create view v1 as select x (); select * from v1; x () 5 drop view v1; drop function x; create table t2 (col1 char collate latin1_german2_ci); create view v2 as select col1 collate latin1_german1_ci from t2; show create view v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` show create view v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t2`.`col1` collate latin1_german1_ci) AS `col1 collate latin1_german1_ci` from `t2` drop view v2; drop table t2; create table t1 (a int); insert into t1 values (1), (2); create view v1 as select 5 from t1 order by 1; select * from v1; 5 5 5 drop view v1; drop table t1; create function x1 () returns int return 5; create table t1 (s1 int); create view v1 as select x1() from t1; drop function x1; select * from v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL View 'test.v1' references invalid table(s) or column(s) or function(s) or define drop view v1; drop table t1; create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` drop view v1; create table t� (c� char); create view v� as select c� from t�; insert into v� values ('�'); select * from v�; c� � drop view v�; drop table t�; create table t1 (a int, b int); insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10); create view v1(c) as select a+1 from t1 where b >= 4; select c from v1 where exists (select * from t1 where a=2 and b=c); c 4 drop view v1; drop table t1; create view v1 as select cast(1 as char(3)); show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1 as char(3) charset latin1) AS `cast(1 as char(3))` select * from v1; cast(1 as char(3)) 1 drop view v1; create table t1 (a int); create view v1 as select a from t1; create view v3 as select a from t1; create database mysqltest; rename table v1 to mysqltest.v1; ERROR HY000: Changing schema from 'test' to 'mysqltest' is not allowed. rename table v1 to v2; rename table v3 to v1, v2 to t1; ERROR 42S01: Table 't1' already exists drop table t1; drop view v2,v3; drop database mysqltest; create view v1 as select 'a',1; create view v2 as select * from v1 union all select * from v1; create view v3 as select * from v2 where 1 = (select `1` from v2); create view v4 as select * from v3; select * from v4; ERROR 21000: Subquery returns more than 1 row drop view v4, v3, v2, v1; create view v1 as select 5 into @w; ERROR HY000: View's SELECT contains a 'INTO' clause create view v1 as select 5 into outfile 'ttt'; ERROR HY000: View's SELECT contains a 'INTO' clause create table t1 (a int); create view v1 as select a from t1 procedure analyse(); ERROR HY000: View's SELECT contains a 'PROCEDURE' clause create view v1 as select 1 from (select 1) as d1; ERROR HY000: View's SELECT contains a subquery in the FROM clause drop table t1; create table t1 (s1 int, primary key (s1)); create view v1 as select * from t1; insert into v1 values (1) on duplicate key update s1 = 7; insert into v1 values (1) on duplicate key update s1 = 7; select * from t1; s1 7 drop view v1; drop table t1; create table t1 (col1 int); create table t2 (col1 int); create view v1 as select * from t1; create view v2 as select * from v1; create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; update v2 set col1 = (select max(col1) from v1); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'. update v2 set col1 = (select max(col1) from t1); ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. update v2 set col1 = (select max(col1) from v2); ERROR HY000: You can't specify target table 'v2' for update in FROM clause update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'. update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'. update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; ERROR HY000: You can't specify target table 'v1' for update in FROM clause update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; ERROR HY000: You can't specify target table 't1' for update in FROM clause update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v1'. update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; ERROR HY000: You can't specify target table 't2' for update in FROM clause update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; ERROR HY000: You can't specify target table 't2' for update in FROM clause update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; ERROR HY000: You can't specify target table 't2' for update in FROM clause update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; ERROR HY000: You can't specify target table 'v2' for update in FROM clause update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't1'. update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v1'. update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'. update v3 set v3.col1 = (select max(col1) from v1); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v3'. update v3 set v3.col1 = (select max(col1) from t1); ERROR HY000: The definition of table 'v3' prevents operation UPDATE on table 'v3'. update v3 set v3.col1 = (select max(col1) from v2); ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'. update v3 set v3.col1 = (select max(col1) from v3); ERROR HY000: You can't specify target table 'v3' for update in FROM clause delete from v2 where col1 = (select max(col1) from v1); ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. delete from v2 where col1 = (select max(col1) from t1); ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. delete from v2 where col1 = (select max(col1) from v2); ERROR HY000: You can't specify target table 'v2' for update in FROM clause delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 't1'. delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; ERROR HY000: You can't specify target table 'v1' for update in FROM clause delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; ERROR HY000: You can't specify target table 't1' for update in FROM clause delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v1'. delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; ERROR HY000: You can't specify target table 'v2' for update in FROM clause delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 't1'. delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v1'. insert into v2 values ((select max(col1) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'. insert into t1 values ((select max(col1) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 't1'. insert into v2 values ((select max(col1) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'. insert into v2 values ((select max(col1) from t1)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. insert into t1 values ((select max(col1) from t1)); ERROR HY000: You can't specify target table 't1' for update in FROM clause insert into v2 values ((select max(col1) from t1)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. insert into v2 values ((select max(col1) from v2)); ERROR HY000: You can't specify target table 'v2' for update in FROM clause insert into t1 values ((select max(col1) from v2)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 't1'. insert into v2 values ((select max(col1) from v2)); ERROR HY000: You can't specify target table 'v2' for update in FROM clause insert into v3 (col1) values ((select max(col1) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v3'. insert into v3 (col1) values ((select max(col1) from t1)); ERROR HY000: The definition of table 'v3' prevents operation INSERT on table 'v3'. insert into v3 (col1) values ((select max(col1) from v2)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'. insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v3'. insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2)); ERROR 23000: Column 'Use_leap_seconds' cannot be null create algorithm=temptable view v4 as select * from t1; insert into t1 values (1),(2),(3); insert into t1 (col1) values ((select max(col1) from v4)); select * from t1; col1 NULL 1 2 3 3 drop view v4,v3,v2,v1; drop table t1,t2; create table t1 (s1 int); create view v1 as select * from t1; handler v1 open as xx; ERROR HY000: 'test.v1' is not BASE TABLE drop view v1; drop table t1; create table t1(a int); insert into t1 values (0), (1), (2), (3); create table t2 (a int); insert into t2 select a from t1 where a > 1; create view v1 as select a from t1 where a > 1; select * from t1 left join (t2 as t, v1) on v1.a=t1.a; a a a 0 NULL NULL 1 NULL NULL 2 2 2 2 3 2 3 2 3 3 3 3 select * from t1 left join (t2 as t, t2) on t2.a=t1.a; a a a 0 NULL NULL 1 NULL NULL 2 2 2 2 3 2 3 2 3 3 3 3 drop view v1; drop table t1, t2; create table t1 (s1 char); create view v1 as select s1 collate latin1_german1_ci as s1 from t1; insert into v1 values ('a'); select * from v1; s1 a update v1 set s1='b'; select * from v1; s1 b update v1,t1 set v1.s1='c' where t1.s1=v1.s1; select * from v1; s1 c prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; set @arg='d'; execute stmt1 using @arg; select * from v1; s1 d set @arg='e'; execute stmt1 using @arg; select * from v1; s1 e deallocate prepare stmt1; drop view v1; drop table t1; create table t1 (a int); create table t2 (a int); create view v1 as select * from t1; lock tables t1 read, v1 read; select * from v1; a select * from t2; ERROR HY000: Table 't2' was not locked with LOCK TABLES drop view v1; drop table t1, t2; create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; insert into v1 values(1); insert into v1 values(3); ERROR HY000: CHECK OPTION failed 'test.v1' insert ignore into v1 values (2),(3),(0); Warnings: Error 1369 CHECK OPTION failed 'test.v1' Error 1369 CHECK OPTION failed 'test.v1' select * from t1; a 1 0 delete from t1; insert into v1 SELECT 1; insert into v1 SELECT 3; ERROR HY000: CHECK OPTION failed 'test.v1' create table t2 (a int); insert into t2 values (2),(3),(0); insert ignore into v1 SELECT a from t2; Warnings: Error 1369 CHECK OPTION failed 'test.v1' Error 1369 CHECK OPTION failed 'test.v1' select * from t1; a 1 0 update v1 set a=-1 where a=0; update v1 set a=2 where a=1; ERROR HY000: CHECK OPTION failed 'test.v1' select * from t1; a 1 -1 update v1 set a=0 where a=0; insert into t2 values (1); update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; select * from t1; a 0 -1 update v1 set a=a+1; update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; Warnings: Error 1369 CHECK OPTION failed 'test.v1' select * from t1; a 1 1 drop view v1; drop table t1, t2; create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; create view v2 as select * from v1 where a > 0 with local check option; create view v3 as select * from v1 where a > 0 with cascaded check option; insert into v2 values (1); insert into v3 values (1); insert into v2 values (0); ERROR HY000: CHECK OPTION failed 'test.v2' insert into v3 values (0); ERROR HY000: CHECK OPTION failed 'test.v3' insert into v2 values (2); insert into v3 values (2); ERROR HY000: CHECK OPTION failed 'test.v3' select * from t1; a 1 1 2 drop view v3,v2,v1; drop table t1; create table t1 (a int, primary key (a)); create view v1 as select * from t1 where a < 2 with check option; insert into v1 values (1) on duplicate key update a=2; insert into v1 values (1) on duplicate key update a=2; ERROR HY000: CHECK OPTION failed 'test.v1' insert ignore into v1 values (1) on duplicate key update a=2; Warnings: Error 1369 CHECK OPTION failed 'test.v1' select * from t1; a 1 drop view v1; drop table t1; create table t1 (s1 int); create view v1 as select * from t1; create view v2 as select * from v1; alter view v1 as select * from v2; ERROR 42S02: Table 'test.v1' doesn't exist alter view v1 as select * from v1; ERROR 42S02: Table 'test.v1' doesn't exist create or replace view v1 as select * from v2; ERROR 42S02: Table 'test.v1' doesn't exist create or replace view v1 as select * from v1; ERROR 42S02: Table 'test.v1' doesn't exist drop view v2,v1; drop table t1; create table t1 (a int); create view v1 as select * from t1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` alter algorithm=undefined view v1 as select * from t1 with check option; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION alter algorithm=merge view v1 as select * from t1 with cascaded check option; show create view v1; View Create View v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION alter algorithm=temptable view v1 as select * from t1; show create view v1; View Create View v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` drop view v1; drop table t1; create table t1 (s1 int); create table t2 (s1 int); create view v2 as select * from t2 where s1 in (select s1 from t1); insert into v2 values (5); insert into t1 values (5); select * from v2; s1 5 update v2 set s1 = 0; select * from v2; s1 select * from t2; s1 0 alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; insert into v2 values (5); update v2 set s1 = 1; ERROR HY000: CHECK OPTION failed 'test.v2' insert into t1 values (1); update v2 set s1 = 1; select * from v2; s1 1 select * from t2; s1 0 1 prepare stmt1 from "select * from v2;"; execute stmt1; s1 1 insert into t1 values (0); execute stmt1; s1 0 1 deallocate prepare stmt1; drop view v2; drop table t1, t2; create table t1 (t time); create view v1 as select substring_index(t,':',2) as t from t1; insert into t1 (t) values ('12:24:10'); select substring_index(t,':',2) from t1; substring_index(t,':',2) 12:24 select substring_index(t,':',2) from v1; substring_index(t,':',2) 12:24 drop view v1; drop table t1; create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0 with local check option; create view v2 as select * from v1 with cascaded check option; insert into v2 values (0); ERROR HY000: CHECK OPTION failed 'test.v2' drop view v2, v1; drop table t1; create table t1 (s1 int); create view v1 as select * from t1 where s1 < 5 with check option; insert ignore into v1 values (6); ERROR HY000: CHECK OPTION failed 'test.v1' insert ignore into v1 values (6),(3); Warnings: Error 1369 CHECK OPTION failed 'test.v1' select * from t1; s1 3 drop view v1; drop table t1; create table t1 (s1 tinyint); create trigger t1_bi before insert on t1 for each row set new.s1 = 500; create view v1 as select * from t1 where s1 <> 127 with check option; insert into v1 values (0); ERROR HY000: CHECK OPTION failed 'test.v1' select * from v1; s1 select * from t1; s1 drop trigger t1_bi; drop view v1; drop table t1; create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0; create view v2 as select * from v1 where s1 <> 1 with cascaded check option; insert into v2 values (0); ERROR HY000: CHECK OPTION failed 'test.v2' select * from v2; s1 select * from t1; s1 drop view v2, v1; drop table t1; create table t1 (a int, b char(10)); create view v1 as select * from t1 where a != 0 with check option; load data infile '../std_data_ln/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; ERROR HY000: CHECK OPTION failed 'test.v1' select * from t1; a b 1 row 1 2 row 2 select * from v1; a b 1 row 1 2 row 2 delete from t1; load data infile '../std_data_ln/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 Error 1369 CHECK OPTION failed 'test.v1' Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 Error 1369 CHECK OPTION failed 'test.v1' select * from t1; a b 1 row 1 2 row 2 3 row 3 select * from v1; a b 1 row 1 2 row 2 3 row 3 drop view v1; drop table t1; create table t1 (a text, b text); create view v1 as select * from t1 where a <> 'Field A' with check option; load data infile '../std_data_ln/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; ERROR HY000: CHECK OPTION failed 'test.v1' select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') select concat('|',a,'|'), concat('|',b,'|') from v1; concat('|',a,'|') concat('|',b,'|') delete from t1; load data infile '../std_data_ln/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; Warnings: Error 1369 CHECK OPTION failed 'test.v1' Warning 1261 Row 2 doesn't contain data for all columns select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') |Field 1| |Field 2' Field 3,'Field 4| |Field 5' ,'Field 6| NULL |Field 6| | 'Field 7'| select concat('|',a,'|'), concat('|',b,'|') from v1; concat('|',a,'|') concat('|',b,'|') |Field 1| |Field 2' Field 3,'Field 4| |Field 5' ,'Field 6| NULL |Field 6| | 'Field 7'| drop view v1; drop table t1; create table t1 (s1 smallint); create view v1 as select * from t1 where 20 < (select (s1) from t1); insert into v1 values (30); ERROR HY000: The target table v1 of the INSERT is not insertable-into create view v2 as select * from t1; create view v3 as select * from t1 where 20 < (select (s1) from v2); insert into v3 values (30); ERROR HY000: The target table v3 of the INSERT is not insertable-into create view v4 as select * from v2 where 20 < (select (s1) from t1); insert into v4 values (30); ERROR HY000: The target table v4 of the INSERT is not insertable-into drop view v4, v3, v2, v1; drop table t1; create table t1 (a int); create view v1 as select * from t1; check table t1,v1; Table Op Msg_type Msg_text test.t1 check status OK test.v1 check status OK check table v1,t1; Table Op Msg_type Msg_text test.v1 check status OK test.t1 check status OK drop table t1; check table v1; Table Op Msg_type Msg_text test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v1; create table t1 (a int); create table t2 (a int); create table t3 (a int); insert into t1 values (1), (2), (3); insert into t2 values (1), (3); insert into t3 values (1), (2), (4); create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a); select * from t3 left join v3 on (t3.a = v3.a); a a b 1 1 1 2 2 NULL 4 NULL NULL explain extended select * from t3 left join v3 on (t3.a = v3.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 create view v1 (a) as select a from t1; create view v2 (a) as select a from t2; create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a); select * from t3 left join v4 on (t3.a = v4.a); a a b 1 1 1 2 2 NULL 4 NULL NULL explain extended select * from t3 left join v4 on (t3.a = v4.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Warnings: Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; execute stmt1; a a b 1 1 1 2 2 NULL 4 NULL NULL execute stmt1; a a b 1 1 1 2 2 NULL 4 NULL NULL deallocate prepare stmt1; drop view v4,v3,v2,v1; drop tables t1,t2,t3; create table t1 (a int, primary key (a), b int); create table t2 (a int, primary key (a)); insert into t1 values (1,100), (2,200); insert into t2 values (1), (3); create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; update v3 set a= 10 where a=1; select * from t1; a b 10 100 2 200 select * from t2; a 1 3 create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; set updatable_views_with_limit=NO; update v2 set a= 10 where a=200 limit 1; ERROR HY000: The target table t1 of the UPDATE is not updatable set updatable_views_with_limit=DEFAULT; select * from v3; a b 2 1 10 1 2 3 10 3 select * from v2; a b 100 1 200 1 100 3 200 3 set @a= 10; set @b= 100; prepare stmt1 from "update v3 set a= ? where a=?"; execute stmt1 using @a,@b; select * from v3; a b 2 1 10 1 2 3 10 3 set @a= 300; set @b= 10; execute stmt1 using @a,@b; select * from v3; a b 2 1 300 1 2 3 300 3 deallocate prepare stmt1; drop view v3,v2; drop tables t1,t2; create table t1 (a int, primary key (a), b int); create table t2 (a int, primary key (a), b int); insert into t2 values (1000, 2000); create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; insert into v3 values (1,2); ERROR HY000: Can not insert into join view 'test.v3' without fields list insert into v3 select * from t2; ERROR HY000: Can not insert into join view 'test.v3' without fields list insert into v3(a,b) values (1,2); ERROR HY000: Can not modify more than one base table through a join view 'test.v3' insert into v3(a,b) select * from t2; ERROR HY000: Can not modify more than one base table through a join view 'test.v3' insert into v3(a) values (1); insert into v3(b) values (10); insert into v3(a) select a from t2; insert into v3(b) select b from t2; Warnings: Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'a' at row 2 insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); select * from t1; a b 10002 NULL 10 NULL 1000 NULL select * from t2; a b 1000 2000 10 NULL 2000 NULL 0 NULL delete from v3; ERROR HY000: Can not delete from join view 'test.v3' delete v3,t1 from v3,t1; ERROR HY000: Can not delete from join view 'test.v3' delete t1,v3 from t1,v3; ERROR HY000: Can not delete from join view 'test.v3' delete from t1; prepare stmt1 from "insert into v3(a) values (?);"; set @a= 100; execute stmt1 using @a; set @a= 300; execute stmt1 using @a; deallocate prepare stmt1; prepare stmt1 from "insert into v3(a) select ?;"; set @a= 101; execute stmt1 using @a; set @a= 301; execute stmt1 using @a; deallocate prepare stmt1; select * from v3; a b 100 1000 101 1000 300 1000 301 1000 100 10 101 10 300 10 301 10 100 2000 101 2000 300 2000 301 2000 100 0 101 0 300 0 301 0 drop view v3; drop tables t1,t2; create table t1(f1 int); create view v1 as select f1 from t1; select * from v1 where F1 = 1; f1 drop view v1; drop table t1; create table t1(c1 int); create table t2(c2 int); insert into t1 values (1),(2),(3); insert into t2 values (1); SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); c1 1 SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); c1 1 create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2); create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); select * from v1; c1 1 select * from v2; c1 1 select * from (select c1 from v2) X; c1 1 drop view v2, v1; drop table t1, t2; CREATE TABLE t1 (C1 INT, C2 INT); CREATE TABLE t2 (C2 INT); CREATE VIEW v1 AS SELECT C2 FROM t2; CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2); SELECT * FROM v2; C1 drop view v2, v1; drop table t1, t2; create table t1 (col1 char(5),col2 int,col3 int); insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); create view v1 as select * from t1; select col1,group_concat(col2,col3) from t1 group by col1; col1 group_concat(col2,col3) one 1025,2025,3025 two 1050,1050 select col1,group_concat(col2,col3) from v1 group by col1; col1 group_concat(col2,col3) one 1025,2025,3025 two 1050,1050 drop view v1; drop table t1; create table t1 (s1 int, s2 char); create view v1 as select s1, s2 from t1; select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); ERROR 42S22: Unknown column 'vq2.s2' in 'having clause' select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); s2 drop view v1; drop table t1; CREATE TABLE t1 (a1 int); CREATE TABLE t2 (a2 int); INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (1), (2), (3); CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1; SELECT * FROM v1; a b 2 2 3 3 CREATE TABLE t3 SELECT * FROM v1; SELECT * FROM t3; a b 2 2 3 3 DROP VIEW v1; DROP TABLE t1,t2,t3; create table t1 (a int); create table t2 like t1; create table t3 like t1; create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a; insert into t3 select x from v1; insert into t2 select x from v1; drop view v1; drop table t1,t2,t3; CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); INSERT INTO t1 VALUES(1,'trudy'); INSERT INTO t1 VALUES(2,'peter'); INSERT INTO t1 VALUES(3,'sanja'); INSERT INTO t1 VALUES(4,'monty'); INSERT INTO t1 VALUES(5,'david'); INSERT INTO t1 VALUES(6,'kent'); INSERT INTO t1 VALUES(7,'carsten'); INSERT INTO t1 VALUES(8,'ranger'); INSERT INTO t1 VALUES(10,'matt'); CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); INSERT INTO t2 VALUES (1,1,'y'); INSERT INTO t2 VALUES (1,2,'y'); INSERT INTO t2 VALUES (2,1,'n'); INSERT INTO t2 VALUES (3,1,'n'); INSERT INTO t2 VALUES (4,1,'y'); INSERT INTO t2 VALUES (4,2,'n'); INSERT INTO t2 VALUES (4,3,'n'); INSERT INTO t2 VALUES (6,1,'n'); INSERT INTO t2 VALUES (8,1,'y'); CREATE VIEW v1 AS SELECT * FROM t1; SELECT a.col1,a.col2,b.col2,b.col3 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y 2 peter 1 n 3 sanja 1 n 4 monty 3 n 5 david NULL NULL 6 kent 1 n 7 carsten NULL NULL 8 ranger 1 y 10 matt NULL NULL SELECT a.col1,a.col2,b.col2,b.col3 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y 2 peter 1 n 3 sanja 1 n 4 monty 3 n 5 david NULL NULL 6 kent 1 n 7 carsten NULL NULL 8 ranger 1 y 10 matt NULL NULL CREATE VIEW v2 AS SELECT * FROM t2; SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y 2 peter 1 n 3 sanja 1 n 4 monty 3 n 5 david NULL NULL 6 kent 1 n 7 carsten NULL NULL 8 ranger 1 y 10 matt NULL NULL SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE a.col1 IN (1,5,9) AND (b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); col1 col2 col2 col3 1 trudy 2 y 5 david NULL NULL CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9); SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y 5 david NULL NULL DROP VIEW v1,v2,v3; DROP TABLE t1,t2; create table t1 as select 1 A union select 2 union select 3; create table t2 as select * from t1; create view v1 as select * from t1 where a in (select * from t2); select * from v1 A, v1 B where A.a = B.a; A A 1 1 2 2 3 3 create table t3 as select a a,a b from t2; create view v2 as select * from t3 where a in (select * from t1) or b in (select * from t2); select * from v2 A, v2 B where A.a = B.b; a b a b 1 1 1 1 2 2 2 2 3 3 3 3 drop view v1, v2; drop table t1, t2, t3; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (4), (2); CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b; SELECT * FROM v1; a b 2 2 4 4 CREATE VIEW v2 AS SELECT * FROM v1; SELECT * FROM v2; a b 2 2 4 4 DROP VIEW v2,v1; DROP TABLE t1, t2; create table t1 (a int); create view v1 as select sum(a) from t1 group by a; create procedure p1() begin select * from v1; end// call p1(); sum(a) call p1(); sum(a) drop procedure p1; drop view v1; drop table t1; CREATE TABLE t1(a char(2) primary key, b char(2)); CREATE TABLE t2(a char(2), b char(2), index i(a)); INSERT INTO t1 VALUES ('a','1'), ('b','2'); INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6'); CREATE VIEW v1 AS SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a; SELECT d, c FROM v1 ORDER BY d,c; d c 5 1 5 2 6 1 6 2 DROP VIEW v1; DROP TABLE t1, t2; create table t1 (s1 int); create view v1 as select sum(distinct s1) from t1; select * from v1; sum(distinct s1) NULL drop view v1; create view v1 as select avg(distinct s1) from t1; select * from v1; avg(distinct s1) NULL drop view v1; drop table t1; create view v1 as select cast(1 as decimal); select * from v1; cast(1 as decimal) 1.00 drop view v1; create table t1(f1 int); create table t2(f2 int); insert into t1 values(1),(2),(3); insert into t2 values(1),(2),(3); create view v1 as select * from t1,t2 where f1=f2; create table t3 (f1 int, f2 int); insert into t3 select * from v1 order by 1; select * from t3; f1 f2 1 1 2 2 3 3 drop view v1; drop table t1,t2,t3; create view v1 as select '\\','\\shazam'; select * from v1; \ \shazam \ \shazam drop view v1; create view v1 as select '\'','\shazam'; select * from v1; ' shazam ' shazam drop view v1; create view v1 as select 'k','K'; select * from v1; k My_exp_K k K drop view v1; create table t1 (s1 int); create view v1 as select s1, 's1' from t1; select * from v1; s1 My_exp_s1 drop view v1; create view v1 as select 's1', s1 from t1; select * from v1; My_exp_s1 s1 drop view v1; create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; select * from v1; My_exp_1_s1 s1 My_exp_s1 drop view v1; create view v1 as select 1 as My_exp_s1, 's1', s1 from t1; select * from v1; My_exp_s1 My_exp_1_s1 s1 drop view v1; create view v1 as select 1 as s1, 's1', 's1' from t1; select * from v1; s1 My_exp_s1 My_exp_1_s1 drop view v1; create view v1 as select 's1', 's1', 1 as s1 from t1; select * from v1; My_exp_1_s1 My_exp_s1 s1 drop view v1; create view v1 as select s1, 's1', 's1' from t1; select * from v1; s1 My_exp_s1 My_exp_1_s1 drop view v1; create view v1 as select 's1', 's1', s1 from t1; select * from v1; My_exp_1_s1 My_exp_s1 s1 drop view v1; create view v1 as select 1 as s1, 's1', s1 from t1; ERROR 42S21: Duplicate column name 's1' create view v1 as select 's1', s1, 1 as s1 from t1; ERROR 42S21: Duplicate column name 's1' drop table t1; create view v1(k, K) as select 1,2; ERROR 42S21: Duplicate column name 'K' create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; select * from v1; t 01:00 drop view v1; create table t1 (a timestamp default now()); create table t2 (b timestamp default now()); create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,(`t1`.`a` < now()) AS `t1.a < now()` from (`t1` join `t2`) where (`t1`.`a` < now()) drop view v1; drop table t1, t2; CREATE TABLE t1 ( a varchar(50) ); CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = current_user()) DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = version()) DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = database()) DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (col1 time); CREATE TABLE t2 (col1 time); CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; CREATE VIEW v2 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; CREATE VIEW v3 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; CREATE VIEW v4 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; CREATE VIEW v5 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; CREATE VIEW v6 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t2; DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v2 check status OK test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v4 check status OK test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v6 check status OK drop view v1, v2, v3, v4, v5, v6; drop table t2; drop function if exists f1; drop function if exists f2; CREATE TABLE t1 (col1 time); CREATE TABLE t2 (col1 time); CREATE TABLE t3 (col1 time); create function f1 () returns int return (select max(col1) from t1); create function f2 () returns int return (select max(col1) from t2); CREATE VIEW v1 AS SELECT f1() FROM t3; CREATE VIEW v2 AS SELECT f2() FROM t3; CREATE VIEW v3 AS SELECT f1() FROM t3; CREATE VIEW v4 AS SELECT f2() FROM t3; CREATE VIEW v5 AS SELECT f1() FROM t3; CREATE VIEW v6 AS SELECT f2() FROM t3; drop function f1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v2 check status OK test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v4 check status OK test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v6 check status OK create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v2 check status OK test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v4 check status OK test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them test.v6 check status OK drop function f1; drop function f2; drop view v1, v2, v3, v4, v5, v6; drop table t2,t3; create table t1 (f1 date); insert into t1 values ('2005-01-01'),('2005-02-02'); create view v1 as select * from t1; select * from v1 where f1='2005.02.02'; f1 2005-02-02 select * from v1 where '2005.02.02'=f1; f1 2005-02-02 drop view v1; drop table t1; CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); SELECT * FROM v1; drop view v1; CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1); SELECT * FROM v1; SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1) dkjhgd drop view v1; create table t1 (f59 int, f60 int, f61 int); insert into t1 values (19,41,32); create view v1 as select f59, f60 from t1 where f59 in (select f59 from t1); update v1 set f60=2345; ERROR HY000: The target table v1 of the UPDATE is not updatable update t1 set f60=(select max(f60) from v1); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'. drop view v1; drop table t1; create table t1 (s1 int); create view v1 as select var_samp(s1) from t1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select var_samp(`t1`.`s1`) AS `var_samp(s1)` from `t1` drop view v1; drop table t1; set sql_mode='strict_all_tables'; CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL); CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; INSERT INTO t1 (col1) VALUES(12); ERROR HY000: Field 'col2' doesn't have a default value INSERT INTO v1 (vcol1) VALUES(12); ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value INSERT INTO v2 (vcol1) VALUES(12); ERROR HY000: Field of view 'test.v2' underlying table doesn't have a default value set sql_mode=default; drop view v2,v1; drop table t1; create table t1 (f1 int); insert into t1 values (1); create view v1 as select f1 from t1; select f1 as alias from v1; alias 1 drop view v1; drop table t1; CREATE TABLE t1 (s1 int, s2 int); INSERT INTO t1 VALUES (1,2); CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; SELECT * FROM v1; s1 s2 2 1 CREATE PROCEDURE p1 () SELECT * FROM v1; CALL p1(); s1 s2 2 1 ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1; CALL p1(); s1 s2 1 2 DROP VIEW v1; CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; CALL p1(); s1 s2 2 1 DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; create table t1 (f1 int, f2 int); create view v1 as select f1 as f3, f2 as f1 from t1; insert into t1 values (1,3),(2,1),(3,2); select * from v1 order by f1; f3 f1 2 1 3 2 1 3 drop view v1; drop table t1; CREATE TABLE t1 (f1 char); INSERT INTO t1 VALUES ('A'); CREATE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES('B'); SELECT * FROM v1; f1 A B SELECT * FROM t1; f1 A B DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); CREATE OR REPLACE VIEW v1 AS SELECT * from t1; DROP PROCEDURE IF EXISTS p1; Warnings: Note 1305 PROCEDURE p1 does not exist CREATE PROCEDURE p1 ( ) BEGIN DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); INSERT INTO t1 VALUES (1); END // CALL p1(); DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); create view v1 as select * from t1; desc v1; Field Type Null Key Default Extra f1 tinyint(1) YES NULL f2 char(1) YES NULL f3 varchar(1) YES NULL f4 geometry YES NULL f5 datetime YES NULL drop view v1; drop table t1; create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; select * from v1; f1 sb 2005-01-01 12:00:00 2005-01-01 10:58:59 drop view v1; drop table t1; CREATE TABLE t1 ( aid int PRIMARY KEY, fn varchar(20) NOT NULL, ln varchar(20) NOT NULL ); CREATE TABLE t2 ( aid int NOT NULL, pid int NOT NULL ); INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); INSERT INTO t2 values (1,1), (2,1), (2,2); CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 WHERE t1.aid = t2.aid GROUP BY pid; pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) 1 a b,c d 2 c d SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) 1 a b,c d 2 c d DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255)); CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2; INSERT INTO t1 VALUES (2, 'foo2'); INSERT INTO t1 VALUES (1, 'foo1'); SELECT * FROM v1; id f 1 foo1 2 foo2 SELECT * FROM v1; id f 1 foo1 2 foo2 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (pk int PRIMARY KEY, b int); CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); CREATE TABLE t3 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); CREATE TABLE t4 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); CREATE TABLE t5 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); CREATE VIEW v1 AS SELECT t1.pk as a FROM t1,t2,t3,t4,t5 WHERE t1.b IS NULL AND t1.pk=t2.fk AND t2.pk=t3.fk AND t3.pk=t4.fk AND t4.pk=t5.fk; SELECT a FROM v1; a DROP VIEW v1; DROP TABLE t1,t2,t3,t4,t5; create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; select * from v1; f1 1 drop view v1; create table t1(a int); create procedure p1() create view v1 as select * from t1; drop table t1; call p1(); ERROR 42S02: Table 'test.t1' doesn't exist call p1(); ERROR 42S02: Table 'test.t1' doesn't exist drop procedure p1; create table t1 (f1 int); create table t2 (f1 int); insert into t1 values (1); insert into t2 values (2); create view v1 as select * from t1 union select * from t2 union all select * from t2; select * from v1; f1 1 2 2 drop view v1; drop table t1,t2; CREATE TEMPORARY TABLE t1 (a int); CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); CREATE VIEW v1 AS SELECT f1(); ERROR HY000: View's SELECT refers to a temporary table 't1' DROP FUNCTION f1; DROP TABLE t1; DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS v1; CREATE TABLE t1 (f4 CHAR(5)); CREATE VIEW v1 AS SELECT * FROM t1; DESCRIBE v1; Field Type Null Key Default Extra f4 char(5) YES NULL ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); DESCRIBE v1; ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them DROP TABLE t1; DROP VIEW v1; create table t1 (f1 char); create view v1 as select strcmp(f1,'a') from t1; select * from v1; strcmp(f1,'a') drop view v1; drop table t1; create table t1 (f1 int, f2 int,f3 int); insert into t1 values (1,10,20),(2,0,0); create view v1 as select * from t1; select if(sum(f1)>1,f2,f3) from v1 group by f1; if(sum(f1)>1,f2,f3) 20 0 drop view v1; drop table t1; create table t1 ( r_object_id char(16) NOT NULL, group_name varchar(32) NOT NULL ) engine = InnoDB; create table t2 ( r_object_id char(16) NOT NULL, i_position int(11) NOT NULL, users_names varchar(32) default NULL ) Engine = InnoDB; create view v1 as select r_object_id, group_name from t1; create view v2 as select r_object_id, i_position, users_names from t2; create unique index r_object_id on t1(r_object_id); create index group_name on t1(group_name); create unique index r_object_id_i_position on t2(r_object_id,i_position); create index users_names on t2(users_names); insert into t1 values('120001a080000542','tstgroup1'); insert into t2 values('120001a080000542',-1, 'guser01'); insert into t2 values('120001a080000542',-2, 'guser02'); select v1.r_object_id, v2.users_names from v1, v2 where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id order by users_names; r_object_id users_names 120001a080000542 guser01 120001a080000542 guser02 drop view v1, v2; drop table t1, t2; create table t1 (s1 int); create view abc as select * from t1 as abc; drop table t1; drop view abc; create table t1(f1 char(1)); create view v1 as select * from t1; select * from (select f1 as f2 from v1) v where v.f2='a'; f2 drop view v1; drop table t1; create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); select * from v1; CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') NULL drop view v1; CREATE TABLE t1 (date DATE NOT NULL); INSERT INTO t1 VALUES ('2005-09-06'); CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select dayname(`t1`.`date`) AS `DAYNAME(date)` from `t1` CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; SHOW CREATE VIEW v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select dayofweek(`t1`.`date`) AS `DAYOFWEEK(date)` from `t1` CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; SHOW CREATE VIEW v3; View Create View v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select weekday(`t1`.`date`) AS `WEEKDAY(date)` from `t1` SELECT DAYNAME('2005-09-06'); DAYNAME('2005-09-06') Tuesday SELECT DAYNAME(date) FROM t1; DAYNAME(date) Tuesday SELECT * FROM v1; DAYNAME(date) Tuesday SELECT DAYOFWEEK('2005-09-06'); DAYOFWEEK('2005-09-06') 3 SELECT DAYOFWEEK(date) FROM t1; DAYOFWEEK(date) 3 SELECT * FROM v2; DAYOFWEEK(date) 3 SELECT WEEKDAY('2005-09-06'); WEEKDAY('2005-09-06') 1 SELECT WEEKDAY(date) FROM t1; WEEKDAY(date) 1 SELECT * FROM v3; WEEKDAY(date) 1 DROP TABLE t1; DROP VIEW v1, v2, v3; CREATE TABLE t1 ( a int, b int ); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); CREATE VIEW v1 AS SELECT a,b FROM t1; SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1; a 2 3 SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1; a 2 3 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 ( a int, b int ); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); CREATE VIEW v1 AS SELECT a,b FROM t1; SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1; a 2 3 SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1; a 2 3 SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3); a 1 2 3 SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); a 1 2 3 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); CREATE TABLE t2 LIKE t1; CREATE TABLE t3 (a INT); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); INSERT INTO t2 VALUES (1,1),(2,2),(3,3); INSERT INTO t3 VALUES (1),(2),(3); CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 1 Using where; Using index 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v1 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 1 Using where; Using index 1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index EXPLAIN SELECT * FROM v2 WHERE a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 const 1 Using where; Using index 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where DROP VIEW v1,v2; DROP TABLE t1,t2,t3; create table t1 (f1 int); create view v1 as select t1.f1 as '123 456' from t1; select * from v1; 123 456 drop view v1; drop table t1; create table t1 (f1 int, f2 int); insert into t1 values(1,1),(1,2),(1,3); create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1; create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1; select * from v1; f1 group_concat(f2 order by f2 asc) 1 1,2,3 select * from v2; f1 group_concat(f2 order by f2 desc) 1 3,2,1 drop view v1,v2; drop table t1; create table t1 (x int, y int); create table t2 (x int, y int, z int); create table t3 (x int, y int, z int); create table t4 (x int, y int, z int); create view v1 as select t1.x from ( (t1 join t2 on ((t1.y = t2.y))) join (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) ); prepare stmt1 from "select count(*) from v1 where x = ?"; set @parm1=1; execute stmt1 using @parm1; count(*) 0 execute stmt1 using @parm1; count(*) 0 drop view v1; drop table t1,t2,t3,t4; CREATE TABLE t1(id INT); CREATE VIEW v1 AS SELECT id FROM t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text test.v1 optimize error 'test.v1' is not BASE TABLE Warnings: Error 1347 'test.v1' is not BASE TABLE ANALYZE TABLE v1; Table Op Msg_type Msg_text test.v1 analyze error 'test.v1' is not BASE TABLE Warnings: Error 1347 'test.v1' is not BASE TABLE REPAIR TABLE v1; Table Op Msg_type Msg_text test.v1 repair error 'test.v1' is not BASE TABLE Warnings: Error 1347 'test.v1' is not BASE TABLE DROP TABLE t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text test.v1 optimize error 'test.v1' is not BASE TABLE Warnings: Error 1347 'test.v1' is not BASE TABLE DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` drop view v1; create definer = current_user sql security invoker view v1 as select 1; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` drop view v1; create table t1 (id INT, primary key(id)); insert into t1 values (1),(2); create view v1 as select * from t1; explain select id from v1 order by id; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index drop view v1; drop table t1; create table t1(f1 int, f2 int); insert into t1 values (null, 10), (null,2); select f1, sum(f2) from t1 group by f1; f1 sum(f2) NULL 12 create view v1 as select * from t1; select f1, sum(f2) from v1 group by f1; f1 sum(f2) NULL 12 drop view v1; drop table t1; drop procedure if exists p1; create procedure p1 () deterministic begin create view v1 as select 1; end; // call p1(); show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` drop view v1; drop procedure p1; CREATE VIEW v1 AS SELECT 42 AS Meaning; DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS INTEGER BEGIN DECLARE retn INTEGER; SELECT Meaning FROM v1 INTO retn; RETURN retn; END // CREATE VIEW v2 AS SELECT f1(); select * from v2; f1() 42 drop view v2,v1; drop function f1; create table t1 (id numeric, warehouse_id numeric); create view v1 as select id from t1; create view v2 as select t1.warehouse_id, v1.id as receipt_id from t1, v1 where t1.id = v1.id; insert into t1 (id, warehouse_id) values(3, 2); insert into t1 (id, warehouse_id) values(4, 2); insert into t1 (id, warehouse_id) values(5, 1); select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2 order by v2.receipt_id; alias1 alias2 3 3 4 4 5 5 drop view v2, v1; drop table t1; CREATE TABLE t1 (a int PRIMARY KEY, b int); INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10); CREATE VIEW v1 AS SELECT * FROM t1; SELECT MAX(a) FROM t1; MAX(a) 5 SELECT MAX(a) FROM v1; MAX(a) 5 EXPLAIN SELECT MAX(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MAX(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away SELECT MIN(a) FROM t1; MIN(a) 0 SELECT MIN(a) FROM v1; MIN(a) 0 EXPLAIN SELECT MIN(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT MIN(a) FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (x varchar(10)); INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null); CREATE VIEW v1 AS SELECT * FROM t1; SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x; IF(x IS NULL, 'blank', 'not blank') blank not blank not blank SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x; x blank not blank not blank Warnings: Warning 1052 Column 'x' in group statement is ambiguous SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1; x blank not blank not blank blank SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y; y blank not blank SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x; x blank not blank not blank Warnings: Warning 1052 Column 'x' in group statement is ambiguous DROP VIEW v1; DROP TABLE t1; drop table if exists t1; drop view if exists v1; create table t1 (id int); create view v1 as select * from t1; drop table t1; show create view v1; drop view v1; // View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` create table t1(f1 int, f2 int); create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb .f1 and ta.f2=tb.f2; insert into t1 values(1,1),(2,2); create view v2 as select * from v1 where a > 1 with local check option; select * from v2; a b 2 2 update v2 set b=3 where a=2; select * from v2; a b 3 3 drop view v2, v1; drop table t1; CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1), (2); CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1; SELECT my_sqrt FROM v1 ORDER BY my_sqrt; my_sqrt 1 1.4142135623731 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (id int PRIMARY KEY); CREATE TABLE t2 (id int PRIMARY KEY); INSERT INTO t1 VALUES (1), (3); INSERT INTO t2 VALUES (1), (2), (3); CREATE VIEW v2 AS SELECT * FROM t2; SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id; COUNT(*) 2 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id; id id 1 1 3 3 SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id; COUNT(*) 2 DROP VIEW v2; DROP TABLE t1, t2; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, td date DEFAULT NULL, KEY idx(td)); INSERT INTO t1 VALUES (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 4 2005-01-03 5 2005-01-04 SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE); id td 2 2005-01-02 3 2005-01-02 4 2005-01-03 5 2005-01-04 DROP VIEW v1; DROP TABLE t1; create table t1 (a int); create view v1 as select * from t1; create view v2 as select * from v1; drop table t1; rename table v2 to t1; select * from v1; ERROR HY000: `test`.`v1` contains view recursion drop view t1, v1; create table t1 (a int); create function f1() returns int begin declare mx int; select max(a) from t1 into mx; return mx; end// create view v1 as select f1() as a; create view v2 as select * from v1; drop table t1; rename table v2 to t1; select * from v1; ERROR HY000: Recursive stored functions and triggers are not allowed. drop function f1; drop view t1, v1; create table t1 (dt datetime); insert into t1 values (20040101000000), (20050101000000), (20060101000000); create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1; select * from v1; ldt 2004-01-01 03:00:00 2005-01-01 03:00:00 2006-01-01 03:00:00 drop view v1; create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000; select * from v1; dt 2005-01-01 00:00:00 2006-01-01 00:00:00 create view v2 as select * from v1 where dt < 20060101000000; select * from v2; dt 2005-01-01 00:00:00 drop view v2; create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1; select * from v2; ldt 2005-01-01 03:00:00 2006-01-01 03:00:00 drop view v1, v2; drop table t1; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime); CREATE VIEW v1 AS SELECT id, date(d) + INTERVAL TIME_TO_SEC(d) SECOND AS t, COUNT(*) FROM t1 GROUP BY id, t; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) AS `t`,count(0) AS `COUNT(*)` from `t1` group by `t1`.`id`,(cast(`t1`.`d` as date) + interval time_to_sec(`t1`.`d`) second) SELECT * FROM v1; id t COUNT(*) DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (i INT, j BIGINT); INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); SELECT * FROM v2; MIN(i) 1 DROP VIEW v2, v1; DROP TABLE t1; CREATE TABLE t1( fName varchar(25) NOT NULL, lName varchar(25) NOT NULL, DOB date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1(fName, lName, DOB) VALUES ('Hank', 'Hill', '1964-09-29'), ('Tom', 'Adams', '1908-02-14'), ('Homer', 'Simpson', '1968-03-05'); CREATE VIEW v1 AS SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; Age 43 39 SELECT * FROM v1; Age 43 39 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); INSERT INTO t1(id) VALUES (1), (2), (3), (4); INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); SELECT * FROM t1; id a 1 xxx 2 xxx 3 xxx 4 xxx 5 yyy 6 yyy CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; SELECT * FROM v1; a m xxx 1 yyy 5 CREATE TABLE t2 SELECT * FROM v1; INSERT INTO t2(m) VALUES (0); SELECT * FROM t2; a m xxx 1 yyy 5 xxx 0 DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); INSERT INTO t1(id) VALUES (1), (2), (3); INSERT INTO t1 VALUES (4,'a'); SELECT * FROM t1; id e 1 b 2 b 3 b 4 a CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; CREATE TABLE t2 SELECT * FROM v1; SELECT * FROM t2; m e 4 a 1 b DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); CREATE VIEW v1 AS SELECT a, b FROM t1; INSERT INTO v1 (b) VALUES (2); Warnings: Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value SET SQL_MODE = STRICT_ALL_TABLES; INSERT INTO v1 (b) VALUES (4); ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value SET SQL_MODE = ''; SELECT * FROM t1; a b 0 2 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (firstname text, surname text); INSERT INTO t1 VALUES ("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 FROM v1; f1 BartBart Milhouse vanMilhouse van MontgomeryMontgomery DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (i int, j int); CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; DESCRIBE v1; Field Type Null Key Default Extra COALESCE(i,j) int(11) YES NULL CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; DESCRIBE t2; Field Type Null Key Default Extra COALESCE(i,j) int(11) YES NULL DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (s varchar(10)); INSERT INTO t1 VALUES ('yadda'), ('yady'); SELECT TRIM(BOTH 'y' FROM s) FROM t1; TRIM(BOTH 'y' FROM s) adda ad CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1; SELECT * FROM v1; TRIM(BOTH 'y' FROM s) adda ad DROP VIEW v1; SELECT TRIM(LEADING 'y' FROM s) FROM t1; TRIM(LEADING 'y' FROM s) adda ady CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1; SELECT * FROM v1; TRIM(LEADING 'y' FROM s) adda ady DROP VIEW v1; SELECT TRIM(TRAILING 'y' FROM s) FROM t1; TRIM(TRAILING 'y' FROM s) yadda yad CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1; SELECT * FROM v1; TRIM(TRAILING 'y' FROM s) yadda yad DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (x INT, y INT); CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` ALTER VIEW v1 AS SELECT x, y FROM t1; SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1` DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (s1 char); INSERT INTO t1 VALUES ('Z'); CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1; CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1; INSERT INTO v1 (col) VALUES ('b'); INSERT INTO v2 (col) VALUES ('c'); SELECT s1 FROM t1; s1 Z b c DROP VIEW v1, v2; DROP TABLE t1; CREATE TABLE t1 (id INT); CREATE VIEW v1 AS SELECT id FROM t1; SHOW TABLES; Tables_in_test t1 v1 DROP VIEW v2,v1; ERROR 42S02: Unknown table 'v2' SHOW TABLES; Tables_in_test t1 CREATE VIEW v1 AS SELECT id FROM t1; DROP VIEW t1,v1; ERROR HY000: 'test.t1' is not VIEW SHOW TABLES; Tables_in_test t1 DROP TABLE t1; DROP VIEW IF EXISTS v1; CREATE DATABASE bug21261DB; USE bug21261DB; CREATE TABLE t1 (x INT); CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost'; CREATE TABLE t2 (y INT); GRANT SELECT ON t2 TO 'user21261'@'localhost'; INSERT INTO v1 (x) VALUES (5); UPDATE v1 SET x=1; GRANT SELECT ON v1 TO 'user21261'@'localhost'; GRANT SELECT ON t1 TO 'user21261'@'localhost'; UPDATE v1,t2 SET x=1 WHERE x=y; SELECT * FROM t1; x 1 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost'; DROP USER 'user21261'@'localhost'; DROP VIEW v1; DROP TABLE t1; DROP DATABASE bug21261DB; USE test; create table t1 (f1 datetime); create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() + interval 1 minute)) drop view v1; drop table t1; DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v2; CREATE TABLE t1(a INT, b INT); CREATE DEFINER=1234567890abcdefGHIKL@localhost VIEW v1 AS SELECT a FROM t1; ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16) CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY VIEW v2 AS SELECT b FROM t1; ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) DROP TABLE t1; DROP FUNCTION IF EXISTS f1; DROP FUNCTION IF EXISTS f2; DROP VIEW IF EXISTS v1, v2; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO v1 VALUES (0); RETURN 0; END | SELECT f1(); f1() 0 CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t1; CREATE FUNCTION f2() RETURNS INT BEGIN INSERT INTO v2 VALUES (0); RETURN 0; END | SELECT f2(); ERROR HY000: The target table v2 of the INSERT is not insertable-into DROP FUNCTION f1; DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; CREATE TABLE t1 (s1 int); CREATE VIEW v1 AS SELECT * FROM t1; EXPLAIN SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found EXPLAIN SELECT * FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found INSERT INTO t1 VALUES (1), (3), (2); EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 DROP VIEW v1; DROP TABLE t1; create table t1 (s1 int); create view v1 as select s1 as a, s1 as b from t1; insert into v1 values (1,1); ERROR HY000: The target table v1 of the INSERT is not insertable-into update v1 set a = 5; drop view v1; drop table t1; CREATE TABLE t1(pk int PRIMARY KEY); CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t1 JOIN t2 ON t2.fk = t1.pk AND t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); SHOW WARNINGS; Level Code Message SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) DROP VIEW v1; DROP TABLE t1, t2; DROP FUNCTION IF EXISTS f1; DROP VIEW IF EXISTS v1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1); CREATE VIEW v1 AS SELECT MAX(i) FROM t1; CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.i = (SELECT * FROM v1) + 1; INSERT INTO t1 VALUES (1); CREATE FUNCTION f1() RETURNS INT RETURN (SELECT * FROM v1); UPDATE t1 SET i= f1(); DROP FUNCTION f1; DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; INSERT INTO v1 (val) VALUES (2); INSERT INTO v1 (val) VALUES (4); INSERT INTO v1 (val) VALUES (6); ERROR HY000: CHECK OPTION failed 'test.v1' UPDATE v1 SET val=6 WHERE id=2; ERROR HY000: CHECK OPTION failed 'test.v1' DROP VIEW v1; DROP TABLE t1; DROP VIEW IF EXISTS v1, v2; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); CREATE VIEW v1 AS SELECT j FROM t1; CREATE VIEW v2 AS SELECT * FROM t1; INSERT INTO t1 (j) VALUES (1); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 INSERT INTO v1 (j) VALUES (2); # LAST_INSERT_ID() should not change. SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 INSERT INTO v2 (j) VALUES (3); # LAST_INSERT_ID() should be updated. SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 3 INSERT INTO v1 (j) SELECT j FROM t1; # LAST_INSERT_ID() should not change. SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 3 SELECT * FROM t1; i j 1 1 2 2 3 3 4 1 5 2 6 3 DROP VIEW v1, v2; DROP TABLE t1; CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; SHOW CREATE VIEW v; View Create View v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x` SELECT !0 * 5 AS x FROM DUAL; x 5 SELECT * FROM v; x 5 DROP VIEW v; DROP VIEW IF EXISTS v1; CREATE VIEW v1 AS SELECT 'The\ZEnd'; SELECT * FROM v1; TheEnd TheEnd SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd` DROP VIEW v1; CREATE TABLE t1 (mydate DATETIME); INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31'); CREATE VIEW v1 AS SELECT mydate from t1; SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; mydate 2007-01-01 00:00:00 2007-01-02 00:00:00 2007-01-30 00:00:00 2007-01-31 00:00:00 SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; mydate 2007-01-01 00:00:00 2007-01-02 00:00:00 2007-01-30 00:00:00 2007-01-31 00:00:00 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1), (2); CREATE VIEW v1 AS SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; SELECT * FROM v1; b 1 2 UPDATE v1 SET b=3; ERROR HY000: CHECK OPTION failed 'test.v1' SELECT * FROM v1; b 1 2 SELECT * FROM t1; a 1 2 SELECT * FROM t2; b 1 2 DROP VIEW v1; DROP TABLE t1,t2; create table t1(f1 int, f2 int); insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); select * from t1; f1 f2 1 2 1 3 1 1 2 3 2 1 2 2 create view v1 as select * from t1 order by f2; select * from v1; f1 f2 1 1 2 1 1 2 2 2 1 3 2 3 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` select * from v1 order by f1; f1 f2 1 1 1 2 1 3 2 1 2 2 2 3 explain extended select * from v1 order by f1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2` drop view v1; drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, country varchar(32), code int(11) default NULL ); INSERT INTO t1 VALUES (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100); CREATE VIEW v1 AS SELECT * FROM t1; SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY MAX(id); code COUNT(DISTINCT country) 200 1 100 2 SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); code COUNT(DISTINCT country) 200 1 100 2 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 ( lid int NOT NULL PRIMARY KEY, name char(10) NOT NULL ); INSERT INTO t1 (lid, name) VALUES (1, 'YES'), (2, 'NO'); CREATE TABLE t2 ( id int NOT NULL PRIMARY KEY, gid int NOT NULL, lid int NOT NULL, dt date ); INSERT INTO t2 (id, gid, lid, dt) VALUES (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); SELECT DISTINCT t2.gid AS lgid, (SELECT t1.name FROM t1, t2 WHERE t1.lid = t2.lid AND t2.gid = lgid ORDER BY t2.dt DESC LIMIT 1 ) as clid FROM t2; lgid clid 1 NO 2 YES CREATE VIEW v1 AS SELECT DISTINCT t2.gid AS lgid, (SELECT t1.name FROM t1, t2 WHERE t1.lid = t2.lid AND t2.gid = lgid ORDER BY t2.dt DESC LIMIT 1 ) as clid FROM t2; SELECT * FROM v1; lgid clid 1 NO 2 YES DROP VIEW v1; DROP table t1,t2; End of 5.0 tests.