src/test/regress/sql/bitmapscan_ao.sql

-- start-ignore

drop schema if exists bm_ao cascade;
create schema bm_ao;
set search_path to bm_ao;

-- end_ignore

CREATE TABLE outer_table (
    flex_value_id numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    language character varying(4) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_update_date date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_updated_by numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    creation_date date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    created_by numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_update_login numeric(10,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    description character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    source_lang character varying(4) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    flex_value_meaning character varying(150) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    legalhold character varying(5) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    legaldescription character varying(255) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    dataowner character varying(255) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    purgedate date ENCODING (compresstype=None,compresslevel=1,blocksize=32768)
)
WITH (appendonly=true, orientation=column, compresstype=None) DISTRIBUTED BY (flex_value_id);


set allow_system_table_mods=true;
UPDATE pg_class
SET
	relpages = 1::int,
	reltuples = 0.0::real
WHERE relname = 'outer_table' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'ofa_applsys');
UPDATE pg_class
SET
	relpages = 64::int,
	reltuples = 61988.0::real
WHERE relname = 'outer_table' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'applsys');

CREATE TABLE inner_table (
    flex_value_set_id numeric(10,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    flex_value_id numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    flex_value character varying(150) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_update_date date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_updated_by numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    creation_date date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    created_by numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    last_update_login numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    enabled_flag character varying(1) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    summary_flag character varying(1) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    start_date_active date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    end_date_active date ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    parent_flex_value_low character varying(60) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    parent_flex_value_high character varying(60) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    structured_hierarchy_level numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    hierarchy_level character varying(30) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    compiled_value_attributes character varying(2000) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    value_category character varying(30) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute1 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute2 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute3 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute4 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute5 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute6 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute7 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute8 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute9 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute10 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute11 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute12 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute13 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute14 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute15 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute16 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute17 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute18 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute19 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute20 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute21 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute22 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute23 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute24 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute25 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute26 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute27 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute28 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute29 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute30 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute31 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute32 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute33 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute34 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute35 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute36 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute37 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute38 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute39 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute40 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute41 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute42 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute43 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute44 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute45 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute46 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute47 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute48 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute49 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute50 character varying(240) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    attribute_sort_order numeric(15,0) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    legalhold character varying(5) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    legaldescription character varying(255) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    dataowner character varying(255) ENCODING (compresstype=None,compresslevel=1,blocksize=32768),
    purgedate date ENCODING (compresstype=None,compresslevel=1,blocksize=32768)
)
WITH (appendonly=true, orientation=column, compresstype=None) DISTRIBUTED BY (flex_value_set_id ,flex_value_id);

CREATE INDEX inner_table_idx ON inner_table USING btree (flex_value_id);
UPDATE pg_class
SET
	relpages = 64::int,
	reltuples = 30994.0::real
WHERE relname = 'inner_table' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'applsys');


insert into outer_table values(12345,'ZHS');
insert into outer_table values(012345,'ZHS');
insert into inner_table values(54321,12345);
insert into inner_table values(654321,123456);

CREATE TABLE foo(fid numeric(10,0));
insert into foo VALUES(54321);

-- start_ignore

set optimizer_segments=64;
set optimizer_enable_bitmapscan=on; 
set optimizer_enable_hashjoin=off;
select disable_xform('CXformInnerJoin2IndexGetApply');

-- end_ignore


SELECT fid FROM
(SELECT b1.flex_value_set_id, b1.flex_value_id
FROM
inner_table b1,
outer_table c1
WHERE
b1.flex_value_id = c1.flex_value_id 
and c1.language = 'ZHS') bar,
foo
WHERE foo.fid = bar.flex_value_set_id;

-- double free mixed bitmap indexes (StreamBitmap with TIDBitmap)
CREATE TABLE bmcrash (
    btree_col2 date DEFAULT now(),
    bitmap_col text NOT NULL,
    btree_col1 character varying(50) NOT NULL,
    dist_col serial
)
WITH (appendonly=true, compresslevel=5, compresstype=zlib) DISTRIBUTED BY (dist_col);

CREATE INDEX bm_idx ON bmcrash USING bitmap (bitmap_col);
CREATE INDEX bt_idx ON bmcrash USING btree (btree_col1);
CREATE INDEX bt_idx_2 ON bmcrash USING btree (btree_col2);

INSERT INTO bmcrash (btree_col2, bitmap_col, btree_col1)
SELECT date '2015-01-01' + (i % (365 * 2)), i % 1000, 'abcdefg' || (i% 1000)
from generate_series(1,10000) as i;

select count(1) from bmcrash where bitmap_col = '999' AND btree_col1 = 'abcdefg999';
select count(1) from bmcrash where bitmap_col = '999' OR btree_col1 = 'abcdefg999';
select count(1) from bmcrash where bitmap_col = '999' OR btree_col1 = 'abcdefg999' AND btree_col2 = '2015-01-01';
select count(1) from bmcrash where bitmap_col = '999' AND btree_col1 = 'abcdefg999' OR btree_col2 = '2015-01-01';

select count(1) from bmcrash where btree_col1 = 'abcdefg999' AND bitmap_col = '999';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' OR bitmap_col = '999' AND btree_col2 = '2015-01-01';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' AND bitmap_col = '999' OR btree_col2 = '2015-01-01';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' AND btree_col2 = '2015-01-01' OR bitmap_col = '999';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' AND btree_col2 = '2015-01-01' AND bitmap_col = '999';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' OR btree_col2 = '2015-01-01' AND bitmap_col = '999';
select count(1) from bmcrash where btree_col1 = 'abcdefg999' OR btree_col2 = '2015-01-01' OR bitmap_col = '999';

set enable_bitmapscan=on; 
set enable_hashjoin=off; 
set enable_indexscan=on; 
set enable_nestloop=on; 
set enable_seqscan=off;

select count(1) from bmcrash where btree_col1 = 'abcdefg999' AND bitmap_col = '999' OR bitmap_col = '888' OR btree_col2 = '2015-01-01';
select count(1) from bmcrash b1, bmcrash b2 where b1.bitmap_col = b2.bitmap_col or b1.bitmap_col = '999' and b1.btree_col1 = 'abcdefg999';

set optimizer_enable_hashjoin=off;
with bm as (select * from bmcrash where btree_col1 = 'abcdefg999' AND bitmap_col = '999' OR bitmap_col = '888' OR btree_col2 = '2015-01-01')
select count(1) from bm b1, bm b2 where b1.dist_col = b2.dist_col;

-- qual with like, any.
with bm as (select * from bmcrash where (btree_col1 like 'abcde%') AND bitmap_col in ('999', '888'))
select count(1) from bm b1, bm b2 where b1.dist_col = b2.dist_col;

-- start_ignore
drop schema bm_ao cascade;
-- end_ignore