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;
prepare create_view from "create view v1 as select a from t1";
execute create_view;
insert into t1 values (1), (2), (3);
prepare prep_select from "select v_1.a from v1 v_1 where v_1.a < 3";
execute prep_select;
drop view v1;
drop table t1;


CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version();
select f2 from v1;
drop view v1;



create table t1 (s1 int) engine=infinidb;
create table t2 (s2 int) engine=infinidb;
insert into t1 values (1), (2);
insert into t2 values (2), (3);
create view v1 as select * from t1 union all select * from t2;
select * from v1 order by 1;
create view v2 as select * from t1 union select * from t2;
select * from v2 order by 1;
drop view v1;
drop view v2;
drop table t1;
drop table t2;



create table t1 (col1 int) engine=infinidb;
insert into t1 values (1);
create view v1 as select count(*) from t1;
select * from v1 order by 1;
insert into t1 values (null);
select * from v1 order by 1;
drop view v1;
drop table t1;



create table t1 (a int) engine=infinidb;
create table t2 (a int) engine=infinidb;
insert into t1 values (1), (2);
insert into t2 values (1), (2), (3);
create view v1 as select a from t1;
create view v2 as select a from t2 where a in (select a from v1);
select * from v1 order by 1;
select * from v2 order by 1;
drop view v2;
drop view v1;
drop table t1;
drop table t2;



create table t1 (a int) engine=infinidb;
insert into t1 values (1),(1),(2),(2),(3),(3);
create view v1 as select a from t1;
select distinct a from v1 order by 1;
select distinct a from v1 order by 1 limit 2;
select distinct a from t1 order by 1 limit 2;
prepare stmt1 from "select distinct a from v1 order by 1 limit 2";
execute stmt1;
drop view v1;
drop table t1;



create table t1 (tg_column bigint) engine=infinidb;
insert into t1 values (1), (2), (3), (4);
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;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2);
create view v1 as select 5 from t1;
select * from v1 order by 1;
drop view v1;
drop table t1;



create table t1 (a int, b int) engine=infinidb;
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;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2);
create view v1 as select a from t1;
create view v3 as select a from t1;
rename table v1 to v2;
select * from v2 order by 1;
rename table v3 to v1, v2 to v3;
select * from v1 order by 1;
select * from v3 order by 1;
drop table t1;
drop view v1;
drop view v3;



create table t1(a int) engine=infinidb;
insert into t1 values (0), (1), (2), (3);
create table t2 (a int) engine=infinidb;
insert into t2 values (2), (3);
create view v1 as select a from t1 where a > 1;

-- Tables are not fully joined from the following two queries.
select * from t1 left join (t2 as t, v1) on v1.a=t1.a order by 1;
select * from t1 left join (t2 as t, t2) on t2.a=t1.a order by 1;
drop view v1;
drop table t1;
drop table t2;