---
--case 5:
---
set client_min_messages = 'ERROR';
drop table if exists lineitem;
reset client_min_messages;
CREATE TABLE LINEITEM
(
    L_ORDERKEY    BIGINT NOT NULL
  , L_PARTKEY     BIGINT NOT NULL
  , L_SUPPKEY     BIGINT NOT NULL
  , L_LINENUMBER  BIGINT NOT NULL
  , L_QUANTITY    DECIMAL(15,2) NOT NULL
  , L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL
  , L_DISCOUNT    DECIMAL(15,2) NOT NULL
  , L_TAX         DECIMAL(15,2) NOT NULL
  , L_RETURNFLAG  CHAR(1) NOT NULL
  , L_LINESTATUS  CHAR(1) NOT NULL
  , L_SHIPDATE    DATE NOT NULL
  , L_COMMITDATE  DATE NOT NULL
  , L_RECEIPTDATE DATE NOT NULL
  , L_SHIPINSTRUCT CHAR(25) NOT NULL
  , L_SHIPMODE     CHAR(10) NOT NULL
  , L_COMMENT      VARCHAR(44) NOT NULL
)
with (orientation = column)
;
COPY LINEITEM FROM '@abs_srcdir@/data/lineitem.data' DELIMITER '|';
DELETE FROM LINEITEM;
COPY LINEITEM FROM '@abs_srcdir@/data/lineitem.data' DELIMITER '|';
drop table lineitem;

-------
--case 6:
------
CREATE TABLE OOrder ( 
                        o_id int NOT NULL, 
                        o_d_id int NOT NULL, 
                        o_w_id int NOT NULL, 
                        o_c_id int NOT NULL, 
                        o_entry_d timestamp NOT NULL, 
                        o_carrier_id int NULL, 
                        o_ol_cnt int NOT NULL, 
                        o_all_local int NOT NULL)  with (orientation=column)
                        ;   
copy oorder  from '@abs_srcdir@/data/oorder.data' with csv null ''; 
copy oorder  from '@abs_srcdir@/data/oorder1_0' with csv null ''; 
copy oorder  from '@abs_srcdir@/data/oorder2_0' with csv null ''; 
select * from oorder where o_d_id=1 and o_c_id=1 order by 1,2,3;
select count('ABC') from oorder;
delete from oorder where o_d_id = 1 and o_c_id = 1;
select count('ABC') from oorder;
-----
--case 6
---
copy oorder to '@abs_srcdir@/results/oorder.data' with csv null '';

--
-- fix bug
--
CREATE TABLE hw_cstore_copy_batchrows
(
	c_int1 int,
	c_int2 int,
	PARTIAL CLUSTER KEY(c_int2)
) WITH (ORIENTATION=COLUMN, MAX_BATCHROW=10000) 
partition by range (c_int2)
(
	partition p1 values less than (50),
	partition p2 values less than (100)
);

CREATE TABLE hw_row_copy_batchrows
(
	c_int1 int,
	c_int2 int,
	c_int3 int
) 
partition by range (c_int2)
(
	partition p1 values less than (50),
	partition p2 values less than (100)
);
-- make sure that the loops number is equal to MAX_BATCHROW defined by hw_cstore_copy_batchrows
INSERT INTO hw_row_copy_batchrows VALUES(1, 2, generate_series(1, 10000));
INSERT INTO hw_cstore_copy_batchrows SELECT c_int1, c_int2 FROM hw_row_copy_batchrows;

-- 10000 expected, but the result is 0
SELECT COUNT(*) FROM hw_cstore_copy_batchrows;


----
--COPY FROM/TO DATA WITH MULTIPLE BYTES DELIMITER
----
COPY tmp_nation TO '@abs_srcdir@/results/nation.mult' delimiter ',,,,,]]]]]]';
COPY tmp_nation TO '@abs_srcdir@/results/nation.mult' delimiter ',,,[]';
truncate tmp_nation;
COPY tmp_nation FROM '@abs_srcdir@/results/nation.mult' delimiter ',,,[]';
SELECT * FROM tmp_nation ORDER BY n_nationkey;
COPY tmp_nation TO '@abs_srcdir@/results/nation.mult' delimiter '(),,,' csv;
truncate tmp_nation;
COPY tmp_nation FROM '@abs_srcdir@/results/nation.mult' delimiter '(),,,' csv;
SELECT * FROM tmp_nation ORDER BY n_nationkey;

create table tmp_mult_delim
(c1 int, c2 varchar)with (orientation = column)
;
insert into tmp_mult_delim 
values(1, '"fewf"sttt'),
(2, '"fewf"sttt'),
(3, 'fstes\\fwt');
select * from tmp_mult_delim order by c1;
COPY tmp_mult_delim TO '@abs_srcdir@/results/mult_delim.mult' delimiter 'few^fw"' csv;
COPY tmp_mult_delim TO '@abs_srcdir@/results/mult_delim.mult' delimiter 'few^fw/' csv;
truncate tmp_mult_delim;
COPY tmp_mult_delim FROM '@abs_srcdir@/results/mult_delim.mult' delimiter 'few^fw/' csv;
select * from tmp_mult_delim order by c1;
drop table tmp_mult_delim;