[PG Bug reporting form <noreply@postgresql.org>]

The following bug has been logged on the website:

Bug reference:      18855
Logged by:          Arseniy Mukhin
Email address:      arseniy.mukhin.dev@gmail.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