From: Tom Lane <tgl@sss.pgh.pa.us>
To: louis.jachiet@telecom-paris.fr
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower).
Date: Sat, 01 Jan 2022 12:43:10 -0500
Message-ID: <2878790.1641058990@sss.pgh.pa.us> (raw)
In-Reply-To: <17350-b5bdcf476e5badbb@postgresql.org>
References: <17350-b5bdcf476e5badbb@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> 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 ;
Fascinating! This is actually a very ancient core-planner bug,
though you could not observe it with all index types. The GIST AM
correctly reports that it can return the value of a, but not the
value of lower(a), in an index-only scan. indxpath.c sees that
it can still make an index-only scan, reasoning that lower(a)
can be recomputed from the returned value of a. But then
set_indexonlyscan_references() screws up: it's told to compute
lower(a) from a tlist that includes lower(a) and a, and it
naturally figures it can just re-use the first output column.
Then at runtime you get a NULL result since that's what the index
will output for non-returnable columns.
We need some mechanism to mark that not all of the index
columns are usable at that step. Doesn't seem terribly hard
to fix, though. Thanks for the report!
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, louis.jachiet@telecom-paris.fr, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower).
In-Reply-To: <2878790.1641058990@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox