\c hdfs

-----------------------------------------------------------------------
-- Basic syntax
-- Expect: success
-----------------------------------------------------------------------

prepare ccddlcheck as
select attrelid::regclass as relname,
attnum, attoptions from pg_class c, pg_attribute_encoding e
where c.relname like 'ccddl%' and c.oid=e.attrelid
order by relname, attnum;

-- default encoding clause
create table ccddl (
	i int,
	j int,
	default column encoding (compresstype=zlib)
) with (appendonly=true, orientation=column);

execute ccddlcheck;

-- This is enough to force compression, specially since we'll hash
-- all to a single segment and the values are all the same.
insert into ccddl select 1, 2 from generate_series(1, 100);
select * from ccddl;

drop table ccddl;

-- Make sure we cleaned up after ourselves: should return zero rows
-- We exclude the named tables because they're hang overs from other
-- tests
select * from pg_attribute_encoding where 
	attrelid not in (select oid from pg_class where
	relname = 't3coquicklz' or relname = 'co' or
	relname = 'aocs_new' or relname = 'tenk_aocs7');

-- mix inline and default
create table ccddl (
	i int,
	j int encoding (compresstype=quicklz),
	default column encoding (compresstype=zlib)
) with (appendonly=true, orientation=column);

execute ccddlcheck;

drop table ccddl;

-- mix column reference and default
create table ccddl (
	i int,
	j int,
	default column encoding (compresstype=zlib),
	column j encoding (compresstype=quicklz)
) with (appendonly=true, orientation=column);

execute ccddlcheck;
drop table ccddl;

-- encoding clause for only some columns, others should
-- have no encoding
create table ccddl (
	i int,
	j text encoding (compresstype=quicklz, blocksize=65536, compresslevel=1)
)  with (appendonly=true, orientation=column);

execute ccddlcheck;

-- Should see the encoding information for the new column
alter table ccddl add column k timestamp default now()
	encoding (compresstype=zlib);
execute ccddlcheck;

-- no encoding information for this one though
alter table ccddl add column l numeric
	default 3.141;
execute ccddlcheck;

drop table ccddl;

-- Decipher encoding clause references
create table ccddl (i int, j int encoding(compresstype=zlib), column i encoding
(compresstype=zlib), default column encoding (compresstype=quicklz)) with
(appendonly=true, orientation=column);
execute ccddlcheck;
drop table ccddl;

-- WITH (..., compresstype=<type>) should act as a default clause
create table ccddl (i int, j int) with(appendonly = true, orientation = column,
compresstype=zlib);
execute ccddlcheck;
drop table ccddl;

-- Mix case for compresstype since it should be case insensitive
create table ccddl (i int, j int encoding (COMPRESSTYPE="QuIckLZ"))
	with(appendonly = true, orientation = column);
execute ccddlcheck;
drop table ccddl;

-- CREATE TABLE (LIKE) WITH (...) must honour the directives in the WITH clause.
create table ccddl (i int);
create table ccddl_co(LIKE ccddl)
  with (appendonly = true, orientation=column);
execute ccddlcheck;
drop table ccddl_co;

create table ccddl_co(LIKE ccddl)
  with (appendonly = true, orientation=column, compresstype=quicklz);
execute ccddlcheck;
drop table ccddl_co, ccddl;

-- storage directives when we only use LIKE
create table ccddl (i int, j int);
create table ccddl_co (like ccddl, column i encoding(compresstype=quicklz))
with (appendonly=true, orientation=column, compresstype=zlib);
execute ccddlcheck;
drop table ccddl, ccddl_co;


-----------------------------------------------------------------------
-- Basic syntax
-- Expect: failure
-----------------------------------------------------------------------

-- only support CO tables
create table ccddl (i int encoding (compresstype=quicklz));
create table ccddl (i int encoding (compresstype=quicklz))
	with (appendonly = true);

-- can't add encoding to a non-CO table
create table ccddl (i int);
alter table ccddl add column j int encoding (compresstype=quicklz);
drop table ccddl;

create table ccddl (i int) with (appendonly=true);
alter table ccddl add column j int encoding (compresstype=quicklz);
drop table ccddl;

-- check that we validate the encoding clause for add column
create table ccddl (i int) with (appendonly=true, orientation=column);
alter table ccddl add column j int encoding(compresstype=yawn);
alter table ccddl add column j int encoding(compresstype=quicklz,
compresslevel=100000);
alter table ccddl add column j int encoding(a=b);
drop table ccddl;

-- encoding clause conflicts with WITH clause
create table a (i int encoding(compresstype=quicklz)) with(compresstype=zlib,
appendonly=true, orientation=column);

-- conflicting WITH and DEFAULT COLUMN ENCODING clause
create table t1 (i int, j int, default column encoding (compresstype=quicklz))
with (compresstype=zlib, appendonly=true, orientation=column);

-- Invalid encoding clauses
create table t1 (i int encoding (compresstype=quicklz, compresslevel=9))
with (appendonly=true, orientation=column);

create table t1 (i int encoding (compresstype=quicklz, ahhhh=boooooo))
with (appendonly=true, orientation=column);

-- Inheritance: check that we don't support inheritance on tables using
-- column compression
create table ccddlparent (i int encoding (compresstype=zlib))
with (appendonly = true, orientation = column);
create table ccddlchild (j int encoding (compresstype=zlib))
inherits(ccddlparent) with (appendonly = true, orientation = column);
drop table ccddlparent cascade;

-- Conflict between default and with, in the LIKE case
create table ccddl (i int);
create table ccddl_co (like ccddl, 
					   default column encoding(compresstype=quicklz)) 
with (appendonly=true, orientation=column, compresstype=zlib);
drop table ccddl;

-- Make sure we preserve WITH() in the presence of CTAS 
create table ccddl (a, b)
with (appendonly=true, orientation=column, compresstype=quicklz) as
select 1, 1;
execute ccddlcheck;
drop table ccddl;

-----------------------------------------------------------------------
-- Partitioning support
-- Expect: success
-----------------------------------------------------------------------

-- trivial partitioning case
create table ccddl (i int, j int)
with (appendonly = true, orientation=column)
partition by range(j)
(partition p1 start(1) end(10),
 partition p2 start(10) end(20),
 column i encoding(compresstype=zlib),
 column j encoding(compresstype=quicklz)
);

execute ccddlcheck;

insert into ccddl select 1,2 from generate_series(1, 100);
select * from ccddl;
drop table ccddl;

-- subpartition template
create table ccddl (i int, j int, k int, l int)
with (appendonly = true, orientation=column)
partition by range(j)
	subpartition by list (k)
	subpartition template(
		subpartition sp1 values(1, 2, 3, 4, 5),
 		column i encoding(compresstype=zlib),
		column j encoding(compresstype=quicklz),
		column k encoding(compresstype=zlib),
		column l encoding(compresstype=zlib)
	)

(partition p1 start(1) end(10),
 partition p2 start(10) end(20)
);

execute ccddlcheck;

select parencattnum, parencattoptions from
pg_partition_encoding e, pg_partition p, pg_class c
where c.relname = 'ccddl' and c.oid = p.parrelid and p.oid = e.parencoid;

insert into ccddl select 1, (i % 19) + 1, ((i+3) % 5) + 1, i+3 from generate_series(1, 100) i;

select * from ccddl;

-- Verify dependency handling
alter table ccddl drop column l;

insert into ccddl select 1, (i % 19) + 1, ((i+3) % 5) + 1 from generate_series(1, 100) i;

select parencattnum, parencattoptions from
pg_partition_encoding e, pg_partition p, pg_class c
where c.relname = 'ccddl' and c.oid = p.parrelid and p.oid = e.parencoid;

select * from ccddl;

drop table ccddl;

-- Add partition should 'inherit' the subpartition template storage encodings
create table ccddl (i int, j int, k int, l int)
with
(appendonly = true, orientation=column)
partition by range(j)
subpartition by list (k)
subpartition template(
subpartition sp1 values(1, 2, 3, 4, 5),
column i encoding(compresstype=zlib),
column j encoding(compresstype=quicklz),
column k encoding(compresstype=zlib),
column l encoding(compresstype=zlib))
(partition p1 start(1) end(10),
partition p2 start(10) end(20)
);

execute ccddlcheck;

alter table ccddl add partition p3 start(20) end(30);

execute ccddlcheck;

drop table ccddl;

-- Make sure the AO/CO case didn't screw up the non CO case
create table ccddl (i int, j int, k int, l int)
with
(appendonly = true)
partition by range(j)
subpartition by list (k)
subpartition template(
subpartition sp1 values(1, 2, 3, 4, 5))
(partition p1 start(1) end(10),
partition p2 start(10) end(20)
);

execute ccddlcheck;

alter table ccddl add partition p3 start(20) end(30);

execute ccddlcheck;

drop table ccddl;

-- Should be nothing in pg_partition_encoding now
select * from pg_partition_encoding;

-- Split support. We must preserve the column encodings of the split partition
create table ccddl (i int encoding (compresstype=zlib))
with (appendonly = true, orientation=column)
partition by range(i)
(partition p1 start(1) end(10));
execute ccddlcheck;

alter table ccddl split partition p1 at (5) into (partition p2, partition p3);
execute ccddlcheck;

drop table ccddl;

-- With subpartitioning
create table ccddl (i int, j int)
with (appendonly=true, orientation=column)
partition by range(i) subpartition by range(j)
subpartition template (subpartition sp1 start(1) end(20),
	column i encoding (compresstype=zlib),
	column j encoding (compresstype=quicklz))
(partition p1 start(1) end(20));

execute ccddlcheck;

alter table ccddl alter partition p1 split partition sp1 at (10) into (partition sp2, partition sp3);
execute ccddlcheck;

alter table ccddl alter partition p1 split partition sp2 at (5) into (partition sp2, partition sp2_5);
execute ccddlcheck;

drop table ccddl;


-- MPP-14407
-- Multi level partitioning: the expansion of the multi-level partitioning
-- configuration duplicates the encoding for `month'. Make sure we 
-- handle this and produce sane results

CREATE TABLE ccddl (id int, year int, month int, day int, region text) 
	with (appendonly=true, orientation=column)
	DISTRIBUTED BY (id)
	PARTITION BY RANGE (year)
		SUBPARTITION BY RANGE (month)
			SUBPARTITION TEMPLATE
			(
				START (1) END (13),
				COLUMN month ENCODING (compresstype=quicklz)
			)
		SUBPARTITION BY LIST (region)
		SUBPARTITION TEMPLATE
		(
			SUBPARTITION usa VALUES ('usa'),
			COLUMN region ENCODING (compresstype=quicklz)
		)
	( START (2008) END (2010) );
execute ccddlcheck;

-- Ensure we can read and write
insert into ccddl select 1, 2008, 1, 2, 'usa' from generate_series(1, 100);
select * from ccddl;
drop table ccddl;

-- Partition specific column encoding
create table ccddl (
	i int,
	j int,
	k int,
	l int
	)
	with (appendonly=true, orientation=column)
	partition by range(i)
	(
		partition p1 start(1) end(2) column i encoding(compresstype=zlib),
	 	partition p2 start(2) end(3) column j encoding(compresstype=quicklz)
									 column k encoding(blocksize=8192),
	 	partition p3 start(3) end(4) column i encoding(compresstype=quicklz)
									 column j encoding(blocksize=8192)
									 default column encoding
									 	(compresstype=zlib),
		column i encoding (blocksize=65536),
		default column encoding (compresstype=quicklz)
	);

execute ccddlcheck;
drop table ccddl;


create table ccddl (i int, j int, k int, l int )
with (appendonly=true, orientation=column)
partition by range(i) subpartition by range(j)
 (
 partition p1 start(1) end(2)
		(subpartition sp1 start(1) end(2) column i encoding(compresstype=zlib),
		 column i encoding (blocksize=65536),
		 default column encoding (compresstype=quicklz)
		),
 partition p2 start(2) end(3)
 		(subpartition sp1 start(1) end(2)
			column j encoding(compresstype=zlib)
			column k encoding(blocksize=8192),
		 column i encoding (blocksize=65536),
		 default column encoding (compresstype=quicklz)
		)
);

execute ccddlcheck;
drop table ccddl;


-- Precedence test: c3 in the partition child must be zlib, not quicklz
CREATE TABLE ccddl ( c1 int ENCODING (compresstype=zlib),
                     c2 char ENCODING (compresstype=quicklz, blocksize=65536),
				     c3 date,
					 COLUMN c3 ENCODING (compresstype=quicklz))
WITH (appendonly=true, orientation=column)
	PARTITION BY RANGE (c3) (
		START ('1900-01-01'::DATE)   END ('2100-12-31'::DATE),
		        COLUMN c3 ENCODING (compresstype=zlib),
				COLUMN c2 ENCODING (compresstype=quicklz)
	);

execute ccddlcheck;
drop table ccddl;

-----------------------------------------------------------------------
-- Partitioning support
-- Expect: failure
-----------------------------------------------------------------------

-- Make sure we validate the storage directives
create table gg (i int, k int) with (appendonly=true, orientation=column)
partition by range(k) (partition p1 start(1) end(2), column i encoding(a=b));

create table gg (i int, k int) with (appendonly=true, orientation=column)
partition by range(k) (partition p1 start(1) end(2), column i
encoding(compresstype=sdf2sdf));

-- We don't support partition element specific encoding clauses in subpartition
-- templates as we have no place to store them.
create table a (i int, j int) with (appendonly=true, orientation=column)
      partition by range(i) subpartition by range(j)
      subpartition template(start(1) end(10) default column encoding (compresstype=zlib),
                            start(10) end(20))
(partition p1 start(1) end(10));

-- partition level mention of column encoding but the table isn't heap oriented
CREATE TABLE ccddl
(a1 int,a2 char(5),a3 text,a4 timestamp ,a5 date) 
partition by range(a1) 
	(   
		start(1) end(1000) every(500),
		COLUMN a1 ENCODING (compresstype=zlib,compresslevel=4,blocksize=32768)
	);

-----------------------------------------------------------------------
-- Type support
-- Expect: success
-----------------------------------------------------------------------

-- The basics
drop type if exists int42 cascade;
create type int42;
CREATE FUNCTION int42_in(cstring)
RETURNS int42
AS 'int4in'
LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION int42_out(int42)
RETURNS cstring
AS 'int4out'
LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE int42 (
internallength = 4,
input = int42_in,
output = int42_out,
alignment = int4,
default = 42,
passedbyvalue,
compresstype="quicklz",
blocksize=65536,
compresslevel=1
);

select typoptions from pg_type_encoding where typid='public.int42'::regtype;

create table ccddl (i int42) with(appendonly = true, orientation=column);
execute ccddlcheck;

alter type int42 set default encoding (compresstype=zlib);
alter table ccddl add column j int42 default '1'::int42;
execute ccddlcheck;

drop table ccddl;

-- Shouldn't apply type default encoding in these cases
create table ccddl (i int42);
execute ccddlcheck;
drop table ccddl;

create table ccddl (i int42) with (appendonly = true);
execute ccddlcheck;
drop table ccddl;

create table ccddl (i int42) with (appendonly = true, orientation=column,
compresstype=none);
execute ccddlcheck;
drop table ccddl;

-- Ensure that we can handle the SQL types that are recognized by the parser
-- but are not represented in the catalog.
alter type character varying set default encoding (compresstype=quicklz);

select typoptions from pg_type t, pg_type_encoding e where
  t.typname = 'varchar' and t.oid = e.typid;

alter type character set default encoding (compresstype=zlib);
select typoptions from pg_type t, pg_type_encoding e where
  t.typname = 'bpchar' and t.oid = e.typid;

alter type timestamp with time zone set default encoding(compresstype=quicklz);
select typoptions from pg_type t, pg_type_encoding e where
  t.typname = 'timestamptz' and t.oid = e.typid;

-- schema qualification
alter type pg_catalog.text set default encoding (compresstype=quicklz);
select typoptions from pg_type t, pg_type_encoding e where
  t.typname = 'text' and t.oid = e.typid;

-----------------------------------------------------------------------
-- Type support
-- Expect: failure
-----------------------------------------------------------------------
-- We should reject any extraneous type information during alter type
alter type numeric(10, 2) set default encoding(compresstype=zlib);
alter type int[2][3] set default encoding(compresstype=quicklz);
-- permissions checks
create role typcheck;
set session authorization typcheck;
alter type int42 set default encoding (compresstype=zlib, compresslevel=1);
reset session authorization;
drop role typcheck;
-- Verify that we validate the storage clause
alter type text set default encoding (a=b);
alter type text set default encoding (compresstype=10);
alter type text set default encoding (compresstype=quicklz, compresslevel=100);

-- dependency check on drop type
drop type int42 cascade;

select * from pg_type_encoding;

-- cleanup
deallocate ccddlcheck;


-----------------------------------------------------------------------
-- RLE sanity checks
-- Expect: failure
-----------------------------------------------------------------------

-- RLE_TYPE is not supported 
create table ccddl (i int) with(appendonly = true, compresstype = rle_type);

-----------------------------------------------------------------------
-- MPP-14381 cdbfast regression: Dropping and adding a column to AO partitioned tables fails with SIGSEGV
-- Expect: success
-----------------------------------------------------------------------
DROP TABLE IF EXISTS ccddl;

CREATE TABLE ccddl (
                    P_PARTKEY INTEGER,
                    P_SIZE integer,
                    P_CONTAINER CHAR(10),
                    P_RETAILPRICE decimal,
                    P_COMMENT VARCHAR(23)
                    )
  partition by range (p_size)
  (
    partition p1 start('1')  WITH (appendonly=true, checksum=true, blocksize=819200, compresslevel=8)
  , partition p2 start('21') end('28')
  , partition p3 start('28') WITH (appendonly=true, checksum=true, blocksize=819200, compresslevel=8)
  , partition p4 start('32') end('33')
  , partition p5 start('33') WITH (appendonly=true, checksum=true, blocksize=819200, compresslevel=8)
);

INSERT INTO ccddl values ( generate_series(1,25000), 3, 'JUMBO CASE', 1001.00, 'ronic dependencies d' );

INSERT INTO ccddl values ( generate_series(1,25000), 23, 'JUMBO CASE', 1001.00, 'ronic dependencies d' );

INSERT INTO ccddl values ( generate_series(1,25000), 29, 'JUMBO CASE', 1001.00, 'ronic dependencies d' );

INSERT INTO ccddl values ( generate_series(1,25000), 37, 'JUMBO CASE', 1001.00, 'ronic dependencies d' );

ALTER TABLE ccddl DROP COLUMN P_PARTKEY;

ALTER TABLE ccddl ADD COLUMN   P_PARTKEY  INTEGER  DEFAULT 100;


-----------------------------------------------------------------------
-- MPP-14477 cdbfast regression: Bitmap Index scan on AO tables with compression fails with SIGSEGV
-- Expect: success
-----------------------------------------------------------------------
DROP TABLE IF EXISTS ccddl cascade;

CREATE TABLE ccddl ( 
   id INTEGER
 , owner VARCHAR
 , property BOX
  ) 
WITH (APPENDONLY=True, COMPRESSTYPE=ZLIB, COMPRESSLEVEL=1)  
DISTRIBUTED BY (id);

insert into ccddl values (59,'Hypatia','( (6050, 20), (7052, 250) )');

CREATE INDEX ccddl_propertyBoxIndex ON ccddl USING Gist (property);

SET enable_seqscan = FALSE;

-- to the same internal representation.
SELECT owner, property FROM ccddl
 WHERE property ~= '((7052,250),(6050,20))';
drop table ccddl;