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 select is_updatable from information_schema.views where table_name='v1'; is_updatable NO 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci 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 filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 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 character_set_client collation_connection v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select (`t1`.`b` + 1) AS `c` from `t1` latin1 latin1_swedish_ci 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 filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 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 filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 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 filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00 3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 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 filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 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 filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 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 # # # latin1_swedish_ci NULL v1 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 VIEW v3 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 VIEW v5 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 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; select is_updatable from information_schema.views where table_name='v2'; is_updatable NO select is_updatable from information_schema.views where table_name='v1'; is_updatable YES 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 filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 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 character_set_client collation_connection v1 CREATE VIEW "v1" AS select "t1"."a*b" AS "a*b" from "t1" latin1 latin1_swedish_ci 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 is_updatable from information_schema.views; is_updatable YES YES 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 42000: 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 character_set_client collation_connection 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` latin1 latin1_swedish_ci 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 character_set_client collation_connection 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` from `v1`) latin1 latin1_swedish_ci 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 character_set_client collation_connection v 1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v 1` AS select 5 AS `5` latin1 latin1_swedish_ci 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 character_set_client collation_connection 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`) latin1 latin1_swedish_ci 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`f``1`() AS `test.``f````1`` ()` latin1 latin1_swedish_ci select * from v1; test.`f``1` () 5 drop view v1; drop function `f``1`; create function a() returns int return 5; create view v1 as select a(); select * from v1; a() 5 drop view v1; drop function a; 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 character_set_client collation_connection 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` latin1 latin1_swedish_ci show create view v2; View Create View character_set_client collation_connection 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` latin1 latin1_swedish_ci 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 Warnings: Warning 1356 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; drop table t1; create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 99999999999999999999999999999999999999999999999999999 AS `col1` latin1 latin1_swedish_ci drop view v1; SET @old_cs_client = @@character_set_client; SET @old_cs_results = @@character_set_results; SET @old_cs_connection = @@character_set_connection; set names utf8; 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ü; SET character_set_client = @old_cs_client; SET character_set_results = @old_cs_results; SET character_set_connection = @old_cs_connection; 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 character_set_client collation_connection 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))` latin1 latin1_swedish_ci 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 table t3 (col1 datetime not null); 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 t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); ERROR 23000: Column 'col1' 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,t3; 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 order by a desc; 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 order by a desc; 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 order by a desc; 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci alter algorithm=undefined view v1 as select * from t1 with check option; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci alter algorithm=merge view v1 as select * from t1 with cascaded check option; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` WITH CASCADED CHECK OPTION latin1 latin1_swedish_ci alter algorithm=temptable view v1 as select * from t1; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci 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/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/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 order by a,b; a b 1 row 1 2 row 2 3 row 3 select * from v1 order by a,b; 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/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/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 Table 'test.t1' doesn't exist 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.v1 check error Corrupt 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 filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 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`.`t3`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`a`)))) on((`test`.`t1`.`a` = `test`.`t3`.`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 filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 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`.`t3`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`a`)))) on((`test`.`t1`.`a` = `test`.`t3`.`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 1048 Column 'a' cannot be null 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 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 character_set_client collation_connection 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()) latin1 latin1_swedish_ci 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 character_set_client collation_connection 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()) latin1 latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = version()) latin1 latin1_swedish_ci DROP VIEW v1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where (`t1`.`a` = database()) latin1 latin1_swedish_ci 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 Table 'test.t1' doesn't exist 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.v1 check error Corrupt test.v2 check status OK test.v3 check Error Table 'test.t1' doesn't exist 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.v3 check error Corrupt test.v4 check status OK test.v5 check Error Table 'test.t1' doesn't exist 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.v5 check error Corrupt 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 FUNCTION test.f1 does not exist 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.v1 check error Corrupt test.v2 check status OK test.v3 check Error FUNCTION test.f1 does not exist 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.v3 check error Corrupt test.v4 check status OK test.v5 check Error FUNCTION test.f1 does not exist 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.v5 check error Corrupt 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 status OK test.v2 check status OK test.v3 check status OK test.v4 check status OK test.v5 check status OK 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select var_samp(`t1`.`s1`) AS `var_samp(s1)` from `t1` latin1 latin1_swedish_ci 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 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select dayname(`t1`.`date`) AS `DAYNAME(date)` from `t1` latin1 latin1_swedish_ci CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1; SHOW CREATE VIEW v2; View Create View character_set_client collation_connection v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select dayofweek(`t1`.`date`) AS `DAYOFWEEK(date)` from `t1` latin1 latin1_swedish_ci CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1; SHOW CREATE VIEW v3; View Create View character_set_client collation_connection v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select weekday(`t1`.`date`) AS `WEEKDAY(date)` from `t1` latin1 latin1_swedish_ci 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; Using join buffer 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 test.v1 optimize error Corrupt ANALYZE TABLE v1; Table Op Msg_type Msg_text test.v1 analyze Error 'test.v1' is not BASE TABLE test.v1 analyze error Corrupt REPAIR TABLE v1; Table Op Msg_type Msg_text test.v1 repair Error 'test.v1' is not BASE TABLE test.v1 repair error Corrupt DROP TABLE t1; OPTIMIZE TABLE v1; Table Op Msg_type Msg_text test.v1 optimize Error 'test.v1' is not BASE TABLE test.v1 optimize error Corrupt ANALYZE TABLE v1; Table Op Msg_type Msg_text test.v1 analyze Error 'test.v1' is not BASE TABLE test.v1 analyze error Corrupt REPAIR TABLE v1; Table Op Msg_type Msg_text test.v1 repair Error 'test.v1' is not BASE TABLE test.v1 repair error Corrupt DROP VIEW v1; create definer = current_user() sql security invoker view v1 as select 1; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci drop view v1; create definer = current_user sql security invoker view v1 as select 1; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`id` AS `id` from `t1` latin1 latin1_swedish_ci 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 character_set_client collation_connection 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) latin1 latin1_swedish_ci 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, test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1(fName, lName, DOB, test_date) VALUES ('Hank', 'Hill', '1964-09-29', '2007-01-01'), ('Tom', 'Adams', '1908-02-14', '2007-01-01'), ('Homer', 'Simpson', '1968-03-05', '2007-01-01'); CREATE VIEW v1 AS SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(`t1`.`test_date`) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) latin1 latin1_swedish_ci SELECT (year(test_date)-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) bigint(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 character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1` latin1 latin1_swedish_ci ALTER VIEW v1 AS SELECT x, y FROM t1; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1` latin1 latin1_swedish_ci 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 character_set_client collation_connection 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)) latin1 latin1_swedish_ci 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 character_set_client collation_connection 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`) from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci 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 character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x` latin1 latin1_swedish_ci 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 character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'The\ZEnd' AS `TheEnd` latin1 latin1_swedish_ci 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 filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 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 filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 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; DROP VIEW IF EXISTS v1; SELECT * FROM (SELECT 1) AS t; 1 1 CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t; ERROR HY000: View's SELECT contains a subquery in the FROM clause # Previously the following would fail. SELECT * FROM (SELECT 1) AS t; 1 1 drop view if exists view_24532_a; drop view if exists view_24532_b; drop table if exists table_24532; create table table_24532 ( a int, b bigint, c int(4), d bigint(48) ); create view view_24532_a as select a IS TRUE, a IS NOT TRUE, a IS FALSE, a IS NOT FALSE, a IS UNKNOWN, a IS NOT UNKNOWN, a is NULL, a IS NOT NULL, ISNULL(a), b IS TRUE, b IS NOT TRUE, b IS FALSE, b IS NOT FALSE, b IS UNKNOWN, b IS NOT UNKNOWN, b is NULL, b IS NOT NULL, ISNULL(b), c IS TRUE, c IS NOT TRUE, c IS FALSE, c IS NOT FALSE, c IS UNKNOWN, c IS NOT UNKNOWN, c is NULL, c IS NOT NULL, ISNULL(c), d IS TRUE, d IS NOT TRUE, d IS FALSE, d IS NOT FALSE, d IS UNKNOWN, d IS NOT UNKNOWN, d is NULL, d IS NOT NULL, ISNULL(d) from table_24532; describe view_24532_a; Field Type Null Key Default Extra a IS TRUE int(1) NO 0 a IS NOT TRUE int(1) NO 0 a IS FALSE int(1) NO 0 a IS NOT FALSE int(1) NO 0 a IS UNKNOWN int(1) NO 0 a IS NOT UNKNOWN int(1) NO 0 a is NULL int(1) NO 0 a IS NOT NULL int(1) NO 0 ISNULL(a) int(1) NO 0 b IS TRUE int(1) NO 0 b IS NOT TRUE int(1) NO 0 b IS FALSE int(1) NO 0 b IS NOT FALSE int(1) NO 0 b IS UNKNOWN int(1) NO 0 b IS NOT UNKNOWN int(1) NO 0 b is NULL int(1) NO 0 b IS NOT NULL int(1) NO 0 ISNULL(b) int(1) NO 0 c IS TRUE int(1) NO 0 c IS NOT TRUE int(1) NO 0 c IS FALSE int(1) NO 0 c IS NOT FALSE int(1) NO 0 c IS UNKNOWN int(1) NO 0 c IS NOT UNKNOWN int(1) NO 0 c is NULL int(1) NO 0 c IS NOT NULL int(1) NO 0 ISNULL(c) int(1) NO 0 d IS TRUE int(1) NO 0 d IS NOT TRUE int(1) NO 0 d IS FALSE int(1) NO 0 d IS NOT FALSE int(1) NO 0 d IS UNKNOWN int(1) NO 0 d IS NOT UNKNOWN int(1) NO 0 d is NULL int(1) NO 0 d IS NOT NULL int(1) NO 0 ISNULL(d) int(1) NO 0 create view view_24532_b as select a IS TRUE, if(ifnull(a, 0), 1, 0) as old_istrue, a IS NOT TRUE, if(ifnull(a, 0), 0, 1) as old_isnottrue, a IS FALSE, if(ifnull(a, 1), 0, 1) as old_isfalse, a IS NOT FALSE, if(ifnull(a, 1), 1, 0) as old_isnotfalse from table_24532; describe view_24532_b; Field Type Null Key Default Extra a IS TRUE int(1) NO 0 old_istrue int(1) NO 0 a IS NOT TRUE int(1) NO 0 old_isnottrue int(1) NO 0 a IS FALSE int(1) NO 0 old_isfalse int(1) NO 0 a IS NOT FALSE int(1) NO 0 old_isnotfalse int(1) NO 0 show create view view_24532_b; View Create View character_set_client collation_connection view_24532_b CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_24532_b` AS select (`table_24532`.`a` is true) AS `a IS TRUE`,if(ifnull(`table_24532`.`a`,0),1,0) AS `old_istrue`,(`table_24532`.`a` is not true) AS `a IS NOT TRUE`,if(ifnull(`table_24532`.`a`,0),0,1) AS `old_isnottrue`,(`table_24532`.`a` is false) AS `a IS FALSE`,if(ifnull(`table_24532`.`a`,1),0,1) AS `old_isfalse`,(`table_24532`.`a` is not false) AS `a IS NOT FALSE`,if(ifnull(`table_24532`.`a`,1),1,0) AS `old_isnotfalse` from `table_24532` latin1 latin1_swedish_ci insert into table_24532 values (0, 0, 0, 0); select * from view_24532_b; a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 0 0 1 1 1 1 0 0 update table_24532 set a=1; select * from view_24532_b; a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 1 1 0 0 0 0 1 1 update table_24532 set a=NULL; select * from view_24532_b; a IS TRUE old_istrue a IS NOT TRUE old_isnottrue a IS FALSE old_isfalse a IS NOT FALSE old_isnotfalse 0 0 1 1 0 0 1 1 drop view view_24532_a; drop view view_24532_b; drop table table_24532; 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; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; SELECT * FROM t1 UNION SELECT * FROM v1; a 1 2 3 EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 2 UNION t1 ALL NULL NULL NULL NULL 3 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL SELECT * FROM v1 UNION SELECT * FROM t1; a 1 2 3 EXPLAIN SELECT * FROM v1 UNION SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 2 UNION t1 ALL NULL NULL NULL NULL 3 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; a 1 2 3 EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 2 UNION t1 ALL NULL NULL NULL NULL 3 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort DROP VIEW v1; DROP TABLE t1; CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; SELECT * FROM v1; col 1.23457 DESCRIBE v1; Field Type Null Key Default Extra col decimal(7,5) NO 0.00000 DROP VIEW v1; CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` latin1 latin1_swedish_ci DROP VIEW v1; CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT, c INT DEFAULT 0); INSERT INTO t1 (a) VALUES (1), (2); INSERT INTO t2 (b) VALUES (1), (2); CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; SELECT * FROM v1; b c 1 0 2 0 UPDATE v1 SET c=1 WHERE b=1; SELECT * FROM v1; b c 1 1 2 0 DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (id int); CREATE TABLE t2 (id int, c int DEFAULT 0); INSERT INTO t1 (id) VALUES (1); INSERT INTO t2 (id) VALUES (1); CREATE VIEW v1 AS SELECT t2.c FROM t1, t2 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; UPDATE v1 SET c=1; DROP VIEW v1; DROP TABLE t1,t2; CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); CREATE TABLE t2 (a2 INT); CREATE TABLE t3 (a3 INT); CREATE TABLE t4 (a4 INT); INSERT INTO t1 (a1) VALUES (1),(2); INSERT INTO t2 (a2) VALUES (1),(2); INSERT INTO t3 (a3) VALUES (1),(2); INSERT INTO t4 (a4) VALUES (1),(2); CREATE VIEW v1 AS SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 WITH CHECK OPTION; SELECT * FROM v1; a1 c 1 0 2 0 UPDATE v1 SET c=3; ERROR HY000: CHECK OPTION failed 'test.v1' PREPARE t FROM 'UPDATE v1 SET c=3'; EXECUTE t; ERROR HY000: CHECK OPTION failed 'test.v1' EXECUTE t; ERROR HY000: CHECK OPTION failed 'test.v1' INSERT INTO v1(a1, c) VALUES (3, 3); ERROR HY000: CHECK OPTION failed 'test.v1' UPDATE v1 SET c=1 WHERE a1=1; SELECT * FROM v1; a1 c 1 1 2 0 SELECT * FROM t1; a1 c 1 1 2 0 CREATE VIEW v2 AS SELECT t1.a1, t1.c FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) JOIN (t3 JOIN t4 ON t3.a3=t4.a4) ON t2.a2=t3.a3 WITH CHECK OPTION; SELECT * FROM v2; a1 c 1 1 2 0 UPDATE v2 SET c=3; ERROR HY000: CHECK OPTION failed 'test.v2' PREPARE t FROM 'UPDATE v2 SET c=3'; EXECUTE t; ERROR HY000: CHECK OPTION failed 'test.v2' EXECUTE t; ERROR HY000: CHECK OPTION failed 'test.v2' INSERT INTO v2(a1, c) VALUES (3, 3); ERROR HY000: CHECK OPTION failed 'test.v2' UPDATE v2 SET c=2 WHERE a1=1; SELECT * FROM v2; a1 c 1 2 2 0 SELECT * FROM t1; a1 c 1 2 2 0 DROP VIEW v1,v2; DROP TABLE t1,t2,t3,t4; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; b SUM(a) 3 4 EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; a SUM(b) 1 6 2 3 EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; a SUM(b) 1 10 EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 ( person_id int NOT NULL PRIMARY KEY, username varchar(40) default NULL, status_flg char(1) NOT NULL default 'A' ); CREATE TABLE t2 ( person_role_id int NOT NULL auto_increment PRIMARY KEY, role_id int NOT NULL, person_id int NOT NULL, INDEX idx_person_id (person_id), INDEX idx_role_id (role_id) ); CREATE TABLE t3 ( role_id int NOT NULL auto_increment PRIMARY KEY, role_name varchar(100) default NULL, app_name varchar(40) NOT NULL, INDEX idx_app_name(app_name) ); CREATE VIEW v1 AS SELECT profile.person_id AS person_id FROM t1 profile, t2 userrole, t3 role WHERE userrole.person_id = profile.person_id AND role.role_id = userrole.role_id AND profile.status_flg = 'A' ORDER BY profile.person_id,role.app_name,role.role_name; INSERT INTO t1 VALUES (6,'Sw','A'), (-1136332546,'ols','e'), (0,' *\n','0'), (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10); INSERT INTO t3 VALUES (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'), (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS'); EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE profile const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort 1 SIMPLE userrole ref idx_person_id,idx_role_id idx_person_id 4 const 2 1 SIMPLE role eq_ref PRIMARY PRIMARY 4 test.userrole.role_id 1 SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; a b 6 6 6 6 DROP VIEW v1; DROP TABLE t1,t2,t3; create table t1 (i int); insert into t1 values (1), (2), (1), (3), (2), (4); create view v1 as select distinct i from t1; select * from v1; i 1 2 3 4 select table_name, is_updatable from information_schema.views where table_name = 'v1'; table_name is_updatable v1 NO drop view v1; drop table t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM v1 USE KEY(non_existant); ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' SELECT * FROM v1 FORCE KEY(non_existant); ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' SELECT * FROM v1 IGNORE KEY(non_existant); ERROR 42000: Key 'non_existant' doesn't exist in table 'v1' DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, PRIMARY KEY(a), KEY (b)); INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY) FORCE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci EXPLAIN SELECT * FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 15 CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; SHOW CREATE VIEW v2; View Create View character_set_client collation_connection v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a` latin1 latin1_swedish_ci EXPLAIN SELECT * FROM v2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; SHOW CREATE VIEW v3; View Create View character_set_client collation_connection v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci EXPLAIN SELECT * FROM v3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort DROP VIEW v1; DROP VIEW v2; DROP VIEW v3; DROP TABLE t1; # # Bug#29477 Not all fields of the target table were checked to have # a default value when inserting into a view. # create table t1(f1 int, f2 int not null); create view v1 as select f1 from t1; insert into v1 values(1); Warnings: Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value set @old_mode=@@sql_mode; set @@sql_mode=traditional; insert into v1 values(1); ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value set @@sql_mode=@old_mode; drop view v1; drop table t1; create table t1 (a int, key(a)); create table t2 (c int); create view v1 as select a b from t1; create view v2 as select 1 a from t2, v1 where c in (select 1 from t1 where b = a); insert into t1 values (1), (1); insert into t2 values (1), (1); prepare stmt from "select * from v2 where a = 1"; execute stmt; a 1 1 1 1 drop view v1, v2; drop table t1, t2; CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT p.a AS a FROM t1 p, t1 q; INSERT INTO t1 VALUES (1), (1); SELECT MAX(a), COUNT(DISTINCT a) FROM v1 GROUP BY a; MAX(a) COUNT(DISTINCT a) 1 1 DROP VIEW v1; DROP TABLE t1; # ----------------------------------------------------------------- # -- Bug#34337 Server crash when Altering a view using a table name. # ----------------------------------------------------------------- DROP TABLE IF EXISTS t1; CREATE TABLE t1(c1 INT); SELECT * FROM t1; c1 ALTER ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW t1 (c2) AS SELECT (1); ERROR HY000: 'test.t1' is not VIEW DROP TABLE t1; # -- End of test case for Bug#34337. # ----------------------------------------------------------------- # -- Bug#35193 VIEW query is rewritten without "FROM DUAL", # -- causing syntax error # ----------------------------------------------------------------- CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1; SELECT * FROM v1; 1 1 SHOW CREATE TABLE v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from DUAL where 1 latin1 latin1_swedish_ci DROP VIEW v1; # -- End of test case for Bug#35193. CREATE VIEW v1 AS SELECT 1; DROP VIEW v1; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); INSERT INTO t1 VALUES (1,1), (2,2), (3,3); SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2; c1 c2 2 2 SELECT * FROM t1 USE INDEX (c2) WHERE c2=2; c1 c2 2 2 CREATE VIEW v1 AS SELECT c1, c2 FROM t1; SHOW INDEX FROM v1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2; ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2; ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2; ERROR 42000: Key 'PRIMARY' doesn't exist in table 'v1' SELECT * FROM v1 USE INDEX (c2) WHERE c2=2; ERROR 42000: Key 'c2' doesn't exist in table 'v1' SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2; ERROR 42000: Key 'c2' doesn't exist in table 'v1' SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2; ERROR 42000: Key 'c2' doesn't exist in table 'v1' DROP VIEW v1; DROP TABLE t1; # ----------------------------------------------------------------- # -- Bug#40825: Error 1356 while selecting from a view # -- with a "HAVING" clause though query works # ----------------------------------------------------------------- CREATE TABLE t1 (c INT); CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having (`view_column` <> 0) latin1 latin1_swedish_ci SELECT * FROM v1; view_column DROP VIEW v1; DROP TABLE t1; # -- End of test case for Bug#40825 # ----------------------------------------------------------------- # -- End of 5.0 tests. # ----------------------------------------------------------------- DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; USE `d-1`; CREATE TABLE `t-1` (c1 INT); CREATE VIEW `v-1` AS SELECT c1 FROM `t-1`; SHOW TABLES; Tables_in_d-1 t-1 v-1 RENAME TABLE `t-1` TO `t-2`; RENAME TABLE `v-1` TO `v-2`; SHOW TABLES; Tables_in_d-1 t-2 v-2 DROP TABLE `t-2`; DROP VIEW `v-2`; DROP DATABASE `d-1`; USE test; # # Bug#26676 VIEW using old table schema in a session. # DROP VIEW IF EXISTS v1; DROP TABLE IF EXISTS t1; CREATE TABLE t1(c1 INT, c2 INT); INSERT INTO t1 VALUES (1, 2), (3, 4); SELECT * FROM t1; c1 c2 1 2 3 4 CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM v1; c1 c2 1 2 3 4 ALTER TABLE t1 ADD COLUMN c3 INT AFTER c2; SELECT * FROM t1; c1 c2 c3 1 2 NULL 3 4 NULL SELECT * FROM v1; c1 c2 1 2 3 4 SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` latin1 latin1_swedish_ci DROP VIEW v1; DROP TABLE t1; # End of test case for Bug#26676. # ----------------------------------------------------------------- # -- Bug#32538 View definition picks up character set, but not collation # ----------------------------------------------------------------- DROP VIEW IF EXISTS v1; SET collation_connection = latin1_general_ci; CREATE VIEW v1 AS SELECT _latin1 'text1' AS c1, 'text2' AS c2; SELECT COLLATION(c1), COLLATION(c2) FROM v1; COLLATION(c1) COLLATION(c2) latin1_swedish_ci latin1_general_ci SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'text1' AS `c1`,'text2' AS `c2` latin1 latin1_general_ci SELECT * FROM v1 WHERE c1 = 'text1'; ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_general_ci,COERCIBLE) for operation '=' SELECT * FROM v1 WHERE c2 = 'text2'; c1 c2 text1 text2 use test; SET names latin1; SELECT COLLATION(c1), COLLATION(c2) FROM v1; COLLATION(c1) COLLATION(c2) latin1_swedish_ci latin1_general_ci SELECT * FROM v1 WHERE c1 = 'text1'; c1 c2 text1 text2 SELECT * FROM v1 WHERE c2 = 'text2'; ERROR HY000: Illegal mix of collations (latin1_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' DROP VIEW v1; # -- End of test case for Bug#32538. drop view if exists a; drop procedure if exists p; create procedure p() begin declare continue handler for sqlexception begin end; create view a as select 1; end| call p(); call p(); drop view a; drop procedure p; # # Bug #44860: ALTER TABLE on view crashes server # CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT a FROM t1; ALTER TABLE v1; DROP VIEW v1; DROP TABLE t1; # # Bug#48449: hang on show create view after upgrading when # view contains function of view # DROP VIEW IF EXISTS v1,v2; DROP TABLE IF EXISTS t1,t2; DROP FUNCTION IF EXISTS f1; CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); CREATE FUNCTION f1() RETURNS INT BEGIN SELECT a FROM v2 INTO @a; RETURN @a; END// # Trigger pre-locking when opening v2. CREATE VIEW v1 AS SELECT f1() FROM t1; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f1`() AS `f1()` from `t1` latin1 latin1_swedish_ci Warnings: Note 1599 View `test`.`v2` has no creation context DROP VIEW v1,v2; DROP TABLE t1,t2; DROP FUNCTION f1; CREATE TABLE t1(f1 INT); INSERT INTO t1 VALUES (); CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); DROP VIEW v1; DROP TABLE t1; # # Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 # CREATE TABLE t1 (a CHAR(1) CHARSET latin1, b CHAR(1) CHARSET utf8); CREATE VIEW v1 AS SELECT 1 from t1 WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); DROP VIEW v1; DROP TABLE t1; # # Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846 # CREATE TABLE t1(a int); CREATE VIEW v1 AS SELECT 1 FROM t1 GROUP BY SUBSTRING(1 FROM (SELECT 3 FROM t1 WHERE a >= ANY(SELECT 1))); DROP VIEW v1; DROP TABLE t1; # # Bug#57352 valgrind warnings when creating view # CREATE VIEW v1 AS SELECT 1 IN (1 LIKE 2,0) AS f; DROP VIEW v1; # # Bug 11829681 - 60295: ERROR 1356 ON VIEW THAT EXECUTES FINE AS A QUERY # CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT s.* FROM t1 s, t1 b HAVING a; SELECT * FROM v1; a DROP VIEW v1; DROP TABLE t1; # # LP BUG#777809 (a retrograded condition for view ON) # CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ; INSERT IGNORE INTO t1 VALUES (20, 2); CREATE TABLE t2 ( f3 int NOT NULL ) ; INSERT IGNORE INTO t2 VALUES (7); CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0'; EXECUTE prep_stmt; f6 2 EXECUTE prep_stmt; f6 2 drop view v2; drop table t1,t2; # ----------------------------------------------------------------- # -- End of 5.1 tests. # -----------------------------------------------------------------