create schema test_unione_mix_table;
set current_schema='test_unione_mix_table';

create type composite_type as (c1 int, c2 boolean, c3 money, c4 text, c5 date);

create table t1
(
  c1 tinyint,
  c2 smallint,
  c3 integer,
  c4 bigint,
  c5 money,
  c6 boolean, 
  c7 char(10),
  c8 character(10),
  c9 nchar(10),
  c10 varchar(10),
  c11 text,
  c12 clob,
  c13 blob,
  c14 bytea,
  c15 date,
  c16 time without time zone,
  c17 reltime,
  c18 composite_type
)
with (storage_type=USTORE);

create table t2
(
  c1 tinyint,
  c2 smallint,
  c3 integer,
  c4 bigint,
  c5 money,
  c6 boolean, 
  c7 char(10),
  c8 character(10),
  c9 nchar(10),
  c10 varchar(10),
  c11 text,
  c12 clob,
  c13 blob,
  c14 bytea,
  c15 date,
  c16 time without time zone,
  c17 reltime,
  c18 composite_type
)
with (storage_type=ASTORE);

-- verification
create table v_t1
(
  c1 tinyint,
  c2 smallint,
  c3 integer,
  c4 bigint,
  c5 money,
  c6 boolean, 
  c7 char(10),
  c8 character(10),
  c9 nchar(10),
  c10 varchar(10),
  c11 text,
  c12 clob,
  c13 blob,
  c14 bytea,
  c15 date,
  c16 time without time zone,
  c17 reltime,
  c18 composite_type
)
with (storage_type=ASTORE);

create table v_t2
(
  c1 tinyint,
  c2 smallint,
  c3 integer,
  c4 bigint,
  c5 money,
  c6 boolean, 
  c7 char(10),
  c8 character(10),
  c9 nchar(10),
  c10 varchar(10),
  c11 text,
  c12 clob,
  c13 blob,
  c14 bytea,
  c15 date,
  c16 time without time zone,
  c17 reltime,
  c18 composite_type
)
with (storage_type=ASTORE);

--insert into v_t1 values (1,1,1,1,1,true,'hello','hello','hello','hello','hello','hello','aaaaaaaaaaa','zzz','2015-01-02','03:25:00','@ 105 days',row(1,false,10,'hi','2020-11-11'));
\COPY t1 from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_unione_data1.data' delimiter '|' csv header;
\COPY t2 from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_unione_data2.data' delimiter '|' csv header;
\COPY v_t1 from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_unione_data1.data' delimiter '|' csv header;
\COPY v_t2 from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_unione_data2.data' delimiter '|' csv header;

-- insert select
insert into t2 select * from t1;
insert into v_t2 select * from v_t1;
create view view1 as (select * from t2 except select * from v_t2);
select * from view1;

insert into t1 select * from t2;
insert into v_t1 select * from v_t2;
create view view2 as (select * from t1 except select * from v_t1);
select * from view2;

insert into t2 select * from t1 where c6=false;
insert into v_t2 select * from v_t1 where c6=false;
create view view3 as (select * from t2 except select * from v_t2);
select * from view3;

insert into t1 select * from t2 where c6=true;
insert into v_t1 select * from v_t2 where c6=true;
create view view4 as (select * from t1 except select * from v_t1);
select * from view4;

-- update from subquery
update t2 set t2.c15='2020-11-11' from (select c6 from t1) as subquery where t2.c6=subquery.c6;
update v_t2 set v_t2.c15='2020-11-11' from (select c6 from v_t1) as subquery where v_t2.c6=subquery.c6;
create view view5 as (select * from t2 except select * from v_t2);
select * from view5;

update t1 set t1.c15='2020-11-11' from (select c6 from t2) as subquery where t1.c6=subquery.c6;
update v_t1 set v_t1.c15='2020-11-11' from (select c6 from v_t2) as subquery where v_t1.c6=subquery.c6;
create view view6 as (select * from t1 except select * from v_t1);
select * from view6;

-- left join
create view view7 as (
(select t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18
from t1 left join t2 on t1.c3=t2.c3)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18 
from v_t1 left join v_t2 on v_t1.c3=v_t2.c3));
select * from view7;

create view view8 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18
from t1 left join t2 on t1.c2=t2.c2)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18 
from v_t1 left join v_t2 on v_t1.c2=v_t2.c2));
select * from view8;

create view view9 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 left join t2 on t1.c1=t2.c1)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18 
from v_t1 left join v_t2 on v_t1.c1=v_t2.c1));
select * from view9;

-- right join
create view view10 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 right join t2 on t1.c4=t2.c4)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18 
from v_t1 right join v_t2 on v_t1.c4=v_t2.c4));
select * from view10;

create view view11 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 right join t2 on t1.c5=t2.c5)
except
(select * from v_t1 right join v_t2 on v_t1.c5=v_t2.c5));
select * from view11;

-- full join
create view view12 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 full join t2 on t1.c7=t2.c7)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t1 full join v_t2 on v_t1.c7=v_t2.c7)); 
select * from view12;

create view view13 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 full join t2 on t1.c8=t2.c8)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t1 full join v_t2 on v_t1.c8=v_t2.c8));
select * from view13;

-- inner join
create view view14 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 inner join t2 on t1.c8=t2.c8)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t1 inner join v_t2 on v_t1.c8=v_t2.c8));
select * from view14;

create view view15 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18, t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 
from t1 inner join t2 on t1.c9=t2.c9)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18, v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t1 inner join v_t2 on v_t1.c9=v_t2.c9));
select * from view15;

-- union
create view view16 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18 
from t1 union select t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 from t2)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18 from v_t1 union select v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t2));
select * from view16;

-- intersect
create view view17 as (
(select t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18
from t1 intersect select t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18  from t2)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18 from v_t1 intersect select v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18  from v_t2));
select * from view17;

-- except
create view view18 as (
(select  t1.c1 as t1_c1, t1.c2 as t1_c2, t1.c3 as t1_c3, t1.c4 as t1_c4, t1.c5 as t1_c5, t1.c6 as t1_c6, t1.c7 as t1_c7, t1.c8 as t1_c8, t1.c9 as t1_c9, t1.c10 as t1_c10, t1.c11 as t1_c11, t1.c12 as t1_c12, t1.c13 as t1_c13, t1.c14 as t1_c14, t1.c15 as t1_c15, t1.c16 as t1_c16, t1.c17 as t1_c17, t1.c18 as t1_c18
from t1 except select t2.c1 as t2_c1, t2.c2 as t2_c2, t2.c3 as t2_c3, t2.c4 as t2_c4, t2.c5 as t2_c5, t2.c6 as t2_c6, t2.c7 as t2_c7, t2.c8 as t2_c8, t2.c9 as t2_c9, t2.c10 as t2_c10, t2.c11 as t2_c11, t2.c12 as t2_c12, t2.c13 as t2_c13, t2.c14 as t2_c14, t2.c15 as t2_c15, t2.c16 as t2_c16, t2.c17 as t2_c17, t2.c18 as t2_c18 from t2)
except
(select v_t1.c1 as v_t1_c1, v_t1.c2 as v_t1_c2, v_t1.c3 as v_t1_c3, v_t1.c4 as v_t1_c4, v_t1.c5 as v_t1_c5, v_t1.c6 as v_t1_c6, v_t1.c7 as v_t1_c7, v_t1.c8 as v_t1_c8, v_t1.c9 as v_t1_c9, v_t1.c10 as v_t1_c10, v_t1.c11 as v_t1_c11, v_t1.c12 as v_t1_c12, v_t1.c13 as v_t1_c13, v_t1.c14 as v_t1_c14, v_t1.c15 as v_t1_c15, v_t1.c16 as v_t1_c16, v_t1.c17 as v_t1_c17, v_t1.c18 as v_t1_c18  from v_t1 except select v_t2.c1 as v_t2_c1, v_t2.c2 as v_t2_c2, v_t2.c3 as v_t2_c3, v_t2.c4 as v_t2_c4, v_t2.c5 as v_t2_c5, v_t2.c6 as v_t2_c6, v_t2.c7 as v_t2_c7, v_t2.c8 as v_t2_c8, v_t2.c9 as v_t2_c9, v_t2.c10 as v_t2_c10, v_t2.c11 as v_t2_c11, v_t2.c12 as v_t2_c12, v_t2.c13 as v_t2_c13, v_t2.c14 as v_t2_c14, v_t2.c15 as v_t2_c15, v_t2.c16 as v_t2_c16, v_t2.c17 as v_t2_c17, v_t2.c18 as v_t2_c18 from v_t2));
select * from view18;

drop view view1;
drop view view2;
drop view view3;
drop view view4;
drop view view5;
drop view view6;
drop view view7;
drop view view8;
drop view view9;
drop view view10;
drop view view11;
drop view view12;
drop view view13;
drop view view14;
drop view view15;
drop view view16;
drop view view17;
drop view view18;
drop table t1;
drop table t2;
drop table v_t1;
drop table v_t2;
drop type composite_type;