BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower).
От | PG Bug reporting form |
---|---|
Тема | BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower). |
Дата | |
Msg-id | 17350-b5bdcf476e5badbb@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower).
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17350 Logged by: louis jachiet Email address: louis.jachiet@telecom-paris.fr PostgreSQL version: 13.4 Operating system: Archlinux Description: Hello everyone, I hope this is not a duplicate of a similar bug (I tried to check…). GIST TRGM Index seems to return wrong values when populated with a string function (such as lower) and an include. If I issue the following lines, instead of returning 'foo' and 'bar', postgresql will return two empty lines: CREATE EXTENSION IF NOT EXISTS pg_trgm ; CREATE TABLE t (a VARCHAR(50)); INSERT INTO t VALUES ('foo') ; INSERT INTO t VALUES ('BAR') ; CREATE INDEX test_idx ON t USING gist(lower(a) gist_trgm_ops) INCLUDE (a) ; set enable_seqscan=off ; SELECT lower(a) FROM t ; Obviously for this last SELECT, the index is only useful because of the seqscan=off but if you have a large database and issue a command like: SELECT 1 FROM t WHERE lower(a) LIKE '%o%' ; then the output will be also empty because the index will return empty lines and the recheck condition will fail. If I use a function different than lower (e.g. upper) the problem persists but if remove the function or if remove the include then everything works just fine. It really seems that it is the combination of include + string function + gist trgm that makes the SELECT return empty lines. Thank you for your time! Regards
В списке pgsql-bugs по дате отправления: