--reference table:replication(tablespace) and hash
set datestyle = 'iso, ymd';set intervalstyle to postgres;set time zone prc;
--I1.create table and insert into data
create table redis_table_003_normal (c_int integer, c_smallint smallint unique, c_bigint bigint, c_decimal decimal , c_numeric numeric , c_real real , c_double double precision , c_money money , c_character_varying character varying(10) , c_varchar varchar(10) , c_char char(10) , c_text text , c_bytea bytea , c_timestamp_without  timestamp without time zone , c_timestamp_with timestamp with time zone , c_boolean boolean ,  c_cidr cidr , c_inet inet ,c_macaddr macaddr , c_bit bit(20) , c_bit_varying bit varying(20) , c_oid oid , c_regproc regproc , c_regprocedure regprocedure , c_regoperator regoperator , c_regclass regclass , c_regtype regtype , c_character character(10) , c_interval interval , c_date date , c_time_without time without time zone, c_time_with time with time zone,c_binary_integer binary_integer,c_binary_double binary_double,c_dec dec(18,9),c_numeric_1 numeric(19,9),c_raw raw,c_varchar2 varchar2) tablespace testtablespace distribute by replication;
create index redis_index_003_normal on redis_table_003_normal using btree(c_int) where c_int>5;
create unique index redis_index_003_normal_1 on redis_table_003_normal(c_time_without,c_int,c_date,c_smallint);
create view redistable.redis_view_003_normal as select * from redis_table_003_normal where c_smallint > 2;

create table redistable.redis_table_003_reference (c_int integer, c_smallint smallint, c_bigint bigint, c_decimal decimal , c_numeric numeric , c_real real , c_double double precision , c_money money , c_character_varying character varying(10) , c_varchar varchar(10) , c_char char(10) , c_text text , c_bytea bytea , c_timestamp_without  timestamp without time zone , c_timestamp_with timestamp with time zone , c_boolean boolean ,  c_cidr cidr , c_inet inet ,c_macaddr macaddr , c_bit bit(20) , c_bit_varying bit varying(20) , c_oid oid , c_regproc regproc , c_regprocedure regprocedure , c_regoperator regoperator , c_regclass regclass , c_regtype regtype , c_character character(10) , c_interval interval , c_date date , c_time_without time without time zone, c_time_with time with time zone,c_binary_integer binary_integer,c_binary_double binary_double,c_dec dec(18,9),c_numeric_1 numeric(19,9),c_raw raw,c_varchar2 varchar2) distribute by hash(c_smallint);
create unique index redis_index_003_reference on redistable.redis_table_003_reference(c_smallint,c_int);

insert into redis_table_003_normal select * from redistable.redis_table_000;

--I2.select to verify
--set enable_seqscan=off;
select count(*),null as normal_table,sum(c_int) as c_int_sum,avg(c_int) as c_int_avg,sum(c_smallint) as c_smallint_sum,avg(c_smallint) as c_smallint_avg,sum(c_bigint) as c_bigint_sum,avg(c_bigint) as c_bigint_avg,sum(c_decimal) as c_decimal_sum,avg(c_decimal) as c_decimal_avg,sum(c_numeric) as c_numeric_sum,avg(c_numeric) as c_numeric_avg,max(length(c_character_varying)) as c_character_varying_length_max,min(length(c_char)) as c_char_length_min,sum(length(c_bit_varying)) as c_bit_varying_length_sum from redis_table_003_normal where c_int <= 201 and c_smallint >= 45 or c_bigint < 10201 and c_smallint != 1 and c_smallint != 16;
select distinct(c_int),null as normal_table,max(distinct(c_bigint)),c_decimal from redis_table_003_normal where c_int in (select distinct(c_int) from redis_table_003_normal where c_smallint != -6) and c_oid >=15 group by c_int,c_decimal order by c_int limit 11;

select count(*),null as reference_table,sum(c_int) as c_int_sum,avg(c_int) as c_int_avg,sum(c_smallint) as c_smallint_sum,avg(c_smallint) as c_smallint_avg,sum(c_bigint) as c_bigint_sum,avg(c_bigint) as c_bigint_avg,sum(c_decimal) as c_decimal_sum,avg(c_decimal) as c_decimal_avg,sum(c_numeric) as c_numeric_sum,avg(c_numeric) as c_numeric_avg,max(length(c_character_varying)) as c_character_varying_length_max,min(length(c_char)) as c_char_length_min,sum(length(c_bit_varying)) as c_bit_varying_length_sum from redistable.redis_table_003_reference where c_int <= 201 and c_smallint >= 45 or c_bigint < 10201 and c_smallint != 1 and c_smallint != 16;
select distinct(c_int),null as reference_table,max(distinct(c_bigint)),c_decimal from redistable.redis_table_003_reference where c_int in (select distinct(c_int) from redistable.redis_table_003_reference where c_smallint != -6) and c_oid >=15 group by c_int,c_decimal order by c_int limit 11;

--join 
select count(*),sum(table_reference.c_int) as c_int_sum,avg(table_reference.c_int) as c_int_avg,sum(table_reference.c_smallint) as c_smallint_sum,avg(table_normal.c_smallint) as c_smallint_avg,sum(table_normal.c_bigint) as c_bigint_sum,avg(table_reference.c_bigint) as c_bigint_avg,sum(table_reference.c_decimal) as c_decimal_sum,avg(table_reference.c_decimal) as c_decimal_avg,sum(table_normal.c_numeric) as c_numeric_sum,avg(table_normal.c_numeric) as c_numeric_avg,max(length(table_reference.c_character_varying)) as c_character_varying_length_max,min(length(table_normal.c_char)) as c_char_length_min,sum(length(table_reference.c_bit_varying)) as c_bit_varying_length_sum from redistable.redis_table_003_reference as table_reference inner join redis_table_003_normal as table_normal on table_reference.c_int < 200 and table_normal.c_int>10 and table_reference.c_smallint != table_normal.c_smallint;
--set enable_seqscan=on;