-- -- Test roaringbitmap extension -- CREATE EXTENSION if not exists roaringbitmap; -- Test input and output set roaringbitmap.output_format='array'; set extra_float_digits = 0; select '{}'::roaringbitmap; roaringbitmap --------------- {} (1 row) select ' { } '::roaringbitmap; roaringbitmap --------------- {} (1 row) select '{ 1 }'::roaringbitmap; roaringbitmap --------------- {1} (1 row) select '{-1,2,555555,-4}'::roaringbitmap; roaringbitmap ------------------ {2,555555,-4,-1} (1 row) select '{ -1 , 2 , 555555 , -4 }'::roaringbitmap; roaringbitmap ------------------ {2,555555,-4,-1} (1 row) select '{ 1 , -2 , 555555 , -4 }'::roaringbitmap; roaringbitmap ------------------ {1,555555,-4,-2} (1 row) select '{ 1 , -2 , 555555 , -4 ,2147483647,-2147483648}'::roaringbitmap; roaringbitmap ----------------------------------------- {1,555555,2147483647,-2147483648,-4,-2} (1 row) select roaringbitmap('{ 1 , -2 , 555555 , -4 }'); roaringbitmap ------------------ {1,555555,-4,-2} (1 row) set roaringbitmap.output_format='bytea'; select '{}'::roaringbitmap; roaringbitmap -------------------- \x3a30000000000000 (1 row) select '{ -1 , 2 , 555555 , -4 }'::roaringbitmap; roaringbitmap ------------------------------------------------------------------------------------ \x3a300000030000000000000008000000ffff01002000000022000000240000000200237afcffffff (1 row) set roaringbitmap.output_format='array'; select '{}'::roaringbitmap; roaringbitmap --------------- {} (1 row) select '\x3a30000000000000'::roaringbitmap; roaringbitmap --------------- {} (1 row) select '\x3a300000030000000000000008000000ffff01002000000022000000240000000200237afcffffff'::roaringbitmap; roaringbitmap ------------------ {2,555555,-4,-1} (1 row) -- Exception select ''::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select ''::roaringbitmap; ^ select '{'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{'::roaringbitmap; ^ select '{1'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{1'::roaringbitmap; ^ select '{1} x'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{1} x'::roaringbitmap; ^ select '{1x}'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{1x}'::roaringbitmap; ^ select '{-x}'::roaringbitmap; ERROR: invalid input syntax for integer: "-x}" LINE 1: select '{-x}'::roaringbitmap; ^ select '{,}'::roaringbitmap; ERROR: invalid input syntax for integer: ",}" LINE 1: select '{,}'::roaringbitmap; ^ select '{1,}'::roaringbitmap; ERROR: invalid input syntax for integer: "}" LINE 1: select '{1,}'::roaringbitmap; ^ select '{1,xxx}'::roaringbitmap; ERROR: invalid input syntax for integer: "xxx}" LINE 1: select '{1,xxx}'::roaringbitmap; ^ select '{1,3'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{1,3'::roaringbitmap; ^ select '{1,1'::roaringbitmap; ERROR: malformed bitmap literal LINE 1: select '{1,1'::roaringbitmap; ^ select '{1,-2147483649}'::roaringbitmap; ERROR: value "-2147483649}" is out of range for type integer LINE 1: select '{1,-2147483649}'::roaringbitmap; ^ select '{2147483648}'::roaringbitmap; ERROR: value "2147483648}" is out of range for type integer LINE 1: select '{2147483648}'::roaringbitmap; ^ -- Test Type cast select '{}'::roaringbitmap::bytea; bytea -------------------- \x3a30000000000000 (1 row) select '{1}'::roaringbitmap::bytea; bytea ---------------------------------------- \x3a3000000100000000000000100000000100 (1 row) select '{1,9999}'::roaringbitmap::bytea; bytea -------------------------------------------- \x3a30000001000000000001001000000001000f27 (1 row) select '{}'::roaringbitmap::bytea::roaringbitmap; roaringbitmap --------------- {} (1 row) select '{1}'::roaringbitmap::bytea::roaringbitmap; roaringbitmap --------------- {1} (1 row) select '{1,9999,-88888}'::roaringbitmap::bytea::roaringbitmap; roaringbitmap ----------------- {1,9999,-88888} (1 row) select roaringbitmap('{1,9999,-88888}'::roaringbitmap::bytea); roaringbitmap ----------------- {1,9999,-88888} (1 row) -- Exception select roaringbitmap('\x11'::bytea); ERROR: bitmap format is error select '\x11'::bytea::roaringbitmap; ERROR: bitmap format is error -- Test Opperator select roaringbitmap('{}') & roaringbitmap('{}'); ?column? ---------- {} (1 row) select roaringbitmap('{}') & roaringbitmap('{3,4,5}'); ?column? ---------- {} (1 row) select roaringbitmap('{1,2,3}') & roaringbitmap('{}'); ?column? ---------- {} (1 row) select roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}'); ?column? ---------- {3} (1 row) select roaringbitmap('{1,-2,-3}') & roaringbitmap('{-3,-4,5}'); ?column? ---------- {-3} (1 row) select roaringbitmap('{}') | roaringbitmap('{}'); ?column? ---------- {} (1 row) select roaringbitmap('{}') | roaringbitmap('{3,4,5}'); ?column? ---------- {3,4,5} (1 row) select roaringbitmap('{1,2,3}') | roaringbitmap('{}'); ?column? ---------- {1,2,3} (1 row) select roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}'); ?column? ------------- {1,2,3,4,5} (1 row) select roaringbitmap('{1,-2,-3}') | roaringbitmap('{-3,-4,5}'); ?column? ---------------- {1,5,-4,-3,-2} (1 row) select roaringbitmap('{}') | 6; ?column? ---------- {6} (1 row) select roaringbitmap('{1,2,3}') | 6; ?column? ----------- {1,2,3,6} (1 row) select roaringbitmap('{1,2,3}') | 1; ?column? ---------- {1,2,3} (1 row) select roaringbitmap('{1,2,3}') | -1; ?column? ------------ {1,2,3,-1} (1 row) select roaringbitmap('{-1,-2,3}') | -1; ?column? ----------- {3,-2,-1} (1 row) select 6 | roaringbitmap('{}'); ?column? ---------- {6} (1 row) select 6 | roaringbitmap('{1,2,3}'); ?column? ----------- {1,2,3,6} (1 row) select 1 | roaringbitmap('{1,2,3}'); ?column? ---------- {1,2,3} (1 row) select -1 | roaringbitmap('{1,2,3}'); ?column? ------------ {1,2,3,-1} (1 row) select -1 | roaringbitmap('{-1,-2,3}'); ?column? ----------- {3,-2,-1} (1 row) select roaringbitmap('{}') # roaringbitmap('{}'); ?column? ---------- {} (1 row) select roaringbitmap('{}') # roaringbitmap('{3,4,5}'); ?column? ---------- {3,4,5} (1 row) select roaringbitmap('{1,2,3}') # roaringbitmap('{}'); ?column? ---------- {1,2,3} (1 row) select roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}'); ?column? ----------- {1,2,4,5} (1 row) select roaringbitmap('{1,-2,-3}') # roaringbitmap('{-3,-4,5}'); ?column? ------------- {1,5,-4,-2} (1 row) select roaringbitmap('{}') - roaringbitmap('{}'); ?column? ---------- {} (1 row) select roaringbitmap('{}') - roaringbitmap('{3,4,5}'); ?column? ---------- {} (1 row) select roaringbitmap('{1,2,3}') - roaringbitmap('{}'); ?column? ---------- {1,2,3} (1 row) select roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}'); ?column? ---------- {1,2} (1 row) select roaringbitmap('{1,-2,-3}') - roaringbitmap('{-3,-4,5}'); ?column? ---------- {1,-2} (1 row) select roaringbitmap('{}') - 3; ?column? ---------- {} (1 row) select roaringbitmap('{1,2,3}') - 3; ?column? ---------- {1,2} (1 row) select roaringbitmap('{1,2,3}') - 1; ?column? ---------- {2,3} (1 row) select roaringbitmap('{1,2,3}') - -1; ?column? ---------- {1,2,3} (1 row) select roaringbitmap('{-1,-2,3}') - -1; ?column? ---------- {3,-2} (1 row) select roaringbitmap('{}') << 2; ?column? ---------- {} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << 2; ?column? ----------------------------------- {0,1,2147483645,2147483646,-4,-3} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << 1; ?column? ------------------------------------- {0,1,2,2147483646,2147483647,-3,-2} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << 0; ?column? ---------------------------------------- {0,1,2,3,2147483647,-2147483648,-2,-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << -1; ?column? -------------------------------------- {1,2,3,4,-2147483648,-2147483647,-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << -2; ?column? ----------------------------------- {2,3,4,5,-2147483647,-2147483646} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << 4294967295; ?column? ---------- {0} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << 4294967296; ?column? ---------- {} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << -4294967295; ?column? ---------- {-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') << -4294967296; ?column? ---------- {} (1 row) select roaringbitmap('{}') >> 2; ?column? ---------- {} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> 2; ?column? ----------------------------------- {2,3,4,5,-2147483647,-2147483646} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> 1; ?column? -------------------------------------- {1,2,3,4,-2147483648,-2147483647,-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> 0; ?column? ---------------------------------------- {0,1,2,3,2147483647,-2147483648,-2,-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> -1; ?column? ------------------------------------- {0,1,2,2147483646,2147483647,-3,-2} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> -2; ?column? ----------------------------------- {0,1,2147483645,2147483646,-4,-3} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> 4294967295; ?column? ---------- {-1} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> 4294967296; ?column? ---------- {} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> -4294967295; ?column? ---------- {0} (1 row) select roaringbitmap('{-2,-1,0,1,2,3,2147483647,-2147483648}') >> -4294967296; ?column? ---------- {} (1 row) select roaringbitmap('{}') @> roaringbitmap('{}'); ?column? ---------- t (1 row) select roaringbitmap('{}') @> roaringbitmap('{3,4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') @> roaringbitmap('{}'); ?column? ---------- t (1 row) select roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') @> roaringbitmap('{3,2}'); ?column? ---------- t (1 row) select roaringbitmap('{1,-2,-3}') @> roaringbitmap('{-3,1}'); ?column? ---------- t (1 row) select roaringbitmap('{}') @> 2; ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') @> 20; ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') @> 1; ?column? ---------- t (1 row) select roaringbitmap('{1,2,3}') @> -1; ?column? ---------- f (1 row) select roaringbitmap('{-1,-2,3}') @> -1; ?column? ---------- t (1 row) select roaringbitmap('{}') <@ roaringbitmap('{}'); ?column? ---------- t (1 row) select roaringbitmap('{}') <@ roaringbitmap('{3,4,5}'); ?column? ---------- t (1 row) select roaringbitmap('{1,2,3}') <@ roaringbitmap('{}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') <@ roaringbitmap('{3,4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{2,3}') <@ roaringbitmap('{1,3,2}'); ?column? ---------- t (1 row) select roaringbitmap('{1,-3}') <@ roaringbitmap('{-3,1,1000}'); ?column? ---------- t (1 row) select 6 <@ roaringbitmap('{}'); ?column? ---------- f (1 row) select 3 <@ roaringbitmap('{1,2,3}'); ?column? ---------- t (1 row) select 1 <@ roaringbitmap('{1,2,3}'); ?column? ---------- t (1 row) select -1 <@ roaringbitmap('{1,2,3}'); ?column? ---------- f (1 row) select -1 <@ roaringbitmap('{-1,-2,3}'); ?column? ---------- t (1 row) select roaringbitmap('{}') && roaringbitmap('{}'); ?column? ---------- f (1 row) select roaringbitmap('{}') && roaringbitmap('{3,4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') && roaringbitmap('{}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}'); ?column? ---------- t (1 row) select roaringbitmap('{1,-2,-3}') && roaringbitmap('{-3,-4,5}'); ?column? ---------- t (1 row) select roaringbitmap('{}') = roaringbitmap('{}'); ?column? ---------- t (1 row) select roaringbitmap('{}') = roaringbitmap('{3,4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') = roaringbitmap('{}'); ?column? ---------- f (1 row) select roaringbitmap('{1,2,3}') = roaringbitmap('{3,1,2}'); ?column? ---------- t (1 row) select roaringbitmap('{1,-2,-3}') = roaringbitmap('{-3,-4,5}'); ?column? ---------- f (1 row) select roaringbitmap('{}') <> roaringbitmap('{}'); ?column? ---------- f (1 row) select roaringbitmap('{}') <> roaringbitmap('{3,4,5}'); ?column? ---------- t (1 row) select roaringbitmap('{1,2,3}') <> roaringbitmap('{}'); ?column? ---------- t (1 row) select roaringbitmap('{1,2,3}') <> roaringbitmap('{3,1,2}'); ?column? ---------- f (1 row) select roaringbitmap('{1,-2,-3}') <> roaringbitmap('{-3,-4,5}'); ?column? ---------- t (1 row) -- Test the functions with one bitmap variable select rb_build(NULL); rb_build ---------- (1 row) select rb_build('{}'::int[]); rb_build ---------- {} (1 row) select rb_build('{1}'::int[]); rb_build ---------- {1} (1 row) select rb_build('{-1,2,555555,-4}'::int[]); rb_build ------------------ {2,555555,-4,-1} (1 row) select rb_build('{1,-2,555555,-4,2147483647,-2147483648}'::int[]); rb_build ----------------------------------------- {1,555555,2147483647,-2147483648,-4,-2} (1 row) select rb_to_array(NULL); rb_to_array ------------- (1 row) select rb_to_array('{}'::roaringbitmap); rb_to_array ------------- {} (1 row) select rb_to_array('{1}'::roaringbitmap); rb_to_array ------------- {1} (1 row) select rb_to_array('{-1,2,555555,-4}'::roaringbitmap); rb_to_array ------------------ {2,555555,-4,-1} (1 row) select rb_to_array('{1,-2,555555,-4,2147483647,-2147483648}'::roaringbitmap); rb_to_array ----------------------------------------- {1,555555,2147483647,-2147483648,-4,-2} (1 row) select rb_is_empty(NULL); rb_is_empty ------------- (1 row) select rb_is_empty('{}'); rb_is_empty ------------- t (1 row) select rb_is_empty('{1}'); rb_is_empty ------------- f (1 row) select rb_is_empty('{1,10,100}'); rb_is_empty ------------- f (1 row) select rb_cardinality(NULL); rb_cardinality ---------------- (1 row) select rb_cardinality('{}'); rb_cardinality ---------------- 0 (1 row) select rb_cardinality('{1}'); rb_cardinality ---------------- 1 (1 row) select rb_cardinality('{1,10,100}'); rb_cardinality ---------------- 3 (1 row) select rb_max(NULL); rb_max -------- (1 row) select rb_max('{}'); rb_max -------- (1 row) select rb_max('{1}'); rb_max -------- 1 (1 row) select rb_max('{1,10,100}'); rb_max -------- 100 (1 row) select rb_max('{1,10,100,2147483647,-2147483648,-1}'); rb_max -------- -1 (1 row) select rb_min(NULL); rb_min -------- (1 row) select rb_min('{}'); rb_min -------- (1 row) select rb_min('{1}'); rb_min -------- 1 (1 row) select rb_min('{1,10,100}'); rb_min -------- 1 (1 row) select rb_min('{1,10,100,2147483647,-2147483648,-1}'); rb_min -------- 1 (1 row) select rb_iterate(NULL); rb_iterate ------------ (0 rows) select rb_iterate('{}'); rb_iterate ------------ (0 rows) select rb_iterate('{1}'); rb_iterate ------------ 1 (1 row) select rb_iterate('{1,10,100}'); rb_iterate ------------ 1 10 100 (3 rows) select rb_iterate('{1,10,100,2147483647,-2147483648,-1}'); rb_iterate ------------- 1 10 100 2147483647 -2147483648 -1 (6 rows) -- Test the functions with two bitmap variables select rb_and(NULL,'{1,10,100}'); rb_and -------- (1 row) select rb_and('{1,10,100}',NULL); rb_and -------- (1 row) select rb_and('{}','{1,10,100}'); rb_and -------- {} (1 row) select rb_and('{1,10,100}','{}'); rb_and -------- {} (1 row) select rb_and('{2}','{1,10,100}'); rb_and -------- {} (1 row) select rb_and('{1,2,10}','{1,10,100}'); rb_and -------- {1,10} (1 row) select rb_and('{1,10}','{1,10,100}'); rb_and -------- {1,10} (1 row) select rb_and_cardinality(NULL,'{1,10,100}'); rb_and_cardinality -------------------- (1 row) select rb_and_cardinality('{1,10,100}',NULL); rb_and_cardinality -------------------- (1 row) select rb_and_cardinality('{}','{1,10,100}'); rb_and_cardinality -------------------- 0 (1 row) select rb_and_cardinality('{1,10,100}','{}'); rb_and_cardinality -------------------- 0 (1 row) select rb_and_cardinality('{2}','{1,10,100}'); rb_and_cardinality -------------------- 0 (1 row) select rb_and_cardinality('{1,2,10}','{1,10,100}'); rb_and_cardinality -------------------- 2 (1 row) select rb_and_cardinality('{1,10}','{1,10,100}'); rb_and_cardinality -------------------- 2 (1 row) select rb_or(NULL,'{1,10,100}'); rb_or ------- (1 row) select rb_or('{1,10,100}',NULL); rb_or ------- (1 row) select rb_or('{}','{1,10,100}'); rb_or ------------ {1,10,100} (1 row) select rb_or('{1,10,100}','{}'); rb_or ------------ {1,10,100} (1 row) select rb_or('{2}','{1,10,100}'); rb_or -------------- {1,2,10,100} (1 row) select rb_or('{1,2,10}','{1,10,100}'); rb_or -------------- {1,2,10,100} (1 row) select rb_or('{1,10}','{1,10,100}'); rb_or ------------ {1,10,100} (1 row) select rb_or_cardinality(NULL,'{1,10,100}'); rb_or_cardinality ------------------- (1 row) select rb_or_cardinality('{1,10,100}',NULL); rb_or_cardinality ------------------- (1 row) select rb_or_cardinality('{}','{1,10,100}'); rb_or_cardinality ------------------- 3 (1 row) select rb_or_cardinality('{1,10,100}','{}'); rb_or_cardinality ------------------- 3 (1 row) select rb_or_cardinality('{2}','{1,10,100}'); rb_or_cardinality ------------------- 4 (1 row) select rb_or_cardinality('{1,2,10}','{1,10,100}'); rb_or_cardinality ------------------- 4 (1 row) select rb_or_cardinality('{1,10}','{1,10,100}'); rb_or_cardinality ------------------- 3 (1 row) select rb_xor(NULL,'{1,10,100}'); rb_xor -------- (1 row) select rb_xor('{1,10,100}',NULL); rb_xor -------- (1 row) select rb_xor('{}','{1,10,100}'); rb_xor ------------ {1,10,100} (1 row) select rb_xor('{1,10,100}','{}'); rb_xor ------------ {1,10,100} (1 row) select rb_xor('{2}','{1,10,100}'); rb_xor -------------- {1,2,10,100} (1 row) select rb_xor('{1,2,10}','{1,10,100}'); rb_xor --------- {2,100} (1 row) select rb_xor('{1,10}','{1,10,100}'); rb_xor -------- {100} (1 row) select rb_xor_cardinality(NULL,'{1,10,100}'); rb_xor_cardinality -------------------- (1 row) select rb_xor_cardinality('{1,10,100}',NULL); rb_xor_cardinality -------------------- (1 row) select rb_xor_cardinality('{}','{1,10,100}'); rb_xor_cardinality -------------------- 3 (1 row) select rb_xor_cardinality('{1,10,100}','{}'); rb_xor_cardinality -------------------- 3 (1 row) select rb_xor_cardinality('{2}','{1,10,100}'); rb_xor_cardinality -------------------- 4 (1 row) select rb_xor_cardinality('{1,2,10}','{1,10,100}'); rb_xor_cardinality -------------------- 2 (1 row) select rb_xor_cardinality('{1,10}','{1,10,100}'); rb_xor_cardinality -------------------- 1 (1 row) select rb_equals(NULL,'{1,10,100}'); rb_equals ----------- (1 row) select rb_equals('{1,10,100}',NULL); rb_equals ----------- (1 row) select rb_equals('{}','{1,10,100}'); rb_equals ----------- f (1 row) select rb_equals('{1,10,100}','{}'); rb_equals ----------- f (1 row) select rb_equals('{2}','{1,10,100}'); rb_equals ----------- f (1 row) select rb_equals('{1,2,10}','{1,10,100}'); rb_equals ----------- f (1 row) select rb_equals('{1,10}','{1,10,100}'); rb_equals ----------- f (1 row) select rb_equals('{1,10,100}','{1,10,100}'); rb_equals ----------- t (1 row) select rb_equals('{1,10,100,10}','{1,100,10}'); rb_equals ----------- t (1 row) select rb_intersect(NULL,'{1,10,100}'); rb_intersect -------------- (1 row) select rb_intersect('{1,10,100}',NULL); rb_intersect -------------- (1 row) select rb_intersect('{}','{1,10,100}'); rb_intersect -------------- f (1 row) select rb_intersect('{1,10,100}','{}'); rb_intersect -------------- f (1 row) select rb_intersect('{2}','{1,10,100}'); rb_intersect -------------- f (1 row) select rb_intersect('{1,2,10}','{1,10,100}'); rb_intersect -------------- t (1 row) select rb_intersect('{1,10}','{1,10,100}'); rb_intersect -------------- t (1 row) select rb_intersect('{1,10,100}','{1,10,100}'); rb_intersect -------------- t (1 row) select rb_intersect('{1,10,100,10}','{1,100,10}'); rb_intersect -------------- t (1 row) select rb_andnot(NULL,'{1,10,100}'); rb_andnot ----------- (1 row) select rb_andnot('{1,10,100}',NULL); rb_andnot ----------- (1 row) select rb_andnot('{}','{1,10,100}'); rb_andnot ----------- {} (1 row) select rb_andnot('{1,10,100}','{}'); rb_andnot ------------ {1,10,100} (1 row) select rb_andnot('{2}','{1,10,100}'); rb_andnot ----------- {2} (1 row) select rb_andnot('{1,2,10}','{1,10,100}'); rb_andnot ----------- {2} (1 row) select rb_andnot('{1,10}','{1,10,100}'); rb_andnot ----------- {} (1 row) select rb_andnot('{1,10,100}','{1,10,100}'); rb_andnot ----------- {} (1 row) select rb_andnot('{1,10,100,10}','{1,100,10}'); rb_andnot ----------- {} (1 row) select rb_andnot_cardinality(NULL,'{1,10,100}'); rb_andnot_cardinality ----------------------- (1 row) select rb_andnot_cardinality('{1,10,100}',NULL); rb_andnot_cardinality ----------------------- (1 row) select rb_andnot_cardinality('{}','{1,10,100}'); rb_andnot_cardinality ----------------------- 0 (1 row) select rb_andnot_cardinality('{1,10,100}','{}'); rb_andnot_cardinality ----------------------- 3 (1 row) select rb_andnot_cardinality('{2}','{1,10,100}'); rb_andnot_cardinality ----------------------- 1 (1 row) select rb_andnot_cardinality('{1,2,10}','{1,10,100}'); rb_andnot_cardinality ----------------------- 1 (1 row) select rb_andnot_cardinality('{1,10}','{1,10,100}'); rb_andnot_cardinality ----------------------- 0 (1 row) select rb_andnot_cardinality('{1,10,100}','{1,10,100}'); rb_andnot_cardinality ----------------------- 0 (1 row) select rb_andnot_cardinality('{1,10,100,10}','{1,100,10}'); rb_andnot_cardinality ----------------------- 0 (1 row) select rb_jaccard_dist(NULL,'{1,10,100}'); rb_jaccard_dist ----------------- (1 row) select rb_jaccard_dist('{1,10,100}',NULL); rb_jaccard_dist ----------------- (1 row) select rb_jaccard_dist('{}','{1,10,100}'); rb_jaccard_dist ----------------- 0 (1 row) select rb_jaccard_dist('{1,10,100}','{}'); rb_jaccard_dist ----------------- 0 (1 row) select rb_jaccard_dist('{2}','{1,10,100}'); rb_jaccard_dist ----------------- 0 (1 row) select rb_jaccard_dist('{1,2,10}','{1,10,100}'); rb_jaccard_dist ----------------- 0.5 (1 row) select rb_jaccard_dist('{1,10,11,12}','{1,10,100}'); rb_jaccard_dist ----------------- 0.4 (1 row) select rb_jaccard_dist('{1,10,100}','{1,10,11,12}'); rb_jaccard_dist ----------------- 0.4 (1 row) select rb_jaccard_dist('{1,10,100}','{1,10,100}'); rb_jaccard_dist ----------------- 1 (1 row) select rb_jaccard_dist('{1,10,-100}','{1,10,-100}'); rb_jaccard_dist ----------------- 1 (1 row) select rb_jaccard_dist('{1,10,100}','{1,10,-100}'); rb_jaccard_dist ----------------- 0.5 (1 row) -- Test other functions select rb_rank(NULL,0); rb_rank --------- (1 row) select rb_rank('{}',0); rb_rank --------- 0 (1 row) select rb_rank('{1,10,100}',0); rb_rank --------- 0 (1 row) select rb_rank('{1,10,100}',1); rb_rank --------- 1 (1 row) select rb_rank('{1,10,100}',99); rb_rank --------- 2 (1 row) select rb_rank('{1,10,100}',100); rb_rank --------- 3 (1 row) select rb_rank('{1,10,100}',101); rb_rank --------- 3 (1 row) select rb_rank('{1,10,100,-3,-1}',-2); rb_rank --------- 4 (1 row) select rb_remove(NULL,0); rb_remove ----------- (1 row) select rb_remove('{}',0); rb_remove ----------- {} (1 row) select rb_remove('{1}',1); rb_remove ----------- {} (1 row) select rb_remove('{1,10,100}',0); rb_remove ------------ {1,10,100} (1 row) select rb_remove('{1,10,100}',1); rb_remove ----------- {10,100} (1 row) select rb_remove('{1,10,100}',99); rb_remove ------------ {1,10,100} (1 row) select rb_fill(NULL,0,0); rb_fill --------- (1 row) select rb_fill('{}',0,0); rb_fill --------- {} (1 row) select rb_fill('{}',0,1); rb_fill --------- {0} (1 row) select rb_fill('{}',0,2); rb_fill --------- {0,1} (1 row) select rb_fill('{1,10,100}',10,10); rb_fill ------------ {1,10,100} (1 row) select rb_fill('{1,10,100}',10,11); rb_fill ------------ {1,10,100} (1 row) select rb_fill('{1,10,100}',10,12); rb_fill --------------- {1,10,11,100} (1 row) select rb_fill('{1,10,100}',10,13); rb_fill ------------------ {1,10,11,12,100} (1 row) select rb_fill('{1,10,100}',10,20); rb_fill --------------------------------------- {1,10,11,12,13,14,15,16,17,18,19,100} (1 row) select rb_fill('{1,10,100}',0,-1); rb_fill ------------ {1,10,100} (1 row) select rb_cardinality(rb_fill('{1,10,100}',2,1000000000)); rb_cardinality ---------------- 999999999 (1 row) select rb_cardinality(rb_fill('{1,10,100}',-1,5000000000)); rb_cardinality ---------------- 4294967296 (1 row) select rb_index(NULL,3); rb_index ---------- (1 row) select rb_index('{1,2,3}',NULL); rb_index ---------- (1 row) select rb_index('{}',3); rb_index ---------- -1 (1 row) select rb_index('{1}',3); rb_index ---------- -1 (1 row) select rb_index('{1}',1); rb_index ---------- 0 (1 row) select rb_index('{1,10,100}',10); rb_index ---------- 1 (1 row) select rb_index('{1,10,100}',99); rb_index ---------- -1 (1 row) select rb_index('{1,10,-100}',-100); rb_index ---------- 2 (1 row) select rb_clear(NULL,0,10); rb_clear ---------- (1 row) select rb_clear('{}',0,10); rb_clear ---------- {} (1 row) select rb_clear('{1,10,100}',0,10); rb_clear ---------- {10,100} (1 row) select rb_clear('{1,10,100}',3,3); rb_clear ------------ {1,10,100} (1 row) select rb_clear('{1,10,100}',-3,3); rb_clear ---------- {10,100} (1 row) select rb_clear('{1,10,100}',0,-1); rb_clear ------------ {1,10,100} (1 row) select rb_clear('{1,10,100}',9,9); rb_clear ------------ {1,10,100} (1 row) select rb_clear('{1,10,100}',2,1000000000); rb_clear ---------- {1} (1 row) select rb_clear('{0,1,10,100,-2,-1}',1,4294967295); rb_clear ---------- {0,-1} (1 row) select rb_clear('{0,1,10,100,-2,-1}',0,4294967296); rb_clear ---------- {} (1 row) select rb_flip(NULL,0,10); rb_flip --------- (1 row) select rb_flip('{}',0,10); rb_flip ----------------------- {0,1,2,3,4,5,6,7,8,9} (1 row) select rb_flip('{1,10,100}',9,100); rb_flip -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {1,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100} (1 row) select rb_flip('{1,10,100}',10,101); rb_flip -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {1,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99} (1 row) select rb_flip('{1,10,100}',-3,3); rb_flip -------------- {0,2,10,100} (1 row) select rb_flip('{1,10,100}',0,-1); rb_flip ------------ {1,10,100} (1 row) select rb_flip('{1,10,100}',9,9); rb_flip ------------ {1,10,100} (1 row) select rb_cardinality(rb_flip('{1,10,100}',2,1000000000)); rb_cardinality ---------------- 999999997 (1 row) select rb_cardinality(rb_flip('{1,10,100}',-1,5000000000)); rb_cardinality ---------------- 4294967293 (1 row) select rb_range(NULL,0,10); rb_range ---------- (1 row) select rb_range('{}',0,10); rb_range ---------- {} (1 row) select rb_range('{1,10,100}',0,10); rb_range ---------- {1} (1 row) select rb_range('{1,10,100}',3,3); rb_range ---------- {} (1 row) select rb_range('{1,10,100}',-3,3); rb_range ---------- {1} (1 row) select rb_range('{1,10,100}',0,-1); rb_range ---------- {} (1 row) select rb_range('{1,10,100}',9,9); rb_range ---------- {} (1 row) select rb_range('{1,10,100}',2,1000000000); rb_range ---------- {10,100} (1 row) select rb_range('{0,1,10,100,-2,-1}',1,4294967295); rb_range --------------- {1,10,100,-2} (1 row) select rb_range('{0,1,10,100,-2,-1}',0,4294967296); rb_range -------------------- {0,1,10,100,-2,-1} (1 row) select rb_range_cardinality(NULL,0,10); rb_range_cardinality ---------------------- (1 row) select rb_range_cardinality('{}',0,10); rb_range_cardinality ---------------------- 0 (1 row) select rb_range_cardinality('{1,10,100}',0,10); rb_range_cardinality ---------------------- 1 (1 row) select rb_range_cardinality('{1,10,100}',3,3); rb_range_cardinality ---------------------- 0 (1 row) select rb_range_cardinality('{1,10,100}',-3,3); rb_range_cardinality ---------------------- 1 (1 row) select rb_range_cardinality('{1,10,100}',0,-1); rb_range_cardinality ---------------------- 0 (1 row) select rb_range_cardinality('{1,10,100}',9,9); rb_range_cardinality ---------------------- 0 (1 row) select rb_range_cardinality('{1,10,100}',2,1000000000); rb_range_cardinality ---------------------- 2 (1 row) select rb_range_cardinality('{0,1,10,100,-2,-1}',1,4294967295); rb_range_cardinality ---------------------- 4 (1 row) select rb_range_cardinality('{0,1,10,100,-2,-1}',0,4294967296); rb_range_cardinality ---------------------- 6 (1 row) select rb_select(NULL,10); rb_select ----------- (1 row) select rb_select('{}',10); rb_select ----------- {} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',0); rb_select ----------- {} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',1); rb_select ----------- {0} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2); rb_select ----------- {0,1} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,0); rb_select ----------- {0,1} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1); rb_select ----------- {1,2} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,true); rb_select ------------------ {-2147483648,-2} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,true,9); rb_select ------------------ {-2147483648,-2} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,true,9,4294967295); rb_select -------------------------- {2147483647,-2147483648} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false); rb_select ----------- {1,2} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,9); rb_select ------------ {100,1000} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,10); rb_select ------------ {100,1000} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,10,10); rb_select ----------- {} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,-10,100); rb_select ----------- {1,2} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,-10,-10); rb_select ----------- {} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,false,10,10001); rb_select ------------ {100,1000} (1 row) select rb_select('{0,1,2,10,100,1000,2147483647,-2147483648,-2,-1}',2,1,true,10,10001); rb_select ----------- {10,100} (1 row) -- Test aggregate select rb_and_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_and_agg ------------ (1 row) select rb_and_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_and_agg ------------ {} (1 row) select rb_and_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_and_agg ------------ {1} (1 row) select rb_and_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_and_agg ------------ {1,10,100} (1 row) select rb_and_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_and_agg ------------ {10} (1 row) select rb_and_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_and_agg ------------ {1,10,100} (1 row) select rb_and_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_and_agg ------------ {} (1 row) select rb_and_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_and_agg ------------ {1} (1 row) select rb_and_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_and_agg ------------ {} (1 row) select rb_and_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{2,10}'))) t(id); rb_and_agg ------------ {10} (1 row) select rb_and_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}')),(NULL)) t(id); rb_and_agg ------------ {1,10,100} (1 row) select rb_and_cardinality_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_and_cardinality_agg ------------------------ (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_and_cardinality_agg ------------------------ 0 (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_and_cardinality_agg ------------------------ 1 (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_and_cardinality_agg ------------------------ 3 (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_and_cardinality_agg ------------------------ 1 (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_and_cardinality_agg ------------------------ 3 (1 row) select rb_and_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_and_cardinality_agg ------------------------ 0 (1 row) select rb_and_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_and_cardinality_agg ------------------------ 1 (1 row) select rb_and_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_and_cardinality_agg ------------------------ 0 (1 row) select rb_and_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{2,10}'))) t(id); rb_and_cardinality_agg ------------------------ 1 (1 row) select rb_and_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}')),(NULL)) t(id); rb_and_cardinality_agg ------------------------ 3 (1 row) select rb_or_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_or_agg ----------- (1 row) select rb_or_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_or_agg ----------- {} (1 row) select rb_or_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_or_agg ----------- {1} (1 row) select rb_or_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_or_agg ------------ {1,10,100} (1 row) select rb_or_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_or_agg -------------- {1,2,10,100} (1 row) select rb_or_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_or_agg ------------ {1,10,100} (1 row) select rb_or_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_or_agg ------------ {1,10,100} (1 row) select rb_or_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_or_agg ------------ {1,10,100} (1 row) select rb_or_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_or_agg -------------- {1,2,10,100} (1 row) select rb_or_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{2,10}'))) t(id); rb_or_agg -------------- {1,2,10,100} (1 row) select rb_or_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}')),(NULL)) t(id); rb_or_agg ---------------- {1,10,100,101} (1 row) select rb_or_cardinality_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_or_cardinality_agg ----------------------- (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_or_cardinality_agg ----------------------- 0 (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_or_cardinality_agg ----------------------- 1 (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_or_cardinality_agg ----------------------- 3 (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_or_cardinality_agg ----------------------- 4 (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_or_cardinality_agg ----------------------- 3 (1 row) select rb_or_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_or_cardinality_agg ----------------------- 3 (1 row) select rb_or_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_or_cardinality_agg ----------------------- 3 (1 row) select rb_or_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_or_cardinality_agg ----------------------- 4 (1 row) select rb_or_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{2,10}'))) t(id); rb_or_cardinality_agg ----------------------- 4 (1 row) select rb_or_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}')),(NULL)) t(id); rb_or_cardinality_agg ----------------------- 4 (1 row) select rb_xor_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_xor_agg ------------ (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_xor_agg ------------ {} (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_xor_agg ------------ {1} (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_xor_agg ------------ {1,10,100} (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_xor_agg ------------ {1,2,100} (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_xor_agg ------------ {} (1 row) select rb_xor_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_xor_agg ------------ {1,10,100} (1 row) select rb_xor_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_xor_agg ------------ {10,100} (1 row) select rb_xor_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_xor_agg -------------- {1,2,10,100} (1 row) select rb_xor_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_xor_agg ------------ {1,2,100} (1 row) select rb_xor_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}'))) t(id); rb_xor_agg ------------ {101} (1 row) select rb_xor_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,101}')),(roaringbitmap('{1,100,102}')),(NULL)) t(id); rb_xor_agg ------------- {1,101,102} (1 row) select rb_xor_cardinality_agg(id) from (values (NULL::roaringbitmap)) t(id); rb_xor_cardinality_agg ------------------------ (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{}'))) t(id); rb_xor_cardinality_agg ------------------------ 0 (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{1}'))) t(id); rb_xor_cardinality_agg ------------------------ 1 (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}'))) t(id); rb_xor_cardinality_agg ------------------------ 3 (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_xor_cardinality_agg ------------------------ 3 (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(roaringbitmap('{1,10,100}'))) t(id); rb_xor_cardinality_agg ------------------------ 0 (1 row) select rb_xor_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{}'))) t(id); rb_xor_cardinality_agg ------------------------ 3 (1 row) select rb_xor_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{1}'))) t(id); rb_xor_cardinality_agg ------------------------ 2 (1 row) select rb_xor_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2}'))) t(id); rb_xor_cardinality_agg ------------------------ 4 (1 row) select rb_xor_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(roaringbitmap('{2,10}'))) t(id); rb_xor_cardinality_agg ------------------------ 3 (1 row) select rb_xor_cardinality_agg(id) from (values (roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,100,101}'))) t(id); rb_xor_cardinality_agg ------------------------ 1 (1 row) select rb_xor_cardinality_agg(id) from (values (NULL),(roaringbitmap('{1,10,100}')),(NULL),(roaringbitmap('{1,10,101}')),(roaringbitmap('{1,100,102}')),(NULL)) t(id); rb_xor_cardinality_agg ------------------------ 3 (1 row) select rb_build_agg(id) from (values (NULL::int)) t(id); rb_build_agg -------------- (1 row) select rb_build_agg(id) from (values (1)) t(id); rb_build_agg -------------- {1} (1 row) select rb_build_agg(id) from (values (1),(10)) t(id); rb_build_agg -------------- {1,10} (1 row) select rb_build_agg(id) from (values (1),(10),(10),(100),(1)) t(id); rb_build_agg -------------- {1,10,100} (1 row) -- Test Windows aggregate with t(id,bitmap) as( values(0,NULL),(1,roaringbitmap('{1,10}')),(2,NULL),(3,roaringbitmap('{2,10}')),(4,roaringbitmap('{10,100}')) ) select id,bitmap,rb_and_agg(bitmap) over(order by id),rb_and_cardinality_agg(bitmap) over(order by id) from t; id | bitmap | rb_and_agg | rb_and_cardinality_agg ----+----------+------------+------------------------ 0 | | | 1 | {1,10} | {1,10} | 2 2 | | {1,10} | 2 3 | {2,10} | {10} | 1 4 | {10,100} | {10} | 1 (5 rows) with t(id,bitmap) as( values(0,NULL),(1,roaringbitmap('{1,10}')),(2,NULL),(3,roaringbitmap('{2,10}')),(4,roaringbitmap('{10,100}')) ) select id,bitmap,rb_or_agg(bitmap) over(order by id),rb_or_cardinality_agg(bitmap) over(order by id) from t; id | bitmap | rb_or_agg | rb_or_cardinality_agg ----+----------+--------------+----------------------- 0 | | | 1 | {1,10} | {1,10} | 2 2 | | {1,10} | 2 3 | {2,10} | {1,2,10} | 3 4 | {10,100} | {1,2,10,100} | 4 (5 rows) with t(id,bitmap) as( values(0,NULL),(1,roaringbitmap('{1,10}')),(2,NULL),(3,roaringbitmap('{2,10}')),(4,roaringbitmap('{10,100}')) ) select id,bitmap,rb_xor_agg(bitmap) over(order by id),rb_xor_cardinality_agg(bitmap) over(order by id) from t; id | bitmap | rb_xor_agg | rb_xor_cardinality_agg ----+----------+--------------+------------------------ 0 | | | 1 | {1,10} | {1,10} | 2 2 | | {1,10} | 2 3 | {2,10} | {1,2} | 2 4 | {10,100} | {1,2,10,100} | 4 (5 rows) with t(id) as( values(0),(1),(2),(NULL),(4),(NULL) ) select id,rb_build_agg(id) over(order by id) from t; id | rb_build_agg ----+-------------- 0 | {0} 1 | {0,1} 2 | {0,1,2} 4 | {0,1,2,4} | {0,1,2,4} | {0,1,2,4} (6 rows) -- Test parallel aggregate set max_parallel_workers=8; set max_parallel_workers_per_gather=2; set parallel_setup_cost=0; set parallel_tuple_cost=0; set min_parallel_table_scan_size=0; drop table if exists bitmap_test_tb1; NOTICE: table "bitmap_test_tb1" does not exist, skipping create table bitmap_test_tb1(id int, bitmap roaringbitmap); insert into bitmap_test_tb1 values (NULL,NULL); insert into bitmap_test_tb1 select id,rb_build(ARRAY[id]) from generate_series(1,10000)id; insert into bitmap_test_tb1 values (NULL,NULL); insert into bitmap_test_tb1 values (10001,rb_build(ARRAY[10,100,1000,10000,10001])); explain(costs off) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_build_agg(id) bitmap from bitmap_test_tb1)a; QUERY PLAN -------------------------------------------------------------- Subquery Scan on a -> Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on bitmap_test_tb1 (6 rows) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_build_agg(id) bitmap from bitmap_test_tb1)a; rb_cardinality | rb_min | rb_max ----------------+--------+-------- 10001 | 1 | 10001 (1 row) explain(costs off) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_and_agg(bitmap) bitmap from bitmap_test_tb1)a; QUERY PLAN -------------------------------------------------------------- Subquery Scan on a -> Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on bitmap_test_tb1 (6 rows) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_and_agg(bitmap) bitmap from bitmap_test_tb1)a; rb_cardinality | rb_min | rb_max ----------------+--------+-------- 0 | | (1 row) explain(costs off) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_or_agg(bitmap) bitmap from bitmap_test_tb1)a; QUERY PLAN -------------------------------------------------------------- Subquery Scan on a -> Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on bitmap_test_tb1 (6 rows) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_or_agg(bitmap) bitmap from bitmap_test_tb1)a; rb_cardinality | rb_min | rb_max ----------------+--------+-------- 10001 | 1 | 10001 (1 row) explain(costs off) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_xor_agg(bitmap) bitmap from bitmap_test_tb1)a; QUERY PLAN -------------------------------------------------------------- Subquery Scan on a -> Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on bitmap_test_tb1 (6 rows) select rb_cardinality(bitmap),rb_min(bitmap),rb_max(bitmap) from (select rb_xor_agg(bitmap) bitmap from bitmap_test_tb1)a; rb_cardinality | rb_min | rb_max ----------------+--------+-------- 9997 | 1 | 10001 (1 row) explain(costs off) select rb_and_cardinality_agg(bitmap),rb_or_cardinality_agg(bitmap),rb_xor_cardinality_agg(bitmap) from bitmap_test_tb1; QUERY PLAN -------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on bitmap_test_tb1 (5 rows) select rb_and_cardinality_agg(bitmap),rb_or_cardinality_agg(bitmap),rb_xor_cardinality_agg(bitmap) from bitmap_test_tb1; rb_and_cardinality_agg | rb_or_cardinality_agg | rb_xor_cardinality_agg ------------------------+-----------------------+------------------------ 0 | 10001 | 9997 (1 row) --rb_iterate() not support parallel on PG10 while run on parallel in PG11+ --explain(costs off) --select count(*) from (select rb_iterate(bitmap) from bitmap_test_tb1)a; select count(*) from (select rb_iterate(bitmap) from bitmap_test_tb1)a; count ------- 10005 (1 row) explain(costs off) select id,bitmap, rb_build_agg(id) over(w), rb_or_agg(bitmap) over(w), rb_and_agg(bitmap) over(w), rb_xor_agg(bitmap) over(w) from bitmap_test_tb1 window w as (order by id) order by id limit 10; QUERY PLAN -------------------------------------------------------------- Limit -> WindowAgg -> Sort Sort Key: id -> Gather Workers Planned: 2 -> Parallel Seq Scan on bitmap_test_tb1 (7 rows) select id,bitmap, rb_build_agg(id) over(w), rb_or_agg(bitmap) over(w), rb_and_agg(bitmap) over(w), rb_xor_agg(bitmap) over(w) from bitmap_test_tb1 window w as (order by id) order by id limit 10; id | bitmap | rb_build_agg | rb_or_agg | rb_and_agg | rb_xor_agg ----+--------+------------------------+------------------------+------------+------------------------ 1 | {1} | {1} | {1} | {1} | {1} 2 | {2} | {1,2} | {1,2} | {} | {1,2} 3 | {3} | {1,2,3} | {1,2,3} | {} | {1,2,3} 4 | {4} | {1,2,3,4} | {1,2,3,4} | {} | {1,2,3,4} 5 | {5} | {1,2,3,4,5} | {1,2,3,4,5} | {} | {1,2,3,4,5} 6 | {6} | {1,2,3,4,5,6} | {1,2,3,4,5,6} | {} | {1,2,3,4,5,6} 7 | {7} | {1,2,3,4,5,6,7} | {1,2,3,4,5,6,7} | {} | {1,2,3,4,5,6,7} 8 | {8} | {1,2,3,4,5,6,7,8} | {1,2,3,4,5,6,7,8} | {} | {1,2,3,4,5,6,7,8} 9 | {9} | {1,2,3,4,5,6,7,8,9} | {1,2,3,4,5,6,7,8,9} | {} | {1,2,3,4,5,6,7,8,9} 10 | {10} | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8,9,10} | {} | {1,2,3,4,5,6,7,8,9,10} (10 rows)