--disable_warnings drop table if exists t1,t2,`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; --enable_warnings use test; # # some basic test of views and its functionality # # create view on nonexistent table -- error 1146 create view v1 (c,d) as select a,b from t1; create temporary table t1 (a int, b int); # view on temporary table -- error 1352 create view v1 (c) as select b+1 from 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); # view with variable -- error 1351 create view v1 (c,d) as select a,b+@@global.max_user_connections from t1; # simple view create view v1 (c) as select b+1 from t1; select c from v1; # temporary table should not hide table of view create temporary table t1 (a int, b int); # this is empty select * from t1; # but this based on normal t1 select c from v1; show create table v1; show create view v1; -- error 1347 show create view t1; drop table t1; # try to use fields from underlying table -- error 1054 select a from v1; -- error 1054 select v1.a from v1; -- error 1054 select b from v1; -- error 1054 select v1.b from v1; # view with different algorithms (explain output differs) explain extended select c from v1; create algorithm=temptable view v2 (c) as select b+1 from t1; show create view v2; select c from v2; explain extended select c from v2; # try to use underlying table fields in VIEW creation process -- error 1054 create view v3 (c) as select a+1 from v1; -- error 1054 create view v3 (c) as select b+1 from v1; # VIEW on VIEW test with mixing different algorithms on different order create view v3 (c) as select c+1 from v1; select c from v3; explain extended select c from v3; create algorithm=temptable view v4 (c) as select c+1 from v2; select c from v4; explain extended select c from v4; create view v5 (c) as select c+1 from v2; select c from v5; explain extended select c from v5; create algorithm=temptable view v6 (c) as select c+1 from v1; select c from v6; explain extended select c from v6; # show table/table status test show tables; show full tables; --replace_column 12 # 13 # --replace_result 2147483647 38654705663 show table status; drop view v1,v2,v3,v4,v5,v6; # # alter/create view test # # view with subqueries of different types 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; select * from v2; # try to create VIEW with name of existing VIEW -- error 1050 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; # 'or replace' should work in this case 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; # try to ALTER unexisting VIEW drop view v2; -- error 1146 alter view v2 as select c, d from v1; # 'or replace' on unexisting view create or replace view v2 as select c, d from v1; # alter view on existing view alter view v1 (c,d) as select a,max(b) from t1 group by a; # check that created view works select * from v1; select * from v2; # simple test of grants grant create view on test.* to test@localhost; show grants for test@localhost; revoke create view on test.* from test@localhost; show grants for test@localhost; # try to drop nonexistent VIEW -- error 1051 drop view v100; # try to drop table with DROP VIEW -- error 1347 drop view t1; # try to drop VIEW with DROP TABLE -- error 1051 drop table v1; # try to drop table with DROP VIEW drop view v1,v2; drop table t1; # # outer left join with merged views # 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; select * from v2 natural left join t1; select * from v2 natural left join v1; drop view v1, v2; drop table t1; # # grant create view test # connect (root,localhost,root,,test); connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,test); connection user1; create view v1 as select * from mysqltest.t1; # try to modify view without DELETE privilege on it -- error 1142 alter view v1 as select * from mysqltest.t1; -- error 1142 create or replace view v1 as select * from mysqltest.t1; # no CRETE VIEW privilege -- error 1142 create view mysqltest.v2 as select * from mysqltest.t1; # no SELECT privilege -- error 1142 create view v2 as select * from mysqltest.t2; connection root; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; revoke all privileges on test.* from mysqltest_1@localhost; drop database mysqltest; drop view test.v1; # # grants per columns # # MERGE algorithm --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int); create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; select c from mysqltest.v1; # there are no privileges on column 'd' -- error 1143 select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; # TEMPORARY TABLE algorithm --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int); create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; select c from mysqltest.v1; # there are no privileges on column 'd' -- error 1143 select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; # # EXPLAIN rights # connection root; --disable_warnings create database mysqltest; --enable_warnings #prepare views and tables create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; grant select on mysqltest.v4 to mysqltest_1@localhost; connection user1; # all selects works select c from mysqltest.v1; select c from mysqltest.v2; select c from mysqltest.v3; select c from mysqltest.v4; # test of show coluns show columns from mysqltest.v1; show columns from mysqltest.v2; # but explain/show do not -- error 1345 explain select c from mysqltest.v1; -- error 1142 show create view mysqltest.v1; -- error 1345 explain select c from mysqltest.v2; -- error 1142 show create view mysqltest.v2; -- error 1345 explain select c from mysqltest.v3; -- error 1142 show create view mysqltest.v3; -- error 1345 explain select c from mysqltest.v4; -- error 1142 show create view mysqltest.v4; # allow to see one of underlying table connection root; grant select on mysqltest.t1 to mysqltest_1@localhost; connection user1; # EXPLAIN of view on above table works explain select c from mysqltest.v1; -- error 1142 show create view mysqltest.v1; explain select c from mysqltest.v2; -- error 1142 show create view mysqltest.v2; # but other EXPLAINs do not -- error 1345 explain select c from mysqltest.v3; -- error 1142 show create view mysqltest.v3; -- error 1345 explain select c from mysqltest.v4; -- error 1142 show create view mysqltest.v4; # allow to see any view in mysqltest database connection root; grant show view on mysqltest.* to mysqltest_1@localhost; connection user1; explain select c from mysqltest.v1; show create view mysqltest.v1; explain select c from mysqltest.v2; show create view mysqltest.v2; explain select c from mysqltest.v3; show create view mysqltest.v3; explain select c from mysqltest.v4; show create view mysqltest.v4; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; drop database mysqltest; # # DISTINCT option for VIEW # 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; explain select * from v1; select * from t1; drop view v1; drop table t1; # # syntax compatibility # create table t1 (a int); -- error 1368 create view v1 as select distinct a from t1 WITH CHECK OPTION; 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; # # aliases # 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; create algorithm=temptable view v2 (c) as select b+1 from t1; select test.c from v2 test; select test1.* from v1 test1, v2 test2 where test1.c=test2.c; select test2.* from v1 test1, v2 test2 where test1.c=test2.c; drop table t1; drop view v1,v2; # # LIMIT clause test # 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; explain select * from v1; drop view v1; drop table t1; # # CREATE ... SELECT view test # 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; select * from t2; drop view v1; drop table t1,t2; # # simple view + simple update # 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; # try to update expression -- error 1348 update v1 set c=a+c; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 update v2 set a=a+c; # updatable field of updateable view update v1 set a=a+c; select * from v1; select * from t1; drop table t1; drop view v1,v2; # # simple view + simple multi-update # 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; # try to update expression -- error 1348 update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; # updatable field of updateable view update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; select * from v1; select * from t1; drop table t1,t2; drop view v1,v2; # # UPDATE privileges on VIEW columns and whole VIEW # connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; connection user1; use mysqltest; # update with rights on VIEW column update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; select * from t1; update v1 set a=a+c; select * from t1; # update with rights on whole VIEW update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; select * from t1; update v2 set a=a+c; select * from t1; # no rights on column -- error 1143 update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; -- error 1143 update v2 set c=a+c; # no rights for view -- error 1142 update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; -- error 1142 update v3 set a=a+c; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; # # MERGE VIEW with WHERE clause # 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; # simple select and explaint to be sure that it is MERGE select * from v1; explain extended select * from v1; # update test update v1 set c=c+1; select * from t1; # join of such VIEWs test create view v2 (c) as select b from t1 where a>=3; select * from v1, v2; drop view v1, v2; drop table t1; # # simple view + simple delete # 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; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 delete from v2 where c < 4; # updatable field of updateable view delete from v1 where c < 4; select * from v1; select * from t1; drop table t1; drop view v1,v2; # # simple view + simple multi-delete # 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; # try to update VIEW with forced TEMPORARY TABLE algorithm -- error 1288 delete v2 from t2,v2 where t2.x=v2.a; # updatable field of updateable view delete v1 from t2,v1 where t2.x=v1.a; select * from v1; select * from t1; drop table t1,t2; drop view v1,v2; # # DELETE privileges on VIEW # connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; grant delete on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; connection user1; use mysqltest; # update with rights on VIEW column delete from v1 where c < 4; select * from t1; delete v1 from t2,v1 where t2.x=v1.c; select * from t1; # no rights for view -- error 1142 delete v2 from t2,v2 where t2.x=v2.c; -- error 1142 delete from v2 where c < 4; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; # # key presence check # 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; -- error 1288 update v2 set x=x+1 limit 1; set updatable_views_with_limit=YES; update v1 set x=x+1 limit 1; update v2 set x=x+1 limit 1; set updatable_views_with_limit=DEFAULT; show variables like "updatable_views_with_limit"; select * from t1; drop table t1; drop view v1,v2; # # simple insert # 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; # try insert to VIEW with fields duplicate -- error 1288 insert into v3 values (-60,4,30); # try insert to VIEW with expression in SELECT list -- error 1288 insert into v4 values (-60,4,30); # try insert to VIEW using temporary table algorithm -- error 1288 insert into v5 values (-60,4,30); 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; drop table t1; drop view v1,v2,v3,v4,v5; # # insert ... select # 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; # try insert to VIEW with fields duplicate -- error 1288 insert into v3 select c, b, a from t2; # try insert to VIEW with expression in SELECT list -- error 1288 insert into v4 select c, b, a from t2; # try insert to VIEW using temporary table algorithm -- error 1288 insert into v5 select c, b, a from t2; 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; drop table t1, t2; drop view v1,v2,v3,v4,v5; # # insert privileges on VIEW # connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (1,2), (2,3); create table mysqltest.t2 (x int, y int); insert into mysqltest.t2 values (3,4); create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; grant insert on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; connection user1; use mysqltest; # update with rights on VIEW column insert into v1 values (5,6); select * from t1; insert into v1 select x,y from t2; select * from t1; # no rights for view -- error 1142 insert into v2 values (5,6); -- error 1142 insert into v2 select x,y from t2; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; # # outer join based on VIEW with WHERE clause # 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); drop table t1; drop view v1; # # merging WHERE condition on VIEW on VIEW # 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; drop table t1; drop view v1,v2; # # VIEW on non-updatable view # 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; -- error 1288 update v2 set y=10 where y=2; drop table t1; drop view v1,v2; # # auto_increment field out of VIEW # 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(); insert into t1 (b) values (2); select last_insert_id(); select * from t1; drop view v1; drop table t1; # # test of CREATE VIEW privileges if we have limited privileges # connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); grant update on mysqltest.t1 to mysqltest_1@localhost; grant update(b) on mysqltest.t2 to mysqltest_1@localhost; grant create view,update on test.* to mysqltest_1@localhost; connection user1; create view v1 as select * from mysqltest.t1; create view v2 as select b from mysqltest.t2; # There are not rights on mysqltest.v1 -- error 1142 create view mysqltest.v1 as select * from mysqltest.t1; # There are not any rights on mysqltest.t2.a -- error 1143 create view v3 as select a from mysqltest.t2; # give CREATE VIEW privileges (without any privileges for result column) connection root; create table mysqltest.v3 (b int); grant create view on mysqltest.v3 to mysqltest_1@localhost; drop table mysqltest.v3; connection user1; create view mysqltest.v3 as select b from mysqltest.t2; # give UPDATE privileges connection root; grant create view, update on mysqltest.v3 to mysqltest_1@localhost; drop view mysqltest.v3; connection user1; create view mysqltest.v3 as select b from mysqltest.t2; # give UPDATE and INSERT privilege (to get more privileges then underlying # table) connection root; grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost; drop view mysqltest.v3; connection user1; -- error 1143 create view mysqltest.v3 as select b from mysqltest.t2; # If we would get more privileges on VIEW then we have on # underlying tables => creation prohibited connection root; create table mysqltest.v3 (b int); grant select(b) on mysqltest.v3 to mysqltest_1@localhost; drop table mysqltest.v3; connection user1; -- error 1142 create view mysqltest.v3 as select b from mysqltest.t2; # Expression need select privileges -- error 1143 create view v4 as select b+1 from mysqltest.t2; connection root; grant create view,update,select on test.* to mysqltest_1@localhost; connection user1; -- error 1143 create view v4 as select b+1 from mysqltest.t2; connection root; grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; connection user1; create view v4 as select b+1 from mysqltest.t2; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; # # VIEW fields quoting # set sql_mode='ansi'; create table t1 ("a*b" int); create view v1 as select "a*b" from t1; show create view v1; drop view v1; drop table t1; set sql_mode=default; # # VIEW without tables # create table t1 (t_column int); create view v1 as select 'a'; select * from v1, t1; drop view v1; drop table t1; # # quote mark inside table name # create table `t1a``b` (col1 char(2)); create view v1 as select * from `t1a``b`; select * from v1; describe v1; drop view v1; drop table `t1a``b`; # # Changing of underlying table # 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)); -- error 1356 insert into v1 values('a','aa'); drop table t1; -- error 1356 select * from v1; drop view v1; # # check of duplication of column names # -- error 1060 create view v1 (a,a) as select 'a','a'; # # SP variables inside view test # # QQ This can't be tested with the new table locking for functions, # QQ since views created in an SP can't be used within the same SP # QQ (just as for tables). Instead it fails with error 1146. #delimiter //; #create procedure p1 () begin declare v int; create view v1 as select v; end;// #delimiter ;// #-- error 1351 #call p1(); #drop procedure p1; # # updatablity should be transitive # create table t1 (col1 int,col2 char(22)); insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; create view v2 as select * from v1; update v2 set col2='Hello, view world'; select * from t1; drop view v2, v1; drop table t1; # # check 'use index' on view with temporary table # create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; -- error 1072 select b from v1 use index (some_index) where b=1; drop view v1; drop table t1; # # using VIEW fields several times in query resolved via temporary tables # 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); select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); drop view v1; drop table t1; # # Test of view updatability in prepared statement # create table t1 (a int); create view v1 as select a from t1; insert into t1 values (1); #update SET @v0 = '2'; PREPARE stmt FROM 'UPDATE v1 SET a = ?'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; #insert without field list SET @v0 = '3'; PREPARE stmt FROM 'insert into v1 values (?)'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; #insert with field list SET @v0 = '4'; PREPARE stmt FROM 'insert into v1 (a) values (?)'; EXECUTE stmt USING @v0; DEALLOCATE PREPARE stmt; select * from t1; drop view v1; drop table t1; # # error on preparation # -- error 1096 CREATE VIEW v02 AS SELECT * FROM DUAL; SHOW TABLES; # # EXISTS with UNION VIEW # CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2); select * from v1; drop view v1; # # using VIEW where table is required # create table t1 (col1 int,col2 char(22)); create view v1 as select * from t1; -- error 1347 create index i1 on v1 (col1); drop view v1; drop table t1; # # connection_id(), pi(), current_user(), version() representation test # CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version(); SHOW CREATE VIEW v1; drop view v1; # # VIEW built over UNION # 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; drop view v1; drop tables t1, t2; # # Aggregate functions in view list # 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; drop view v1; drop table t1; # # Showing VIEW with VIEWs in subquery # 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; drop view v2, v1; drop table t1, t2; # # SHOW VIEW view with name with spaces # CREATE VIEW `v 1` AS select 5 AS `5`; show create view `v 1`; drop view `v 1`; # # Removing database with .frm archives # 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; # # VIEW with full text # 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'); CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); select * from v1; drop view v1; drop table t1; # # distinct in temporary table with a VIEW # 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; select distinct a from v1 limit 2; select distinct a from t1 limit 2; prepare stmt1 from "select distinct a from v1 limit 2"; execute stmt1; execute stmt1; deallocate prepare stmt1; drop view v1; drop table t1; # # aggregate function of aggregate function # 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; drop view v1; drop table t1; # # VIEW of VIEW with column renaming # 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; show create view v3; drop view v3, v2, v1; drop table t2, t1; # # VIEW based on functions with complex names # create function `f``1` () returns int return 5; create view v1 as select test.`f``1` (); show create view v1; select * from v1; drop view v1; drop function `f``1`; # # tested problem when function name length close to ALIGN_SIZE # create function x () returns int return 5; create view v1 as select x (); select * from v1; drop view v1; drop function x; # # VIEW with collation # 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; show create view v2; drop view v2; drop table t2; # # order by refers on integer field # 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; drop view v1; drop table t1; # # VIEW over dropped function # create function x1 () returns int return 5; create table t1 (s1 int); create view v1 as select x1() from t1; drop function x1; -- error 1356 select * from v1; --replace_column 8 # 12 # 13 # show table status; drop view v1; drop table t1; # # VIEW with floating point (long number) as column # create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1; show create view v1; drop view v1; # # VIEWs with national characters # create table t� (c� char); create view v� as select c� from t�; insert into v� values ('�'); select * from v�; drop view v�; drop table t�; # # problem with used_tables() of outer reference resolved in VIEW # 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); drop view v1; drop table t1; # # view with cast operation # create view v1 as select cast(1 as char(3)); show create view v1; select * from v1; drop view v1; # # bug handling from VIEWs # 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; -- error 1242 select * from v4; drop view v4, v3, v2, v1; # # VIEW over SELECT with prohibited clauses # -- error 1350 create view v1 as select 5 into @w; -- error 1350 create view v1 as select 5 into outfile 'ttt'; create table t1 (a int); -- error 1350 create view v1 as select a from t1 procedure analyse(); drop table t1; # # INSERT into VIEW with ON DUPLICATE # 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; drop view v1; drop table t1; # # test of updating and fetching from the same table check # create table t1 (col1 int); create table t2 (col1 int); create view v1 as select * from t1; create view v2 as select * from v1; -- error 1093 update v2 set col1 = (select max(col1) from v1); #update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; -- error 1093 delete from v2 where col1 = (select max(col1) from v1); #delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; -- error 1093 insert into v2 values ((select max(col1) from v1)); drop view v2,v1; drop table t1,t2; # # HANDLER with VIEW # create table t1 (s1 int); create view v1 as select * from t1; -- error 1347 handler v1 open as xx; drop view v1; drop table t1; # # view with WHERE in nested join # 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; select * from t1 left join (t2 as t, t2) on t2.a=t1.a; drop view v1; drop table t1, t2; # # Collation with view update # 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; update v1 set s1='b'; select * from v1; update v1,t1 set v1.s1='c' where t1.s1=v1.s1; select * from v1; prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1"; set @arg='d'; execute stmt1 using @arg; select * from v1; set @arg='e'; execute stmt1 using @arg; select * from v1; deallocate prepare stmt1; drop view v1; drop table t1; # # test view with LOCK TABLES (work around) # 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; -- error 1100 select * from t2; drop view v1; drop table t1, t2; # # WITH CHECK OPTION insert/update test # create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; # simple insert insert into v1 values(1); -- error 1369 insert into v1 values(3); # simple insert with ignore insert ignore into v1 values (2),(3),(0); select * from t1; # prepare data for next check delete from t1; # INSERT SELECT test insert into v1 SELECT 1; -- error 1369 insert into v1 SELECT 3; # prepare data for next check create table t2 (a int); insert into t2 values (2),(3),(0); # INSERT SELECT with ignore test insert ignore into v1 SELECT a from t2; select * from t1; #simple UPDATE test update v1 set a=-1 where a=0; -- error 1369 update v1 set a=2 where a=1; select * from t1; # prepare data for next check update v1 set a=0 where a=0; insert into t2 values (1); # multiupdate test update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; select * from t1; # prepare data for next check update v1 set a=a+1; # multiupdate with ignore test update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a; select * from t1; drop view v1; drop table t1, t2; # # CASCADED/LOCAL CHECK OPTION test # 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); -- error 1369 insert into v2 values (0); -- error 1369 insert into v3 values (0); insert into v2 values (2); -- error 1369 insert into v3 values (2); select * from t1; drop view v3,v2,v1; drop table t1; # # CHECK OPTION with INSERT ... ON DUPLICATE KEY UPDATE # 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; -- error 1369 insert into v1 values (1) on duplicate key update a=2; insert ignore into v1 values (1) on duplicate key update a=2; select * from t1; drop view v1; drop table t1; # # check cyclic referencing protection on altering view # create table t1 (s1 int); create view v1 as select * from t1; create view v2 as select * from v1; -- error 1146 alter view v1 as select * from v2; -- error 1066 alter view v1 as select * from v1; -- error 1146 create or replace view v1 as select * from v2; -- error 1066 create or replace view v1 as select * from v1; drop view v2,v1; drop table t1; # # check altering differ options # create table t1 (a int); create view v1 as select * from t1; show create view v1; alter algorithm=undefined view v1 as select * from t1 with check option; show create view v1; alter algorithm=merge view v1 as select * from t1 with cascaded check option; show create view v1; alter algorithm=temptable view v1 as select * from t1; show create view v1; drop view v1; drop table t1; # # updating view with subquery in the WHERE clause # 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; update v2 set s1 = 0; select * from v2; select * from t2; # check it with check option alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; insert into v2 values (5); -- error 1369 update v2 set s1 = 1; insert into t1 values (1); update v2 set s1 = 1; select * from v2; select * from t2; # scheck how VIEWs with subqueries work with prepared statements prepare stmt1 from "select * from v2;"; execute stmt1; insert into t1 values (0); execute stmt1; deallocate prepare stmt1; drop view v2; drop table t1, t2; # # test of substring_index with view # 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; select substring_index(t,':',2) from v1; drop view v1; drop table t1; # # test of cascaded check option for whiew without WHERE clause # 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; -- error 1369 insert into v2 values (0); drop view v2, v1; drop table t1; # # inserting single value with check option failed always get error # create table t1 (s1 int); create view v1 as select * from t1 where s1 < 5 with check option; #single value -- error 1369 insert ignore into v1 values (6); #several values insert ignore into v1 values (6),(3); select * from t1; drop view v1; drop table t1; # # changing value by trigger and CHECK OPTION # 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; -- error 1369 insert into v1 values (0); select * from v1; select * from t1; drop trigger t1.t1_bi; drop view v1; drop table t1; # # CASCADED should be used for all underlaying VIEWs # 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; -- error 1369 insert into v2 values (0); select * from v2; select * from t1; drop view v2, v1; drop table t1; # # LOAD DATA with view and CHECK OPTION # # fixed length fields create table t1 (a int, b char(10)); create view v1 as select * from t1 where a != 0 with check option; -- error 1369 load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; select * from t1; select * from v1; delete from t1; load data infile '../../std_data/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; select * from t1; select * from v1; drop view v1; drop table t1; # variable length fields create table t1 (a text, b text); create view v1 as select * from t1 where a <> 'Field A' with check option; -- error 1369 load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; select concat('|',a,'|'), concat('|',b,'|') from t1; select concat('|',a,'|'), concat('|',b,'|') from v1; delete from t1; load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by ''''; select concat('|',a,'|'), concat('|',b,'|') from t1; select concat('|',a,'|'), concat('|',b,'|') from v1; drop view v1; drop table t1; # # user with global DB privileges # connection root; --disable_warnings create database mysqltest; --enable_warnings create table mysqltest.t1 (a int); grant all privileges on mysqltest.* to mysqltest_1@localhost; connection user1; use mysqltest; create view v1 as select * from t1; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; drop database mysqltest; # # Trys update table from which we select using views and subqueries # create table t1 (s1 smallint); create view v1 as select * from t1 where 20 < (select (s1) from t1); -- error 1288 insert into v1 values (30); create view v2 as select * from t1; create view v3 as select * from t1 where 20 < (select (s1) from v2); -- error 1288 insert into v3 values (30); create view v4 as select * from v2 where 20 < (select (s1) from t1); -- error 1093 insert into v4 values (30); drop view v4, v3, v2, v1; drop table t1; # # CHECK TABLE with VIEW # create table t1 (a int); create view v1 as select * from t1; check table t1,v1; check table v1,t1; drop table t1; check table v1; drop view v1; # # merge of VIEW with several tables # 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); # view over tables 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); explain extended select * from t3 left join v3 on (t3.a = v3.a); # view over views 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); explain extended select * from t3 left join v4 on (t3.a = v4.a); # PS with view over views prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);"; execute stmt1; execute stmt1; deallocate prepare stmt1; drop view v4,v3,v2,v1; drop tables t1,t2,t3; # # updating of join view # 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); # legal view for update 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; select * from t2; # view without primary key create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; set updatable_views_with_limit=NO; -- error 1288 update v2 set a= 10 where a=200 limit 1; set updatable_views_with_limit=DEFAULT; # just view selects select * from v3; select * from v2; # prepare statement with updating join view set @a= 10; set @b= 100; prepare stmt1 from "update v3 set a= ? where a=?"; execute stmt1 using @a,@b; select * from v3; set @a= 300; set @b= 10; execute stmt1 using @a,@b; select * from v3; deallocate prepare stmt1; drop view v3,v2; drop tables t1,t2; # # inserting/deleting join view # 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; # inserting into join view without field list -- error 1394 insert into v3 values (1,2); -- error 1394 insert into v3 select * from t2; # inserting in several tables of join view -- error 1393 insert into v3(a,b) values (1,2); -- error 1393 insert into v3(a,b) select * from t2; # correct inserts into join view 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; insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); select * from t1; select * from t2; # try delete from join view -- error 1395 delete from v3; -- error 1395 delete v3,t1 from v3,t1; # delete from t1 just to reduce result set size delete from t1; # prepare statement with insert join view 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; drop view v3; drop tables t1,t2; # # View field names should be case insensitive # create table t1(f1 int); create view v1 as select f1 from t1; select * from v1 where F1 = 1; drop view v1; drop table t1; # # Resolving view fields in subqueries in VIEW (Bug #6394) # 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); SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1); 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; select * from v2; select * from (select c1 from v2) X; drop view v2, v1; drop table t1, t2; # # view over other view setup (BUG#7433) # 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; drop view v2, v1; drop table t1, t2; # # view and group_concat() (BUG#7116) # 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; select col1,group_concat(col2,col3) from v1 group by col1; drop view v1; drop table t1; # # Item_ref resolved as view field (BUG#6894) # create table t1 (s1 int, s2 char); create view v1 as select s1, s2 from t1; -- error 1054 select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); drop view v1; drop table t1;