
create unlogged table sales_mdam_oppodir_issue
(
  dept int4,
  sdate date,
  item_class serial,
  store int4,
  item int4,
  total_sales numeric
);
create index oppodir_mdam_idx on sales_mdam_oppodir_issue(dept, sdate, item_class, store);

select setseed(0.5);

insert into sales_mdam_oppodir_issue (dept, sdate, item_class, store, total_sales)
select
  dept,
  '1995-01-01'::date + sdate,
  item_class,
  store,
  (random() * 500.0) as total_sales
from
  generate_series(5, 7) dept,
  generate_series(0, 5) sdate,
  generate_series(1, 15) item_class,
  generate_series(1, 25) store;

-- Causes assertion failure on HEAD:
select
  dept,
  sdate,
  item_class,
  store,
  total_sales
from
  sales_mdam_oppodir_issue
where
  dept = 5
  and sdate in ('0001-01-01', '1995-01-01') -- '0001-01-01' is lower than any real date
  and item_class = 1
  and store >= 555555 -- i.e. higher than any value
  order by dept, sdate, item_class, store;

-- EXPLAIN ANALYZE of the same query should show 2 buffers hit -- the root
-- page and a single leaf page.  It should not show 4 pages, as on HEAD
-- (duplicative leaf page accesses were supposed to be a thing of the past
-- after the v17 nbtree SAOP enhancements):
explain (analyze, buffers)
select
  dept,
  sdate,
  item_class,
  store,
  total_sales
from
  sales_mdam_oppodir_issue
where
  dept = 5
  and sdate in ('0001-01-01', '1995-01-01') -- '0001-01-01' is lower than any real date
  and item_class = 1
  and store >= 555555 -- i.e. higher than any value
  order by dept, sdate, item_class, store;
