nbtree_array_mark_restore_bug.sql

drop table if exists amber_small;
drop table if exists amber_big;

create table amber_small
(
  a integer,
  b integer
);

create table amber_big
(
  a integer,
  b integer
);

insert into amber_big select 1,  2 from generate_series(1,1024);
insert into amber_big select 1,  3 from generate_series(1,1024);
insert into amber_big select 1,  5 from generate_series(1,1024);
insert into amber_big select 1,  6 from generate_series(1,1024);
insert into amber_big select 1,  7 from generate_series(1,1024);
insert into amber_big select 1,  8 from generate_series(1,1024);
insert into amber_big select 1, 10 from generate_series(1,1024);
insert into amber_big select 1, 12 from generate_series(1,1024);
insert into amber_big select 1, 13 from generate_series(1,1024);
insert into amber_big select 1, 15 from generate_series(1,1024);
insert into amber_big select 1, 17 from generate_series(1,1024);
insert into amber_big select 1, 19 from generate_series(1,1024);

insert into amber_small select 1,  1 from generate_series(1,8);
insert into amber_small select 1,  2 from generate_series(1,8);
insert into amber_small select 1,  3 from generate_series(1,8);
insert into amber_small select 1,  4 from generate_series(1,8);
insert into amber_small select 1,  5 from generate_series(1,8);
insert into amber_small select 1,  9 from generate_series(1,8);
insert into amber_small select 1, 10 from generate_series(1,8);
insert into amber_small select 1, 11 from generate_series(1,8);
insert into amber_small select 1, 12 from generate_series(1,8);
insert into amber_small select 1, 14 from generate_series(1,8);
insert into amber_small select 1, 17 from generate_series(1,8);
insert into amber_small select 1, 18 from generate_series(1,8);
insert into amber_small select 1, 19 from generate_series(1,8);

create index amber_big_idx on amber_big (a, b);
create index amber_small_idx on amber_small (a, b);

vacuum analyze amber_small;
vacuum analyze amber_big;

-- Expected answer is 57,344, which is what we get here:
select count(*) as correct_count_nestloop_join
from
  amber_small small
    inner join
  amber_big big
    on small.a = big.a and small.b = big.b
where small.a in (1, 3) and big.a in (1, 3)
group by small.a order by small.a;

-- Run the same query once more, but force a merge join this time.
-- This variant will give wrong answers on all supported branches:
set enable_nestloop = off;
set enable_hashjoin = off;
set enable_sort = off;
set enable_material = off;
select count(*) as wrong_count_merge_join
from
  amber_small small
    inner join
  amber_big big
    on small.a = big.a and small.b = big.b
where small.a in (1, 3) and big.a in (1, 3)
group by small.a order by small.a;