--test orc foreign data wrapper.
--

-- Settings to make the result deterministic
SET datestyle = "ISO, YMD";
set enable_vector_engine=false;
set enable_hdfs_predicate_pushdown=false;

--Unit 1. test plan without distributed clause
DROP FOREIGN TABLE IF EXISTS lineitem;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER 	HDFS_FDW OPTIONS (type 'hdfs', address '@hdfshostname@:@hdfsport@',hdfscfgpath '@hdfscfgpath@');
create foreign table lineitem (
 L_ORDERKEY INT4,
 L_PARTKEY INT4,
 L_SUPPKEY INT4,
 L_LINENUMBER INT4,
 L_QUANTITY FLOAT8,
 L_EXTENDEDPRICE FLOAT8,
 L_DISCOUNT FLOAT8,
 L_TAX FLOAT8,
 L_RETURNFLAG TEXT,
 L_LINESTATUS TEXT,
 L_SHIPDATE TEXT,
 L_COMMITDATE TEXT,
 L_RECEIPTDATE TEXT,
 L_SHIPINSTRUCT TEXT,
 L_SHIPMODE TEXT,
 L_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe/000000_0,/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe/000001_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS nation;
create foreign table nation  (
 N_NATIONKEY INT4,
 N_NAME TEXT,
 N_REGIONKEY INT4,
 N_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/nation_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS region;
create foreign table region  (
 R_REGIONKEY INT4,
 R_NAME TEXT,
 R_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS part;
create foreign table part  (
 P_PARTKEY INT4,
 P_NAME TEXT,
 P_MFGR TEXT,
 P_BRAND TEXT,
 P_TYPE TEXT,
 P_SIZE INT4,
 P_CONTAINER TEXT,
 P_RETAILPRICE FLOAT8,
 P_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/part_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS supplier;
create foreign table supplier  (
 S_SUPPKEY INT4,
 S_NAME TEXT,
 S_ADDRESS TEXT,
 S_NATIONKEY INT4,
 S_PHONE TEXT,
 S_ACCTBAL FLOAT8,
 S_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/supplier_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS partsupp;
create foreign table partsupp (
 PS_PARTKEY INT4,
 PS_SUPPKEY INT4,
 PS_AVAILQTY INT4,
 PS_SUPPLYCOST FLOAT8,
 PS_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/partsupp_orc11_64stripe/000000_0') distribute by roundrobin;

analyze foreign tables;

explain (verbose, costs off, nodes off) SELECT L_SHIPDATE FROM lineitem WHERE L_orderkey BETWEEN 509050 AND 509070;

explain (verbose, costs off, nodes off) SELECT count(L_SHIPDATE) FROM lineitem WHERE L_orderkey BETWEEN 509050 AND 509070;

explain (verbose, costs off, nodes off) select n_name, r_name from nation join region on n_regionkey=r_regionkey;

explain (costs on, nodes off) select n_name from nation;

--Test Unit 2: distributed foreign table
DROP FOREIGN TABLE IF EXISTS lineitem;
Create foreign table lineitem (
 L_ORDERKEY INT4,
 L_PARTKEY INT4,
 L_SUPPKEY INT4,
 L_LINENUMBER INT4,
 L_QUANTITY FLOAT8,
 L_EXTENDEDPRICE FLOAT8,
 L_DISCOUNT FLOAT8,
 L_TAX FLOAT8,
 L_RETURNFLAG TEXT,
 L_LINESTATUS TEXT,
 L_SHIPDATE TEXT,
 L_COMMITDATE TEXT,
 L_RECEIPTDATE TEXT,
 L_SHIPINSTRUCT TEXT,
 L_SHIPMODE TEXT,
 L_COMMENT TEXT)
SERVER hdfs_server OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/lineitem_orc11_64stripe') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS customer;
CREATE FOREIGN TABLE customer ( 
 c_custkey BIGINT, 
 c_name TEXT, 
 c_address TEXT, 
 c_nationkey INT4, 
 c_phone TEXT, 
 c_acctbal FLOAT4, 
 c_mktsegment TEXT, 
 c_comment TEXT) 
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/customer_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS orders;
create foreign table orders (
 O_ORDERKEY INT4, 
 O_CUSTKEY INT4, 
 O_ORDERSTATUS TEXT, 
 O_TOTALPRICE FLOAT8,
 O_ORDERDATE TEXT,
 O_ORDERPRIORITY TEXT, 
 O_CLERK TEXT,
 O_SHIPPRIORITY INT4, 
 O_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/orders_orc11_64stripe')
distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS part;
create foreign table part  (
 P_PARTKEY INT4,
 P_NAME TEXT, 
 P_MFGR TEXT,
 P_BRAND TEXT,
 P_TYPE TEXT,
 P_SIZE INT4,
 P_CONTAINER TEXT,
 P_RETAILPRICE FLOAT8,
 P_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/part_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS supplier;
create foreign table supplier  (
 S_SUPPKEY INT4,
 S_NAME TEXT,
 S_ADDRESS TEXT,
 S_NATIONKEY INT4,
 S_PHONE TEXT,
 S_ACCTBAL FLOAT8,
 S_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/supplier_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS partsupp;
create foreign table partsupp (
 PS_PARTKEY INT4,
 PS_SUPPKEY INT4,
 PS_AVAILQTY INT4,
 PS_SUPPLYCOST FLOAT8,
 PS_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/partsupp_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS nation;
create foreign table nation  (
 N_NATIONKEY INT4,
 N_NAME TEXT,
 N_REGIONKEY INT4,
 N_COMMENT TEXT) 
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/nation_orc11_64stripe/000000_0') distribute by roundrobin;

DROP FOREIGN TABLE IF EXISTS region;
create foreign table region  (
 R_REGIONKEY INT4,
 R_NAME TEXT,
 R_COMMENT TEXT)
SERVER hdfs_server
OPTIONS(format 'orc', filenames '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/000000_0') distribute by roundrobin;

analyze foreign tables;

explain (verbose, costs off, nodes off) SELECT count(t1.*) FROM region t1;
SELECT count(t1.*) FROM region t1;
explain (verbose, costs off, nodes off) SELECT count(*) FROM region;
SELECT count(*) FROM region;
SELECT region FROM region order by 1 limit 1;
select sum(r_regionkey), count(row(array[r_regionkey, 2]))from region;
-- there are bugs for the following three queries. cause EOF error from datanode
explain (verbose, costs off, nodes off) SELECT L_shipdate FROM lineitem WHERE L_orderkey between 2125190 and 2125200;
explain (costs on, nodes off) SELECT L_shipdate FROM lineitem WHERE L_orderkey between 2125190 and 2125200;
explain (verbose, costs off, nodes off) SELECT count(L_shipdate) FROM lineitem WHERE L_orderkey between 2125190 and 2125200;

-- TPCH - Q1
explain (verbose, costs off, nodes off) SELECT 
  L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY), AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1) 
FROM 
  lineitem 
WHERE 
  L_SHIPDATE<='1998-09-02' 
GROUP BY L_RETURNFLAG, L_LINESTATUS 
ORDER BY L_RETURNFLAG, L_LINESTATUS;

set datestyle to iso;SET IntervalStyle to postgres;

create schema COLUMNAR_STORAGE;
create foreign table  COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006
(
C_CHAR_3 CHAR(102400),
C_VARCHAR_3 VARCHAR(1024),
C_INT INTEGER,
C_NUMERIC numeric(10,5),
C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
C_TEXT  TEXT
)
SERVER gsmpp_server
OPTIONS(location '/srv/BigData/mpp_cases/Testcase/data/FULL_TEXT/FULL_TEXT_TABLE_006',
format 'text',
mode 'private'
);
create index INDEX_COL_FULL_TEXT_TABLE_006_1_1 ON  COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006(c_numeric) ;

create table COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006_2
(
C_CHAR_3 CHAR(102400),
C_VARCHAR_3 VARCHAR(1024),
C_INT INTEGER,
C_NUMERIC numeric(10,5),
C_TS_WITHOUT TIMESTAMP WITHOUT TIME ZONE,
--@tsvector@C_tsvector  tsvector
C_tsvector VarChar(100000)
) with (orientation=column);

create index INDEX_COL_FULL_TEXT_TABLE_006_2_1 ON  COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006_2(c_numeric) ;
create index INDEX_COL_FULL_TEXT_TABLE_006_2_2 ON  COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006_2(c_char_3,c_int);
explain (verbose, costs off, nodes off) select  a.c_int,b.c_int ,a.c_text,b.c_tsvector  from   COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006 a,COLUMNAR_STORAGE.COL_FULL_TEXT_TABLE_006_2 b order by 1,2,3,4;
reset enable_vector_engine;
DROP FOREIGN TABLE IF EXISTS lineitem CASCADE;
DROP FOREIGN TABLE IF EXISTS customer CASCADE;
DROP FOREIGN TABLE IF EXISTS orders CASCADE;
DROP FOREIGN TABLE IF EXISTS part CASCADE;
DROP FOREIGN TABLE IF EXISTS partsupp CASCADE;
DROP FOREIGN TABLE IF EXISTS supplier CASCADE;
DROP FOREIGN TABLE IF EXISTS nation CASCADE;
DROP FOREIGN TABLE IF EXISTS region CASCADE;
DROP SERVER hdfs_server CASCADE;