create schema vector_mergejoin_engine_1;
set search_path to vector_mergejoin_engine_1;

----
--- case 7: Complicate Case
----
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_00(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE, PARTIAL CLUSTER KEY(C_CHAR_3))WITH (ORIENTATION=COLUMN);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_00_01 on TBL_COMBINE_NESTLOOP_SEQSCAN_00(C_INT_01);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_00_02 on TBL_COMBINE_NESTLOOP_SEQSCAN_00(C_BIGINT_01);
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_00_xx1(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE);	
copy TBL_COMBINE_NESTLOOP_SEQSCAN_00_xx1 from '@abs_srcdir@/data/TBL_COMBINE_NESTLOOP_SEQSCAN_00_xx1.data' DELIMITER as ',' NULL as '';
insert into TBL_COMBINE_NESTLOOP_SEQSCAN_00 select * from TBL_COMBINE_NESTLOOP_SEQSCAN_00_xx1;

CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_01(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE, PARTIAL CLUSTER KEY(C_INT))WITH (ORIENTATION=COLUMN);
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_01_xx2(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_01_01 on TBL_COMBINE_NESTLOOP_SEQSCAN_01(C_INT_01);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_01_02 on TBL_COMBINE_NESTLOOP_SEQSCAN_01(C_BIGINT_01);	
copy TBL_COMBINE_NESTLOOP_SEQSCAN_01_xx2 from '@abs_srcdir@/data/TBL_COMBINE_NESTLOOP_SEQSCAN_01_xx2.data';

insert into TBL_COMBINE_NESTLOOP_SEQSCAN_01 select * from TBL_COMBINE_NESTLOOP_SEQSCAN_01_xx2;
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_02(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE, PARTIAL CLUSTER KEY(C_VARCHAR_3))WITH (ORIENTATION=COLUMN);
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_02_xx3(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_02_01 on TBL_COMBINE_NESTLOOP_SEQSCAN_02(C_INT_01);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_02_02 on TBL_COMBINE_NESTLOOP_SEQSCAN_02(C_BIGINT_01);	
copy TBL_COMBINE_NESTLOOP_SEQSCAN_02_xx3 from '@abs_srcdir@/data/TBL_COMBINE_NESTLOOP_SEQSCAN_02_xx3.data';

insert into TBL_COMBINE_NESTLOOP_SEQSCAN_02 select * from TBL_COMBINE_NESTLOOP_SEQSCAN_02_xx3;

CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_03(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE )WITH (ORIENTATION=COLUMN)
	partition by range (C_INT)--INTERVAL (20)
	( 
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_1 values less than (20),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_2 values less than (40),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_3 values less than (60),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_4 values less than (90),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_5 values less than (201),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_6 values less than (maxvalue)
    );
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_03_xx4(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE )
	partition by range (C_INT)--INTERVAL (20)
	( 
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_1 values less than (20),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_2 values less than (40),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_3 values less than (60),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_4 values less than (90),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_5 values less than (201),
		partition TBL_COMBINE_NESTLOOP_SEQSCAN_03_6 values less than (maxvalue)
    );
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_03_01 ON TBL_COMBINE_NESTLOOP_SEQSCAN_03 (C_INT_01) local;
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_03_02 ON TBL_COMBINE_NESTLOOP_SEQSCAN_03 (C_BIGINT_01) local;

copy TBL_COMBINE_NESTLOOP_SEQSCAN_03_xx4 from '@abs_srcdir@/data/TBL_COMBINE_NESTLOOP_SEQSCAN_03_xx4.data';

insert into TBL_COMBINE_NESTLOOP_SEQSCAN_03 select * from TBL_COMBINE_NESTLOOP_SEQSCAN_03_xx4;

CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_04(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE , PARTIAL CLUSTER KEY(C_INT))WITH (ORIENTATION=COLUMN)
	partition by range (C_INT)
( 
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_1 values less than (30),
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_2 values less than (50),
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_3 values less than (80),
	 partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_4 values less than (150),
	 partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_5 values less than (301)
);
CREATE TABLE vector_mergejoin_engine_1.TBL_COMBINE_NESTLOOP_SEQSCAN_04_xx5(
	C_CHAR_1 CHAR(1),
	C_CHAR_2 CHAR(10),
	C_CHAR_3 CHAR(100),
	C_VARCHAR_1 VARCHAR(1),
	C_VARCHAR_2 VARCHAR(10),
	C_VARCHAR_3 VARCHAR(1024),
	C_INT INTEGER,
	C_BIGINT BIGINT,
	C_INT_01 INTEGER,
	C_BIGINT_01 BIGINT,
	C_SMALLINT SMALLINT,
	C_FLOAT FLOAT,
	C_NUMERIC numeric(10,5),
	C_DP double precision,
	C_DATE DATE,
	C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
	C_TS_WITH TIMESTAMP WITH TIME ZONE) 
	partition by range (C_INT)
( 
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_1 values less than (30),
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_2 values less than (50),
     partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_3 values less than (80),
	 partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_4 values less than (150),
	 partition TBL_COMBINE_NESTLOOP_SEQSCAN_04_5 values less than (301)
);
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_04_01 ON TBL_COMBINE_NESTLOOP_SEQSCAN_04 (C_INT_01) LOCAL;
CREATE INDEX idx_TBL_COMBINE_NESTLOOP_SEQSCAN_04_02 ON TBL_COMBINE_NESTLOOP_SEQSCAN_04 (C_BIGINT_01) LOCAL;

copy TBL_COMBINE_NESTLOOP_SEQSCAN_04_xx5 from '@abs_srcdir@/data/TBL_COMBINE_NESTLOOP_SEQSCAN_04_xx5.data';

insert into TBL_COMBINE_NESTLOOP_SEQSCAN_04 select * from TBL_COMBINE_NESTLOOP_SEQSCAN_04_xx5;

SET TIME ZONE 'PRC';set datestyle to iso;
SET CURRENT_SCHEMA='vector_mergejoin_engine_1';
SET ENABLE_INDEXONLYSCAN=off;
SET ENABLE_HASHJOIN=OFF;
SET ENABLE_MERGEJOIN=ON;
SET ENABLE_INDEXSCAN=ON;
SET ENABLE_NESTLOOP=OFF;
SET ENABLE_SEQSCAN=OFF;
SET ENABLE_BITMAPSCAN=OFF;
SET LOG_MIN_MESSAGES = DEBUG1;
--explain analyze 
Select 
T0.C_INT_01 AS T0_C_INT_01,
T0.C_BIGINT_01 AS T0_C_BIGINT_01
 From
  TBL_COMBINE_NESTLOOP_SEQSCAN_00 T0,
  TBL_COMBINE_NESTLOOP_SEQSCAN_01 T1,
  TBL_COMBINE_NESTLOOP_SEQSCAN_02 T2,
  TBL_COMBINE_NESTLOOP_SEQSCAN_03 T3,
  TBL_COMBINE_NESTLOOP_SEQSCAN_04 T4
 Where
 T0.C_INT_01>=5
 and T0.C_INT_01<50
 and T1.C_INT_01>=7
 and T1.C_INT_01<50
 and T0.C_INT_01 =  T1.C_INT_01
 and T2.C_INT_01>=8
 and T2.C_INT_01<40
 and T0.C_INT_01 =  T2.C_INT_01
 and T0.C_BIGINT_01 = T2.C_BIGINT_01
 and 
 T3.C_INT_01>=10
 and T3.C_INT_01<100
 and T0.C_INT_01 =  T3.C_INT_01
 and T4.C_INT_01>=15
 and T4.C_INT_01<200
 and T0.C_INT_01 =  T4.C_INT_01
 ORDER BY 1,2 DESC LIMIT 500 OFFSET 40000; 
  
RESET LOG_MIN_MESSAGES;
 
--explain analyze 
Select 
T2.C_INT_01 AS T2_C_INT_01,
T2.C_BIGINT_01 AS T2_C_BIGINT_01
 From
  TBL_COMBINE_NESTLOOP_SEQSCAN_00 T0,
  TBL_COMBINE_NESTLOOP_SEQSCAN_01 T1,
  TBL_COMBINE_NESTLOOP_SEQSCAN_02 T2,
  TBL_COMBINE_NESTLOOP_SEQSCAN_03 T3,
  TBL_COMBINE_NESTLOOP_SEQSCAN_04 T4
 Where
T0.C_INT_01>=5
 and T0.C_INT_01<50
 and T1.C_INT_01>=7
 and T1.C_INT_01<50
 and T0.C_INT_01 =  T1.C_INT_01
 and T2.C_INT_01>=8
 and T2.C_INT_01<40
 and T0.C_INT_01 =  T2.C_INT_01
 and T0.C_BIGINT_01 = T2.C_BIGINT_01
 and 
 T3.C_INT_01>=2
 and T3.C_INT_01<100
 and T0.C_INT_01 =  T3.C_INT_01
 and T4.C_INT_01>=5
 and T4.C_INT_01<200
 and T0.C_INT_01 =  T4.C_INT_01
 ORDER BY 1,2 DESC LIMIT 1000 OFFSET 5000; 

-- test for Vector Merge Full Join
SET ENABLE_NESTLOOP TO FALSE;
SET ENABLE_HASHJOIN TO FALSE; 

CREATE TABLE TBL_MERGEJOIN_NATURAL_OUT_1200501(ID INTEGER,NAME VARCHAR(3000), PARTIAL CLUSTER KEY(ID)) WITH (ORIENTATION=COLUMN);
CREATE INDEX TBL_MERGEJOIN_NATURAL_OUT_1200501_IDX ON TBL_MERGEJOIN_NATURAL_OUT_1200501(ID);
CREATE TABLE TBL_MERGEJOIN_NATURAL_IN_1200501(ID INTEGER,ADDRESS VARCHAR(3000), PARTIAL CLUSTER KEY(ADDRESS)) WITH (ORIENTATION=COLUMN);
CREATE INDEX TBL_MERGEJOIN_NATURAL_IN_1200501_IDX ON TBL_MERGEJOIN_NATURAL_IN_1200501(ID);

copy TBL_MERGEJOIN_NATURAL_IN_1200501 from '@abs_srcdir@/data/TBL_MERGEJOIN_NATURAL_IN_1200501.data';

SELECT COUNT(*) FROM TBL_MERGEJOIN_NATURAL_IN_1200501;

copy TBL_MERGEJOIN_NATURAL_OUT_1200501 from '@abs_srcdir@/data/TBL_MERGEJOIN_NATURAL_OUT_1200501.data';


EXPLAIN (costs off)
SELECT *
FROM TBL_MERGEJOIN_NATURAL_IN_1200501 NATURAL
FULL JOIN TBL_MERGEJOIN_NATURAL_OUT_1200501
ORDER BY 1, 2, 3 ASC;

SELECT *
FROM TBL_MERGEJOIN_NATURAL_IN_1200501 NATURAL
FULL JOIN TBL_MERGEJOIN_NATURAL_OUT_1200501
ORDER BY 1, 2, 3 ASC;

drop schema  vector_mergejoin_engine_1 cascade;