-- TEST CASE: when one column is dropped, 0 is returned to for only fixed length value, excluding varlen datatype
--
SET DATESTYLE TO ISO;
CREATE TABLE cmpr_drop_varlen_col_02(
        c_int_1 INTEGER NOT NULL,
        c_int_2 INTEGER NOT NULL,
        c_sint_1 SMALLINT NOT NULL,
        c_sint_2 SMALLINT NOT NULL,
        c_dec_1 DECIMAL(10,4) NOT NULL,
        c_dec_2 DECIMAL(10,4) NOT NULL,
        c_tsw_1 TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        c_tsw_2 TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        c_ts_1 TIMESTAMP WITH TIME ZONE NOT NULL,
        c_ts_2 TIMESTAMP WITH TIME ZONE NOT NULL,
        c_date_1 DATE NOT NULL,
        c_date_2 DATE NOT NULL)WITH (FILLFACTOR=90);

CREATE TABLE cmpr_drop_varlen_col_03(
        c_int_1 INTEGER NOT NULL,
        c_int_2 INTEGER NOT NULL,
        c_sint_1 SMALLINT NOT NULL,
        c_sint_2 SMALLINT NOT NULL,
        c_dec_1 DECIMAL(10,4) NOT NULL,
        c_dec_2 DECIMAL(10,4) NOT NULL,
        c_tsw_1 TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        c_tsw_2 TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        c_ts_1 TIMESTAMP WITH TIME ZONE NOT NULL,
        c_ts_2 TIMESTAMP WITH TIME ZONE NOT NULL,
        c_date_1 DATE NOT NULL,
        c_date_2 DATE NOT NULL)WITH (FILLFACTOR=90) ;

CREATE OR REPLACE FUNCTION FUNC_COMPRESS_INSERT_042_1(rownums int) RETURN boolean IS
i INTEGER;
star_int INTEGER;
star_sint SMALLINT;
star_dec DECIMAL(10,4);
star_tsw TIMESTAMP WITHOUT TIME ZONE;
star_ts TIMESTAMP WITH TIME ZONE;
star_date DATE;
BEGIN
    star_int = 100000;
    star_sint = 100;
    star_dec = 999.0009;
    star_tsw = '2012-08-21 12:11:50';
    star_ts = '2012-08-21 12:12:14';
    star_date = '2012-8-21';
    
    FOR i IN 1..rownums LOOP
        INSERT INTO cmpr_drop_varlen_col_02 VALUES (star_int+i, star_int+i, star_sint+i, star_sint+i, star_dec+i, star_dec+i, star_tsw, star_tsw, star_ts+i, star_ts+i, star_date, star_date);
    END LOOP;
    RETURN TRUE;
END;
/
CALL FUNC_COMPRESS_INSERT_042_1(1000);

CREATE OR REPLACE FUNCTION FUNC_COMPRESS_INSERT_042_2(rownums int) RETURN boolean IS
i INTEGER;
star_int INTEGER;
star_dec DECIMAL(10,4);
star_tsw TIMESTAMP WITHOUT TIME ZONE;
star_ts TIMESTAMP WITH TIME ZONE;
star_date DATE;
BEGIN
    star_int = 9;
    star_dec = 99.0009;
    star_tsw = '2023-08-21 12:11:50';
    star_ts = '2023-08-21 12:12:14';
    star_date = '2023-8-21';
    
    FOR i IN 1..rownums LOOP
        INSERT INTO cmpr_drop_varlen_col_03 VALUES (i, star_int+i, star_int+i, star_int+i, star_dec+i, star_dec+i, star_tsw+i, star_tsw+i, star_ts+i, star_ts+i, star_date+i, star_date+i);
    END LOOP;
    RETURN TRUE;
END;
/
CALL FUNC_COMPRESS_INSERT_042_2(1000);

COPY cmpr_drop_varlen_col_02 TO '@abs_srcdir@/data/cmpr_drop_varlen_col_02.out';
COPY cmpr_drop_varlen_col_03 FROM '@abs_srcdir@/data/cmpr_drop_varlen_col_02.out';

ALTER TABLE cmpr_drop_varlen_col_03 ALTER c_int_2 TYPE NUMBER(20,4);
ALTER TABLE cmpr_drop_varlen_col_03 ADD CHECK(c_date_1 > '2011-12-20');
ALTER TABLE cmpr_drop_varlen_col_03 DROP COLUMN c_dec_1;

ALTER TABLE cmpr_drop_varlen_col_02 SET COMPRESS;
ALTER TABLE cmpr_drop_varlen_col_02 DROP COLUMN c_dec_1;
DELETE FROM cmpr_drop_varlen_col_02;
ALTER TABLE cmpr_drop_varlen_col_02 ALTER c_int_2 TYPE NUMBER(20,4);

COPY cmpr_drop_varlen_col_03 TO '@abs_srcdir@/data/cmpr_drop_varlen_col_03.out';
COPY cmpr_drop_varlen_col_02 FROM '@abs_srcdir@/data/cmpr_drop_varlen_col_03.out';

ALTER TABLE cmpr_drop_varlen_col_02 ADD CHECK(c_date_1 > '2011-12-20');
ALTER TABLE cmpr_drop_varlen_col_02 DROP COLUMN c_int_2;
(SELECT COUNT(*),MIN(C_INT_1),MAX(C_INT_1),AVG(C_INT_1) FROM cmpr_drop_varlen_col_02 WHERE C_INT_1=100500)
MINUS ALL
(SELECT COUNT(*),MIN(C_INT_1),MAX(C_INT_1),AVG(C_INT_1) FROM cmpr_drop_varlen_col_03 WHERE C_INT_1=100500);
(SELECT COUNT(*),MIN(C_INT_1),MAX(C_INT_1),AVG(C_INT_1) FROM cmpr_drop_varlen_col_02 WHERE C_INT_1>10 AND c_date_1<'2013-12-30')
MINUS ALL
(SELECT COUNT(*),MIN(C_INT_1),MAX(C_INT_1),AVG(C_INT_1) FROM cmpr_drop_varlen_col_03 WHERE C_INT_1>10 AND c_date_1<'2013-12-30');

DROP TABLE cmpr_drop_varlen_col_03;
DROP TABLE cmpr_drop_varlen_col_02;
\! rm @abs_srcdir@/data/cmpr_drop_varlen_col_02.out
\! rm @abs_srcdir@/data/cmpr_drop_varlen_col_03.out