diff options
Diffstat (limited to 'mysql-test/t/view.test')
| -rw-r--r-- | mysql-test/t/view.test | 1889 |
1 files changed, 1889 insertions, 0 deletions
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test new file mode 100644 index 00000000000..5454b3409da --- /dev/null +++ b/mysql-test/t/view.test @@ -0,0 +1,1889 @@ +--disable_warnings +drop table if exists t1,t2,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; +--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 8 # 12 # 13 # +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; + +# 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; + +# WL #2486 should enable these tests +#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; + + +# +# 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; + +# +# 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; + +# +# 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; + +# +# 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; + +# +# 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; +create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; +-- error 1093 +update v2 set col1 = (select max(col1) from v1); +-- error 1093 +update v2 set col1 = (select max(col1) from t1); +-- error 1093 +update v2 set col1 = (select max(col1) from v2); +-- error 1093 +update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; +-- error 1093 +update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; +-- error 1093 +update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; +-- error 1093 +update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; +-- error 1093 +update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; +-- error 1093 +update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; +-- error 1093 +update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; +-- error 1093 +update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +-- error 1093 +update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; +-- error 1093 +update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; +-- error 1093 +update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +-- error 1093 +update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; +-- error 1093 +update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; +-- error 1093 +update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; +-- error 1093 +update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; +-- error 1093 +update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; +-- error 1093 +update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; +-- error 1093 +update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; +-- error 1093 +update v3 set v3.col1 = (select max(col1) from v1); +-- error 1093 +update v3 set v3.col1 = (select max(col1) from t1); +-- error 1093 +update v3 set v3.col1 = (select max(col1) from v2); +-- error 1093 +update v3 set v3.col1 = (select max(col1) from v3); +-- error 1093 +delete from v2 where col1 = (select max(col1) from v1); +-- error 1093 +delete from v2 where col1 = (select max(col1) from t1); +-- error 1093 +delete from v2 where col1 = (select max(col1) from v2); +-- error 1093 +delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; +-- error 1093 +delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; +-- error 1093 +delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; +-- error 1093 +delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; +-- error 1093 +delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; +-- error 1093 +delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; +-- error 1093 +delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; +-- error 1093 +delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; +-- error 1093 +delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; +-- error 1093 +insert into v2 values ((select max(col1) from v1)); +-- error 1093 +insert into t1 values ((select max(col1) from v1)); +-- error 1093 +insert into v2 values ((select max(col1) from v1)); +-- error 1093 +insert into v2 values ((select max(col1) from t1)); +-- error 1093 +insert into t1 values ((select max(col1) from t1)); +-- error 1093 +insert into v2 values ((select max(col1) from t1)); +-- error 1093 +insert into v2 values ((select max(col1) from v2)); +-- error 1093 +insert into t1 values ((select max(col1) from v2)); +-- error 1093 +insert into v2 values ((select max(col1) from v2)); +-- error 1093 +insert into v3 (col1) values ((select max(col1) from v1)); +-- error 1093 +insert into v3 (col1) values ((select max(col1) from t1)); +-- error 1093 +insert into v3 (col1) values ((select max(col1) from v2)); +#check with TZ tables in list +-- error 1093 +insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); +insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); +-- error 1048 +insert into mysql.time_zone values ('', (select CONVERT_TZ('20050101000000','UTC','MET') from t2)); +# temporary table algorithm view should be equal to subquery in the from clause +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; + +drop view v4,v3,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_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; + +# +# 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; +-- error 1395 +delete t1,v3 from t1,v3; +# 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; + +# +# Test case for bug #9398 CREATE TABLE with SELECT from a multi-table view +# +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; +CREATE TABLE t3 SELECT * FROM v1; +SELECT * FROM t3; + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + +# +# Test for BUG#8703 "insert into table select from view crashes" +# +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; + +# +# Test for BUG #6106: query over a view using subquery for the underlying table +# + +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); + +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); + +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); + +# Tests from the report for bug #6107 + +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)); + +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); + +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; + +# +# BUG#8490 Select from views containing subqueries causes server to hang +# forever. +# +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; +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; +drop view v1, v2; +drop table t1, t2, t3; + +# +# Test case for bug #8528: select from view over multi-table view +# +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; +CREATE VIEW v2 AS SELECT * FROM v1; +SELECT * FROM v2; + +DROP VIEW v2,v1; + +DROP TABLE t1, t2; +# +# Correct restoring view name in SP table locking BUG#9758 +# +create table t1 (a int); +create view v1 as select sum(a) from t1 group by a; +delimiter //; +create procedure p1() +begin +select * from v1; +end// +delimiter ;// +call p1(); +call p1(); +drop procedure p1; +drop view v1; +drop table t1; + +# +# Bug#7422 "order by" doesn't work +# +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; +DROP VIEW v1; +DROP TABLE t1, t2; +# +# using sum(distinct ) & avg(distinct ) in views (BUG#7015) +# +create table t1 (s1 int); +create view v1 as select sum(distinct s1) from t1; +select * from v1; +drop view v1; +create view v1 as select avg(distinct s1) from t1; +select * from v1; +drop view v1; +drop table t1; + +# +# using cast(... as decimal) in views (BUG#11387); +# +create view v1 as select cast(1 as decimal); +select * from v1; +drop view v1; + +# +# Bug#11298 insert into select from VIEW produces incorrect result when +# using ORDER BY +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; +drop view v1; +drop table t1,t2,t3; + +# +# Generation unique names for columns, and correct names check (BUG#7448) +# +# names with ' and \ +create view v1 as select '\\','\\shazam'; +select * from v1; +drop view v1; +create view v1 as select '\'','\shazam'; +select * from v1; +drop view v1; +# autogenerated names differ by case only +create view v1 as select 'k','K'; +select * from v1; +drop view v1; +create table t1 (s1 int); +# same autogenerated names +create view v1 as select s1, 's1' from t1; +select * from v1; +drop view v1; +create view v1 as select 's1', s1 from t1; +select * from v1; +drop view v1; +# set name as one of expected autogenerated +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +select * from v1; +drop view v1; +create view v1 as select 1 as My_exp_s1, 's1', s1 from t1; +select * from v1; +drop view v1; +# set name conflict with autogenerated names +create view v1 as select 1 as s1, 's1', 's1' from t1; +select * from v1; +drop view v1; +create view v1 as select 's1', 's1', 1 as s1 from t1; +select * from v1; +drop view v1; +# underlying field name conflict with autogenerated names +create view v1 as select s1, 's1', 's1' from t1; +select * from v1; +drop view v1; +create view v1 as select 's1', 's1', s1 from t1; +select * from v1; +drop view v1; +# underlying field name conflict with set name +-- error 1060 +create view v1 as select 1 as s1, 's1', s1 from t1; +-- error 1060 +create view v1 as select 's1', s1, 1 as s1 from t1; +drop table t1; +# set names differ by case only +-- error 1060 +create view v1(k, K) as select 1,2; + +# +# using time_format in view (BUG#7521) +# +create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; +select * from v1; +drop view v1; + +# +# evaluation constant functions in WHERE (BUG#4663) +# +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; +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; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); +SHOW CREATE VIEW v1; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP TABLE t1; + +# +# checking views after some view with error (BUG#11337) +# +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; +drop view v1, v2, v3, v4, v5, v6; +drop table t2; + +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; +create function f1 () returns int return (select max(col1) from t1); +DROP TABLE t1; +# following will show underlying table until BUG#11555 fix +CHECK TABLE v1, v2, v3, v4, v5, v6; +drop function f1; +drop function f2; +drop view v1, v2, v3, v4, v5, v6; +drop table t2,t3; + +# +# bug #11325 Wrong date comparison in views +# +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'; +select * from v1 where '2005.02.02'=f1; +drop view v1; +drop table t1; + +# +# using encrypt & substring_index in view (BUG#7024) +# +CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); +disable_result_log; +SELECT * FROM v1; +enable_result_log; +drop view v1; +CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1); +SELECT * FROM v1; +drop view v1; + +# +# Using var_samp with view (BUG#10651) +# +create table t1 (s1 int); +create view v1 as select var_samp(s1) from t1; +show create view v1; +drop view v1; +drop table t1; + +# +# Correct inserting data check (absence of default value) for view +# underlying tables (BUG#6443) +# +set sql_mode='strict_all_tables'; +CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL) ENGINE = INNODB; +CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; +CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; +-- error 1364 +INSERT INTO t1 (col1) VALUES(12); +-- error 1423 +INSERT INTO v1 (vcol1) VALUES(12); +-- error 1423 +INSERT INTO v2 (vcol1) VALUES(12); +set sql_mode=default; +drop view v2,v1; +drop table t1; + +# +# Bug#11399 Use an alias in a select statement on a view +# +create table t1 (f1 int); +insert into t1 values (1); +create view v1 as select f1 from t1; +select f1 as alias from v1; +drop view v1; +drop table t1; + +# +# Test for bug #6120: SP cache to be invalidated when altering a view +# + +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; +CREATE PROCEDURE p1 () SELECT * FROM v1; +CALL p1(); +ALTER VIEW v1 AS SELECT s1 AS s1, s2 AS s2 FROM t1; +CALL p1(); +DROP VIEW v1; +CREATE VIEW v1 AS SELECT s2 AS s1, s1 AS s2 FROM t1; +CALL p1(); + +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +# +# Test for bug #11709 View was ordered by wrong column +# +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; +drop view v1; +drop table t1; + +# +# Test for bug #11771: wrong query_id in SELECT * FROM <view> +# +CREATE TABLE t1 (f1 char) ENGINE = innodb; +INSERT INTO t1 VALUES ('A'); +CREATE VIEW v1 AS SELECT * FROM t1; + +INSERT INTO t1 VALUES('B'); +SELECT * FROM v1; +SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1; + +# +# opening table in correct locking mode (BUG#9597) +# +CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); +CREATE OR REPLACE VIEW v1 AS SELECT * from t1; +DROP PROCEDURE IF EXISTS p1; +delimiter //; +CREATE PROCEDURE p1 ( ) +BEGIN + DO (SELECT @next := IFNULL(max(bug_table_seq),0) + 1 FROM v1); + INSERT INTO t1 VALUES (1); +END // +delimiter ;// +CALL p1(); +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned +# subtime() in view +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; +drop view v1; +drop table t1; + +# +# Test for bug #11412: query over a multitable view with GROUP_CONCAT +# +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; +SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; + +DROP VIEW v1; +DROP TABLE t1,t2; |