drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
drop table if exists t9;
drop table if exists t1a;
drop table if exists b;
drop table if exists v1;
drop table if exists v2;
drop table if exists v3;
drop table if exists v4;
drop table if exists v5;
drop table if exists v6;

drop view if exists t1;
drop view if exists t2;
drop view if exists t1a;
drop view if exists v;
drop view if exists v1;
drop view if exists v2;
drop view if exists v3;
drop view if exists v4;
drop view if exists v5;
drop view if exists v6;

create table t1 (a int) engine=infinidb;
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 order by 1;
drop view v1;
drop table t1;


create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3), (4);
create view v1 as select a+1 from t1;
create table t2 select * from v1;
select * from t2;
drop view v1;
drop table t1;
drop table t2 restrict;



create table t1 (a int, b int) engine=infinidb;
insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100);
create view v1 (c) as select b from t1 where a<=3;
select * from v1;
create view v2 (c) as select b from t1 where a>=2;
select v1.* from v1, v2 where v1.c = v2.c;
drop view v1;
drop view v2;
drop table t1;



create table t1 (a int, b int, c int) engine=infinidb;
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;
select * from v1;
select * from v2;
select a.y as a1, b.y as b1 from v1 a, v2 b where a.x = b.x;
drop table t1;
drop view v1;
drop view v2;



create table t1 (a int) engine=infinidb;
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) order by 1,2;
drop table t1;
drop view v1;



create table t1 (a int) engine=infinidb;
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;
drop view v2;



create table t1 (t_column char(1)) engine=infinidb;
insert into t1 values ('a');
create view v1 as select 'a';
select v1.a, t1.t_column from v1, t1 where v1.a = t1.t_column;
drop view v1;
drop table t1;



create table t1 (col1 char(5),col2 char(5)) engine=infinidb;
insert into t1 values ('abc', 'def');
create view v1 as select * from t1;
alter table t1 add column col3 char(5);
insert into t1 values ('ghi', 'jkl', 'mno');
select * from v1;
drop view v1;
drop table t1;



create table t1 (col1 char(5),col2 char(5)) engine=infinidb;
create view v1 (col1,col2) as select col1,col2 from t1;
insert into t1 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) order by 1;
select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1) order by 1;
drop view v1;
drop table t1;