-- start_matchsubs
-- m/\(cost=.*\)/
-- s/\(cost=.*\)//
-- end_matchsubs
create extension if not exists gp_inject_fault;

SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;

create table bm_test (i int, t text);
insert into bm_test select i % 10, (i % 10)::text  from generate_series(1, 100) i;
create index bm_test_idx on bm_test using bitmap (i);
select count(*) from bm_test where i=1;
select count(*) from bm_test where i in(1, 3);

 -- this sql should confirm that the tuple with i=1
 -- and the tuple with i=5 are on different segments
select count(distinct gp_segment_id) from bm_test where i in (1, 5);
select count(*) from bm_test where i in(1, 5);

select * from bm_test where i > 10;
reindex index bm_test_idx;
select count(*) from bm_test where i in(1, 3);
drop index bm_test_idx;
create index bm_test_coll_idx on bm_test using bitmap(i, t COLLATE "C");
select * from bm_test where i=5 and t='5';
select * from bm_test where i=5 or t='6';

 -- this sql should confirm that the tuple with i=5
 -- and the tuple with t='1' are on different segments
 select count(distinct gp_segment_id) from bm_test where i=5 or t='1';
select * from bm_test where i=5 or t='1';

select * from bm_test where i between 1 and 10 and i::text = t;
drop table bm_test;

-- test a bunch of different data types
create table bm_test (i2 int2, i4 int4, i8 int8, f4 float4, f8 float8,
	n numeric(10, 3), t1 varchar(3), t2 char(3), t3 text, a int[2],
	ip inet, b bytea, t timestamp, d date, g bool);

insert into bm_test values(1, 1, 1, 1.0, 1.0, 1000.333, '1', '1', '1',
    array[1, 3], '127.0.0.1', E'\001', '2007-01-01 01:01:01',
    '2007-01-01', 't');

insert into bm_test values(2, 2, 2, 2.0, 2.0, 2000.333, '2', '2', 'foo',
    array[2, 6], '127.0.0.2', E'\002', '2007-01-02 01:01:01',
    '2007-01-02', 'f');

insert into bm_test default values; -- test nulls

create index bm_i2_idx on bm_test using bitmap(i2);
create index bm_i4_idx on bm_test using bitmap(i4);
create index bm_i8_idx on bm_test using bitmap(i8);

create index bm_f4_idx on bm_test using bitmap(f4);
create index bm_f8_idx on bm_test using bitmap(f8);

create index bm_n_idx on bm_test using bitmap(n);

create index bm_t1_idx on bm_test using bitmap(t1);
create index bm_t2_idx on bm_test using bitmap(t2);
create index bm_t3_idx on bm_test using bitmap(t3);

create index bm_a_idx on bm_test using bitmap(a);

create index bm_ip_idx on bm_test using bitmap(ip);

create index bm_b_idx on bm_test using bitmap(b);

create index bm_t_idx on bm_test using bitmap(t);

create index bm_d_idx on bm_test using bitmap(d);

create index bm_g_idx on bm_test using bitmap(g);

create index bm_t3_upper_idx on bm_test using bitmap(upper(t3));
create index bm_n_null_idx on bm_test using bitmap(n) WHERE n ISNULL;
-- Try some cross type stuff
select a.t from bm_test a, bm_test b where a.i2 = b.i2;
select a.t from bm_test a, bm_test b where a.i2 = b.i4;
select a.t from bm_test a, bm_test b where a.i2 = b.i8;
select a.t from bm_test a, bm_test b where b.f4 = a.f8 and a.f8 = '2.0';

-- some range queries
select a.t from bm_test a, bm_test b where a.n < b.n;
select a.t from bm_test a, bm_test b where a.ip < b.ip;

-- or queries
select a.t from bm_test a, bm_test b where a.ip=b.ip OR a.b = b.b;

-- and
select a.t from bm_test a, bm_test b where a.ip=b.ip and a.b = b.b and a.i2=1;

-- subquery
select a.t from bm_test a where d in(select d from bm_test b where a.g=b.g);

-- functional and predicate indexes
select t from bm_test where upper(t3) = 'FOO';
select t from bm_test where n ISNULL;
-- test updates
update bm_test set i4 = 3;
-- should return nothing
select * from bm_test where i4 = 1;
-- should return all
select * from bm_test where i4=3;
-- should return one row
select * from bm_test where i2=1;
-- test splitting of words
-- We distribute by k and only insert a single distinct value in that 
-- field so that we can be guaranteed of behaviour. We're not testing
-- the parallel mechanism here so it's fine to harass a single backend
create table bm_test2 (i int, j int, k int) distributed by (k);
create index bm_test2_i_idx on bm_test2 using bitmap(i);
insert into bm_test2 select 1,
case when (i % (16 * 16 + 8)) = 0 then 2  else 1 end, 1
from generate_series(1, 16 * 16 * 16) i;
select count(*) from bm_test2 where i = 1;
select count(*) from bm_test2 where j = 2;
-- break some compressed words
update bm_test2 set i = 2 where j = 2;
select count(*) from bm_test2 where i = 1;
select count(*) from bm_test2 where i = 2;
update bm_test2 set i = 3 where i = 1;
select count(*) from bm_test2 where i = 1;
select count(*) from bm_test2 where i = 2;
select count(*) from bm_test2 where i = 3;
-- now try and break a whole page
-- bitmap words are 16 bits so, with no compression we get about 
-- 16500 words per 32K page. So, what we want to do is, insert
-- 8250 uncompressed words, then a compressed word, then more uncompressed
-- words until the page is full. After this, we can break the compressed word
-- and there by test the word spliting system
create table bm_test3 (i int, j int, k int) distributed by (k);
create index bm_test3_i_idx on bm_test3 using bitmap(i);
insert into bm_test3 select i, 1, 1 from
generate_series(1, 8250 * 8) g, generate_series(1, 2) i;
insert into bm_test3 

select 17, 1, 1 from generate_series(1, 16 * 16) i;
insert into bm_test3 values(17, 2, 1);
insert into bm_test3
select 17, 1, 1 from generate_series(1, 16 * 16) i;

insert into bm_test3 select i, 1, 1 from
generate_series(1, 8250 * 8) g, generate_series(1, 2) i;
select count(*) from bm_test3 where i = 1;
select count(*) from bm_test3 where i = 17;
select count(*) from bm_test3 where i = 17 and j = 2;
update bm_test3 set i = 18 where i = 17 and j = 2;
select count(*) from bm_test3 where i = 1;
select count(*) from bm_test3 where i = 2;
select count(*) from bm_test3 where i = 17;
select count(*) from bm_test3 where i = 18;
drop table bm_test;
drop table bm_test2;
drop table bm_test3;

create table bm_test (i int, j int);
insert into bm_test values (0, 0), (0, 0), (0, 1), (1,0), (1,0), (1,1);
analyze bm_test;
create index bm_test_j on bm_test using bitmap(j);
delete from bm_test where j =1;
vacuum bm_test;
insert into bm_test values (0, 0), (1,0);

set enable_seqscan=off;
set enable_bitmapscan=off;
set optimizer_enable_bitmapscan=off;
-- start_ignore
-- Known_opt_diff: MPP-19808
-- end_ignore
explain select * from bm_test where j = 1;
select * from bm_test where j = 1;
drop table bm_test;
-- MPP-3232
create table bm_test (i int,j int);
insert into bm_test values (1, 1), (1, 2);
create index bm_test_j on bm_test using bitmap(j);
update bm_test set j=20 where j=1;
vacuum bm_test;
drop table bm_test;

-- unique index with null value tests
drop table if exists ijk;
create table ijk(i int, j int, k int) distributed by (i);
insert into ijk values (1, 1, 3);
insert into ijk values (1, 2, 4);
insert into ijk values (1, 3, NULL);
insert into ijk values (1, 3, NULL);
insert into ijk values (1, NULL, NULL);
insert into ijk values (1, NULL, NULL);

-- should fail.
create unique index ijk_i on ijk(i);
create unique index ijk_ij on ijk(i,j);
-- should OK.
create unique index ijk_ijk on ijk(i,j,k);

drop table if exists ijk;
create table ijk(i int, j int, k int) distributed by (i);
insert into ijk values (1, 1, 3);
insert into ijk values (1, 2, 4);
insert into ijk values (1, 3, NULL);
insert into ijk values (1, 3, NULL);
insert into ijk values (1, NULL, NULL);
insert into ijk values (1, NULL, NULL);

-- should fail.
create unique index ijk_i on ijk(i);
create unique index ijk_ij on ijk(i,j);
-- should OK.
create unique index ijk_ijk on ijk(i,j,k);

drop table ijk;

--
-- test bitmaps with NULL and non-NULL values (MPP-8461)
--
create table bmap_test (x int, y int, z int);
insert into bmap_test values (1,NULL,NULL);
insert into bmap_test values (NULL,1,NULL);
insert into bmap_test values (NULL,NULL,1);
insert into bmap_test values (1,NULL,NULL);
insert into bmap_test values (NULL,1,NULL);
insert into bmap_test values (NULL,NULL,1);
insert into bmap_test values (1,NULL,5);
insert into bmap_test values (NULL,1,NULL);
insert into bmap_test values (NULL,NULL,1);
insert into bmap_test select a from generate_series(1,10*1000) as s(a);
create index bmap_test_idx_1 on bmap_test using bitmap (x,y,z);
analyze bmap_test;
select * from bmap_test where x = 1 order by x,y,z;

drop table bmap_test;

--
-- Test over-sized values
--
create table oversize_test (c1 text);
CREATE INDEX oversize_test_idx ON oversize_test USING BITMAP (c1);
insert into oversize_test values ('a');
select * from oversize_test;
-- this fails, because the value is too large
insert into oversize_test values (array_to_string(array(select generate_series(1, 10000)), '123456789'));
set enable_seqscan=off;
select * from oversize_test where c1 < 'z';

-- Drop the index, insert the row, and then try creating the index again. This is essentially
-- the same test, but now the failure happens during CREATE INDEX rather than INSERT.
drop index oversize_test_idx;
insert into oversize_test values (array_to_string(array(select generate_series(1, 10000)), '123456789'));
CREATE INDEX oversize_test_idx ON oversize_test USING BITMAP (c1);

--
-- Test Index Only Scans.
--
-- Bitmap indexes don't really support index only scans at the moment. But the
-- planner can still choose an Index Only Scan, if the query doesn't need any
-- of the attributes from the index.
create table bm_indexonly_test (i int, t text);
insert into bm_indexonly_test select g, 'foo' || g from generate_series(1, 5) g;
create index on bm_indexonly_test using bitmap (i, t);
set enable_seqscan=off;
set enable_bitmapscan=off;
set enable_indexscan=on;
set enable_indexonlyscan=on;

-- if bitmap indexes supported Index Only Scans, properly, this could use one.
explain (costs off) select i, t from bm_indexonly_test where i = 1;
select i, t from bm_indexonly_test where i = 1;

-- even without proper support, the planner chooses an Index Only Scan for this.
explain (costs off) select 'foobar' from bm_indexonly_test;
select 'foobar' from bm_indexonly_test;

--
-- Test unlogged table
--
set enable_seqscan=off;
set enable_indexscan=on;
set optimizer_enable_bitmapscan=on;
create unlogged table unlogged_test(c1 int); 
insert into unlogged_test select * from generate_series(1,1000);
CREATE INDEX unlogged_test_idx ON unlogged_test USING BITMAP (c1);
analyze unlogged_test;
explain select * from unlogged_test where c1 = 100;
select * from unlogged_test where c1 = 100;
drop table unlogged_test;

--
-- Test crash recovery
--

--
-- disable fault-tolerance service (FTS) probing to ensure
-- the mirror does not accidentally get promoted
--
SELECT gp_inject_fault_infinite('fts_probe', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' and content = -1;
CREATE TABLE bm_test_insert(a int) DISTRIBUTED BY (a);
CREATE INDEX bm_a_idx ON bm_test_insert USING bitmap(a);
CREATE TABLE bm_test_update(a int, b int) DISTRIBUTED BY (a);
CREATE INDEX bm_b_idx ON bm_test_update USING bitmap(b);
INSERT INTO bm_test_update SELECT i,i FROM generate_series (1, 10000) i;
-- flush the data to disk
CHECKPOINT;
-- skip all further checkpoint
SELECT gp_inject_fault_infinite('checkpoint', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content > -1;
INSERT INTO bm_test_insert SELECT generate_series (1, 10000);
UPDATE bm_test_update SET b=b+1;
-- trigger recovery on primaries 
SELECT gp_inject_fault_infinite('finish_prepared_after_record_commit_prepared', 'panic', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content > -1;
SET client_min_messages='ERROR';
CREATE TABLE trigger_recovery_on_primaries(c int);
RESET client_min_messages;
-- reconnect to the database after restart
\c
SELECT gp_inject_fault('checkpoint', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content > -1;
SELECT gp_inject_fault('finish_prepared_after_record_commit_prepared', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content > -1;
SET enable_seqscan=off;
SET enable_indexscan=off;
SELECT * FROM bm_test_insert WHERE a=1;
SELECT * FROM bm_test_update WHERE b=1;
DROP TABLE trigger_recovery_on_primaries;
DROP TABLE bm_test_insert;
DROP TABLE bm_test_update;

--
-- re-enable fault-tolerance service (FTS) probing after recovery completed.
--
SELECT gp_inject_fault('fts_probe', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' and content = -1;


-- If the table is AO table, it need generate some fake tuple pointer,
-- this pointer is a little different from the heap tables pointer,
-- If the Offset in pointer is 0(If the row number is 32768, the Offset
-- should be 0), we set the 16th bit of the Offsert to be 1, so we
-- do not forget to remove the flag when we use it, otherwise we will
-- get an wrong value.
CREATE TABLE bm_test_reindex(c1 int, c2 int) WITH (appendonly=true);
CREATE INDEX bm_test_reindex_idx ON bm_test_reindex USING bitmap(c2);
INSERT INTO bm_test_reindex SELECT 1,i FROM generate_series(1, 65537)i;
REINDEX INDEX bm_test_reindex_idx;
SET enable_bitmapscan to on;
SET enable_seqscan to off;
SELECT * from bm_test_reindex where c2 = 32767;
SELECT * from bm_test_reindex where c2 = 32768;
SELECT * from bm_test_reindex where c2 = 32769;
SELECT * from bm_test_reindex where c2 = 65536;

SET enable_seqscan = ON;
SET enable_indexscan = ON;

--
-- correct cost estimate to avoid bm index scan for wrong result
--
CREATE TABLE test_bmselec(id int, type int, msg text) distributed by (id);
INSERT INTO test_bmselec (id, type, msg) SELECT g, g % 10000, md5(g::text) FROM generate_series(1,100000) as g;
CREATE INDEX ON test_bmselec USING bitmap(type);
ANALYZE test_bmselec;

-- it used to choose bitmap index over seq scan, which not right.
explain (analyze, verbose) select * from test_bmselec where type < 500;

SET enable_seqscan = OFF;
SET enable_bitmapscan = OFF;
-- we can see the bitmap index scan is much more slower
explain (analyze, verbose) select * from test_bmselec where type < 500;
DROP TABLE test_bmselec;

SET enable_seqscan = ON;
SET enable_bitmapscan = ON;

-- for sparse bitmap index
create table test_bmsparse(id int, type int, msg text) distributed by (id);
INSERT INTO test_bmsparse (id, type, msg) SELECT g, g % 10000, md5(g::text) FROM generate_series(1,10000) as g;
INSERT INTO test_bmsparse (id, type, msg) SELECT g, g % 200, md5(g::text) FROM generate_series(1,80000) as g;
INSERT INTO test_bmsparse (id, type, msg) SELECT g, g % 10000, md5(g::text) FROM generate_series(1,10000) as g;
CREATE INDEX ON test_bmsparse USING bitmap(type);
ANALYZE test_bmsparse;

-- select lots of rows but on small part of distinct values, should use seq scan
explain (analyze, verbose) select * from test_bmsparse where type < 200;

SET enable_seqscan = OFF;
SET enable_bitmapscan = OFF;
explain (analyze, verbose) select * from test_bmsparse where type < 200;

SET enable_seqscan = ON;
SET enable_bitmapscan = ON;
-- select small part of table but on lots of distinct values, should use seq scan
explain (analyze, verbose) select * from test_bmsparse where type > 500;

SET enable_seqscan = OFF;
SET enable_bitmapscan = OFF;
explain (analyze, verbose) select * from test_bmsparse where type > 500;

DROP TABLE test_bmsparse;


-- test the scenario that we need read the same batch words many times
-- more detials can be found at https://github.com/greenplum-db/gpdb/issues/13446
SET enable_seqscan = OFF;
SET enable_bitmapscan = OFF;

create table foo_13446(a int, b int);
create index idx_13446 on foo_13446 using bitmap(b);
insert into foo_13446 select 1, 1 from generate_series(0, 16384);
-- At current implementation, BMIterateResult can only store 16*1024=16384 TIDs,
-- if we have 13685 TIDs to read, it must scan same batch words twice, that's what we want
select count(*) from foo_13446 where b = 1;

drop table foo_13446;

-- test bitmap index scan when using NULL array-condition as index key
create table foo(a int);
create index foo_i on foo using bitmap(a);
explain (verbose on, costs off) select * from foo where a = any(null::int[]);
select * from foo where a = any(null::int[]);

insert into foo values(1);
select * from foo where a = 1 and a = any(null::int[]);
select * from foo where a = 1 or a = any(null::int[]);

drop table foo;

SET enable_seqscan = ON;
SET enable_bitmapscan = ON;

--
-- test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v
-- which creates bitmapand plan on two bitmap indexs that match multiple keys by using in in where clause
--
create table bmunion (a int, b int);
insert into bmunion
  select (r%53), (r%59)
  from generate_series(1,70000) r;
create index bmu_i_bmtest2_a on bmunion using bitmap(a);
create index bmu_i_bmtest2_b on bmunion using bitmap(b);
insert into bmunion select 53, 1 from generate_series(1, 1000);

set optimizer_enable_tablescan=off;
set optimizer_enable_dynamictablescan=off;
-- inject fault for planner so that it could produce bitmapand plan node.
select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1;
explain (costs off) select count(*) from bmunion where a = 53 and b < 3;
select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1;

select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1;
select count(*) from bmunion where a = 53 and b < 3;
select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1;

reset optimizer_enable_tablescan;
reset optimizer_enable_dynamictablescan;

drop table bmunion;


-- test create bitmap index and there have HOT chains.
drop table if exists bm_test;
create table bm_test(a int, b int);

-- insert some data into a one segment
insert into bm_test values (1, 1); 
insert into bm_test values (1, 2); 
insert into bm_test values (1, 3); 
insert into bm_test values (12, 1); 

-- update the first tuple using HOT, since this page
-- just have 4 tuples, there have full free space to
-- use HOT update.
update bm_test set b = 1 where a = 1 and b = 1;

-- After the update, the tids that the value of b is equal to 1
-- we scanned will not be in order, due to HOT.
create index idx_bm_test on bm_test using bitmap(b);
select * from bm_test where b = 1;

-- clean up
drop table bm_test;
