view.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

#
# 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 is_updatable from information_schema.views;
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 1176
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
#
set names utf8;
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ü;
set names latin1;

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

#
# renaming views
#
create table t1 (a int);
create view v1 as select a from t1;
create view v3 as select a from t1;
create database mysqltest;
-- error 1450
rename table v1 to mysqltest.v1;
rename table v1 to v2;
--error 1050
rename table v3 to v1, v2 to t1;
drop table t1;
drop view v2,v3;
drop database mysqltest;

#
# 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();
-- error ER_VIEW_SELECT_DERIVED
create view v1 as select 1 from (select 1) as d1;
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 1443
update v2 set col1 = (select max(col1) from v1);
-- error 1443
update v2 set col1 = (select max(col1) from t1);
-- error 1093
update v2 set col1 = (select max(col1) from v2);
-- error 1443
update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-- error 1443
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 1443
update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-- error 1443
update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
-- error 1443
update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
-- error 1443
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 1443
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 1443
update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-- error 1443
update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-- error 1443
update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
-- error 1443
update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-- error 1443
update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-- error 1443
update v3 set v3.col1 = (select max(col1) from v1);
-- error 1443
update v3 set v3.col1 = (select max(col1) from t1);
-- error 1443
update v3 set v3.col1 = (select max(col1) from v2);
-- error 1093
update v3 set v3.col1 = (select max(col1) from v3);
-- error 1443
delete from v2 where col1 = (select max(col1) from v1);
-- error 1443
delete from v2 where col1 = (select max(col1) from t1);
-- error 1093
delete from v2 where col1 = (select max(col1) from v2);
-- error 1443
delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-- error 1443
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 1443
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 1443
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 1443
delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
-- error 1443
delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
-- error 1443
insert into v2 values ((select max(col1) from v1));
-- error 1443
insert into t1 values ((select max(col1) from v1));
-- error 1443
insert into v2 values ((select max(col1) from v1));
-- error 1443
insert into v2 values ((select max(col1) from t1));
-- error 1093
insert into t1 values ((select max(col1) from t1));
-- error 1443
insert into v2 values ((select max(col1) from t1));
-- error 1093
insert into v2 values ((select max(col1) from v2));
-- error 1443
insert into t1 values ((select max(col1) from v2));
-- error 1093
insert into v2 values ((select max(col1) from v2));
-- error 1443
insert into v3 (col1) values ((select max(col1) from v1));
-- error 1443
insert into v3 (col1) values ((select max(col1) from t1));
-- error 1443
insert into v3 (col1) values ((select max(col1) from v2));
#check with TZ tables in list
-- error 1443
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;