--
-- ### Execution plan checks
--

create table t1 ( primary a int not null, b string(30));
create table t2 ( primary c int not null, d string(30));
create table t3 ( primary e int not null, f string(30));
create table t4 ( primary g int not null, h string(30));
create table t5 ( primary i int not null, j string(30));
create table t6 ( primary k int not null, l string(30));
create table t7 ( primary m int not null, n string(30));


create index t4_idx1 on t4(h);
create index t5_idx1 on t5(i);
create index t5_idx2 on t5(j);

create view v1 as select tx.a as a, ty.d as d from t1 tx, t2 ty where tx.a = ty.c;
create view v2 as select tx.a as a, ty.e as e from v1 tx, t3 ty where tx.a = ty.e;

insert into t1 values ( 1, 'xxx');
insert into t1 values ( 2, 'yyy');
insert into t1 values ( 3, 'zzz');

insert into t2 values ( 1, 'xxx');
insert into t2 values ( 2, 'III');
insert into t2 values ( 3, 'OOOO');

insert into t3 values ( 1, 'Iwan');
insert into t3 values ( 2, 'xxx');
insert into t3 values ( 3, 'hurga');


insert into t4 values ( 1, 'alpha');
insert into t4 values ( 2, 'beta');
insert into t4 values ( 3, 'gamma');
insert into t4 values ( 4, 'delta');

plan select f, ( select l from t6 where k = tx.e )  from t3 tx;

plan select a, ( select l from t6 where k = vx.e ) 
     from v2 vx, t4 
      where v2.a = t4.g and t4.h = 'beta' and t4.g in ( select ( select m from t7 where m = tx.i ) from t5 tx where j = 'xxx');

drop view v1;
drop view v2;

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;
drop table t7;
