set enable_default_ustore_table = on;
create schema test_ustore_union_3;
set current_schema='test_ustore_union_3';
-- test left outer join with all supported data types
drop table if exists t1;
drop table if exists t2;
drop type if exists composite_type;
create type composite_type as (int_type integer, char_type text, date_type date);
---------------------------------------------------------------------------------
----------------- SUPPORTED TYPES SO FAR -------------------
---------------------------------------------------------------------------------
create temp 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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (storage_type=USTORE);
create temp 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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (storage_type=USTORE);
create temp table t3
(
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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (storage_type=USTORE);
-- create temp tables for post data insertion verification
drop table if exists t1_verification;
drop table if exists t2_verification;
drop table if exists t3_verification;
create temp table t1_verification
(
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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (orientation=row);
create temp table t2_verification
(
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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (orientation=row);
create temp table t3_verification
(
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,
c14 bytea,
c15 date,
c16 time without time zone,
c17 reltime
)
with (orientation=row);
----------------------------------------------------------------------------------
-------------------- ALL DATA TYPES -----------------------
---------------------------------------------------------------------------------
--create temp 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 temp 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=USTORE);
--
--create temp table t3
--(
-- 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 temp table for post data insertion verification
--drop table if exists t1_verification;
--drop table if exists t2_verification;
--drop table if exists t3_verification;
--
--create temp table t1_verification
--(
-- 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 (orientation=row);
--
--create temp table t2_verification
--(
-- 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 (orientation=row);
--
--create temp table t3_verification
--(
-- 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 (orientation=row);
-- case 1: empty tables
create view union_view_case1_1 as (select * from t1 union select * from t2);
create view union_view_case1_2 as (select * from t1 union select * from t2 union select * from t3);
create view union_view_case1_3 as (select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2);
create view union_view_case1_4 as (select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2 union all select * from t3 where t3.c1=3);
select * from union_view_case1_1;
select * from union_view_case1_2;
select * from union_view_case1_3;
select * from union_view_case1_4;
create view union_all_view_case1_1 as (select * from t1 union all select * from t2);
create view union_all_view_case1_2 as (select * from t1 union all select * from t2 union all select * from t3);
create view union_all_view_case1_3 as (select * from t1 where t1.c1=1 union all select * from t2 where t2.c1=2);
create view union_all_view_case1_4 as (select * from t1 where t1.c1=1 union all select * from t2 where t2.c1=2 union all select * from t3 where t3.c1=3);
select * from union_all_view_case1_1;
select * from union_all_view_case1_2;
select * from union_all_view_case1_3;
select * from union_all_view_case1_4;
-- case 2: one empty table
\COPY t1 FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data1.data' delimiter '|';
\COPY t1_verification FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data1.data' delimiter '|';
-- t1 data verification
create view union_t1_verification_view as (select * from t1 except select * from t1_verification);
select * from union_t1_verification_view;
create view union_view_case2_1 as ((select * from t1 union select * from t2)
except
(select * from t1_verification union select * from t2_verification));
create view union_view_case2_2 as ((select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2)
except
(select * from t1_verification where t1_verification.c1=1 union select * from t2_verification where t2_verification.c1=2));
select * from union_view_case2_1;
select * from union_view_case2_2;
create view union_all_view_case2_1 as ((select * from t1 union all select * from t2)
except
(select * from t1_verification union all select * from t2_verification));
create view union_all_view_case2_2 as ((select * from t1 where t1.c1=1 union all select * from t2 where t2.c1=2)
except
(select * from t1_verification where t1_verification.c1=1 union all select * from t2_verification where t2_verification.c1=2));
select * from union_all_view_case2_1;
select * from union_all_view_case2_2;
-- case 3: no empty table
\COPY t2 FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data2.data' delimiter '|';
\COPY t2_verification FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data2.data' delimiter '|';
-- t2 data verification
create view union_t2_verification_view as (select * from t2 except select * from t2_verification);
select * from union_t2_verification_view;
create view union_view_case3_1 as ((select * from t1 union select * from t2)
except
(select * from t1_verification union select * from t2_verification));
create view union_view_case3_2 as ((select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2)
except
(select * from t1_verification where t1_verification.c1=1 union select * from t2_verification where t2_verification.c1=2));
create view union_view_case3_3 as ((select * from t1 union all select * from t2)
except
(select * from t1_verification union all select * from t2_verification));
create view union_view_case3_4 as ((select * from t1 where t1.c1=1 union all select * from t2 where t2.c1=2)
except
(select * from t1_verification where t1_verification.c1=1 union all select * from t2_verification where t2_verification.c1=2));
select * from union_view_case3_1;
select * from union_view_case3_2;
select * from union_view_case3_3;
select * from union_view_case3_4;
-- insert 10k rows for t3
\COPY t3 FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data3.data' delimiter '|';
\COPY t3_verification FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test_ustore_data3.data' delimiter '|';
-- t3 data verification
create view union_t3_verification_view as (select * from t3 except select * from t3_verification);
select * from union_t3_verification_view;
create view union_view_case3_5 as ((select * from t1 union select * from t2 union select * from t3)
except
(select * from t1_verification union select * from t2_verification union select * from t3_verification));
create view union_view_case3_6 as ((select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2 union all select * from t3 where t3.c1=3)
except
(select * from t1_verification where t1_verification.c1=1 union select * from t2_verification where t2_verification.c1=2 union all select * from t3_verification where t3_verification.c1=3));
create view union_view_case3_7 as ((select * from t1 union all select * from t2 union all select * from t3)
except
(select * from t1_verification union all select * from t2_verification union all select * from t3_verification));
create view union_view_case3_8 as ((select * from t1 where t1.c1=1 union all select * from t2 where t2.c1=2 union all select * from t3 where t3.c1=3)
except(select * from t1_verification where t1_verification.c1=1 union all select * from t2_verification where t2_verification.c1=2 union all select * from t3_verification where t3_verification.c1=3));
select * from union_view_case3_5;
select * from union_view_case3_6;
select * from union_view_case3_7;
select * from union_view_case3_8;
create view union_view_case3_9 as (select * from t1 where t1.c1=1 union select * from t2 where t2.c1=2);
create view union_view_case3_10 as (select * from t1_verification where t1_verification.c1=1 union select * from t2_verification where t2_verification.c1=2);
create view union_view_case3_11 as (select * from union_view_case3_9 where c1=100 except select * from union_view_case3_10 where c1=100);
select * from union_view_case3_11;
-- case 4: union in subquery
create view union_view_case4_1 as ((select * from
(select * from t1 union select * from t2))
except
(select * from
(select * from t1_verification union select * from t2_verification)));
create view union_view_case4_2 as ((select * from
(select * from t1 union select * from t2 union select * from t3))
except
(select * from
(select * from t1_verification union select * from t2_verification union select * from t3)));
create view union_view_case4_3 as ((select * from
(select * from t1 union select * from t2) as foo)
except
(select * from
(select * from t1_verification union select * from t2_verification) as foo));
create view union_view_case4_4 as ((select * from
(select * from t1 where t1.c1=1 union select * from t2) as foo)
except
(select * from
(select * from t1_verification where t1_verification.c1=1 union select * from t2_verification) as foo));
create view union_view_case4_5 as ((select * from
(select * from t1 union all select * from t2))
except
(select * from
(select * from t1_verification union all select * from t2_verification)));
create view union_view_case4_6 as ((select * from
(select * from t1 union all select * from t2 union all select * from t3))
except
(select * from
(select * from t1_verification union all select * from t2_verification union all select * from t3)));
create view union_view_case4_7 as ((select * from
(select * from t1 union all select * from t2) as foo)
except
(select * from
(select * from t1_verification union all select * from t2_verification) as foo));
create view union_view_case4_8 as ((select * from
(select * from t1 where t1.c1=1 union all select * from t2) as foo)
except
(select * from
(select * from t1_verification where t1_verification.c1=1 union all select * from t2_verification) as foo));
--create view union_view_case4_9 as ((select * from
--(select t1.c18 from t1 union select t2.c18 from t2) as foo)
--except
--(select * from
--(select t1_verification.c18 from t1_verification union select t2_verification.c18 from t2_verification) as foo));
--create view union_view_case4_10 as ((select * from
--(select t1.c18 from t1 union all select t2.c18 from t2) as foo)
--except
--(select * from
--(select t1_verification.c18 from t1_verification union all select t2_verification.c18 from t2_verification) as foo));
select * from union_view_case4_1;
select * from union_view_case4_2;
select * from union_view_case4_3;
select * from union_view_case4_4;
select * from union_view_case4_5;
select * from union_view_case4_6;
select * from union_view_case4_7;
select * from union_view_case4_8;
--select * from union_view_case4_9;
--select * from union_view_case4_10;
-- drop all tables and views
drop view union_view_case1_1 CASCADE;
drop view union_view_case1_2 CASCADE;
drop view union_view_case1_3 CASCADE;
drop view union_view_case1_4 CASCADE;
drop view union_all_view_case1_1 CASCADE;
drop view union_all_view_case1_2 CASCADE;
drop view union_all_view_case1_3 CASCADE;
drop view union_all_view_case1_4 CASCADE;
drop view union_t1_verification_view CASCADE;
drop view union_view_case2_1 CASCADE;
drop view union_view_case2_2 CASCADE;
drop view union_all_view_case2_1 CASCADE;
drop view union_all_view_case2_2 CASCADE;
drop view union_t2_verification_view CASCADE;
drop view union_view_case3_1 CASCADE;
drop view union_view_case3_2 CASCADE;
drop view union_view_case3_3 CASCADE;
drop view union_view_case3_4 CASCADE;
drop view union_t3_verification_view CASCADE;
drop view union_view_case3_5 CASCADE;
drop view union_view_case3_6 CASCADE;
drop view union_view_case3_7 CASCADE;
drop view union_view_case3_8 CASCADE;
drop view union_view_case3_9 CASCADE;
drop view union_view_case3_10 CASCADE;
drop view union_view_case3_11 CASCADE;
drop view union_view_case4_1 CASCADE;
drop view union_view_case4_2 CASCADE;
drop view union_view_case4_3 CASCADE;
drop view union_view_case4_4 CASCADE;
drop view union_view_case4_5 CASCADE;
drop view union_view_case4_6 CASCADE;
drop view union_view_case4_7 CASCADE;
drop view union_view_case4_8 CASCADE;
--drop view union_view_case4_9 CASCADE;
--drop view union_view_case4_10 CASCADE;
drop table t1 CASCADE;
drop table t2 CASCADE;
drop table t3 CASCADE;
drop table t1_verification CASCADE;
drop table t2_verification CASCADE;
drop table t3_verification CASCADE;