BUG #18855: Using BRIN index with int8_bloom_ops produces incorrect results

The following bug has been logged on the website:

Bug reference: 18855
Logged by: Arseniy Mukhin
Email address: arseniy(dot)mukhin(dot)dev(at)gmail(dot)com
PostgreSQL version: 17.4
Operating system: Ubuntu 24.04.2 LTS
Description:

PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

In this example, the query using the index returns 0 rows, but the query
using seqscan returns 1 row.

How to reproduce:

-- dataset
drop table if exists t1;
create table if not exists t1 (a bigint);
insert into t1 (a) select x from generate_series(1,300000) as x;
create index t1_a_brin_idx on t1 using brin (a int8_bloom_ops);
analyse;

-- helper function to find buggy value. It's experimentally known that there
should be such a value within [289000, 289500].
-- So function iterates through this segment and stops when query returns no
rows;
CREATE OR REPLACE FUNCTION find_missed_value()
RETURNS bigint AS
$$
DECLARE
value bigint;
result RECORD;
BEGIN
-- to make sure we use index
SET enable_bitmapscan = on;

-- Loop through the range
FOR value IN 289000..289500 LOOP
-- Execute simple select
PERFORM * FROM t1 WHERE a = value::bigint;

-- Check if no row was returned
IF NOT FOUND THEN
-- If no row found, return the current value
RETURN value;
END IF;
END LOOP;

-- If no value with 0 rows is found, return NULL
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

select find_missed_value();

-- find_missed_value() returns value which we can use it to demonstrate that
we have different results with and without the index,
-- replace REPLACE_WITH_MISSED_VALUE below with it.

set enable_bitmapscan = off;
explain analyse select * from t1 where a =
REPLACE_WITH_MISSED_VALUE::bigint;
-- seq scan, results rows = 1

set enable_bitmapscan = on;
explain analyse select * from t1 where a =
REPLACE_WITH_MISSED_VALUE::bigint;
-- bitmap index scan, rows = 0