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
