Thread: [BUGS] BUG #14563: count(*) gives a wrong result in PostgreSQL for someregex with pg_trm/GIN index
[BUGS] BUG #14563: count(*) gives a wrong result in PostgreSQL for someregex with pg_trm/GIN index
From
isayko.alexey@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 14563 Logged by: Alexey Isayko Email address: isayko.alexey@gmail.com PostgreSQL version: 9.6.1 Operating system: Windows 10.0.14393 x64 Description: When using some RE to filter rows on gin-indexed column with pg_trm, count(*) could give a wrong result (zero instead of real count): CREATE EXTENSION pg_trgm; CREATE TABLE t ( s text ); CREATE INDEX ON t USING gin (s gin_trgm_ops); INSERT INTO t VALUES ('12-34'); SET enable_seqscan = OFF; -- to force using the index on our small table SELECT count(*) FROM t WHERE s~'\d{2}[-]?\d{2}'; -- gives me 0 and it's wrong But if we will turn on seq scans, we will get the correct result: SET enable_seqscan = ON; SELECT count(*) FROM t WHERE s~'\d{2}[-]?\d{2}'; -- gives me 1 and it's correct Same behaviour I get with 9.5.4 (windows and linux) and 9.6.1 (windows) versions of the PostgreSQL -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14563: count(*) gives a wrong result in PostgreSQL for some regex with pg_trm/GIN index
From
Tom Lane
Date:
isayko.alexey@gmail.com writes: > When using some RE to filter rows on gin-indexed column with pg_trm, > count(*) could give a wrong result (zero instead of real count): Duplicated that here, will look into it. Thanks for the report! regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs