BUG #16388: Different results when bitmap scan enabled/disabled
The following bug has been logged on the website:
Bug reference: 16388
Logged by: Charles Offenbacher
Email address: charlie@torqinterface.com
PostgreSQL version: 11.7
Operating system: Ubuntu
Description:
Hi pgsql-bugs,
When Postgres uses a bitmap heap scan to evaluate a tsquery that includes !,
it is giving me different (and incorrect) results compared to when it
performs a seqscan.
Can anybody shed some light on this? Simply enabling / disabling bitmapscan
changes the query results, which feels like a bug to me. Are there any
workarounds? I found one for my repro below (using NOT) but for some of the
complicated tsquery queries that I have in production, I'm not sure I can
make that work.
CREATE TABLE examples (content text);
CREATE INDEX ts_idx ON examples USING gin(to_tsvector('simple', content));
INSERT INTO examples VALUES ('Example with a word');
/* Incorrectly returns no results */
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
SELECT * FROM examples
WHERE to_tsvector('simple', content) @@ to_tsquery('simple',
'!(example<->word)')
/* Correctly returns results */
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF; /* disabled */
SELECT * FROM examples
WHERE to_tsvector('simple', content) @@ to_tsquery('simple',
'!(example<->word)')
/* Also correctly returns results by using index and NOT keyword */
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON; /* enabled */
SELECT * FROM examples
WHERE NOT to_tsvector('simple', content) @@ to_tsquery('simple',
'(example<->word)')
Thanks for your time, and any thoughts that you might have to spare.
-Charlie
PG Bug reporting form <noreply@postgresql.org> writes:
When Postgres uses a bitmap heap scan to evaluate a tsquery that includes !,
it is giving me different (and incorrect) results compared to when it
performs a seqscan.
Can anybody shed some light on this?
It's a bug, without a doubt.
It looks to me like what is happening is that when gin_tsquery_consistent
asks TS_execute if the query is matched, we recurse down to
TS_phrase_execute which does this:
/*
* If either operand has no position information, then we can't
* return position data, only a "possible match" result. "Possible
* match" answers are only wanted when TS_EXEC_PHRASE_NO_POS flag
* is set, otherwise return false.
*/
if ((Ldata.npos == 0 && !Ldata.negate) ||
(Rdata.npos == 0 && !Rdata.negate))
return (flags & TS_EXEC_PHRASE_NO_POS) ? true : false;
so that returns "true" up to the calling TS_execute level:
case OP_NOT:
if (flags & TS_EXEC_CALC_NOT)
return !TS_execute(curitem + 1, arg, flags, chkcond);
else
return true;
which returns "false" and we conclude the index entry doesn't match.
AFAICS this is fundamentally broken and the only way to un-break it
is to introduce explicit three-valued logic, ie we have to return
"maybe" which OP_NOT mustn't invert. Various people have tried to
wiggle around that by inventing assorted more-or-less-bogus flags,
like the TS_EXEC_PHRASE_NO_POS flag seen above, but in the end it
Just Does Not Work to take shortcuts.
Depressingly, although tsginidx.c has decided to invent its
own version with three-valued logic (TS_execute_ternary),
that isn't being used in this scenario ... and it looks like
it'd get the case wrong if it were used, because it's got its
own set of bogosities.
I'll see about getting this fixed in time for next month's
minor releases, but it's definitely not a trivial change.
Thanks for the report!
regards, tom lane
I wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
When Postgres uses a bitmap heap scan to evaluate a tsquery that includes !,
it is giving me different (and incorrect) results compared to when it
performs a seqscan.
Can anybody shed some light on this?
It's a bug, without a doubt.
Here's a proposed patch that fixes it as I suggested yesterday, by
converting TS_execute and TS_phrase_execute to use honest ternary
results instead of trying to get by with bool. There's also a minor
bug fix in TS_execute_ternary; that doesn't show up with the one-row
example Charles posted, but it does show up with larger indexes.
I had worried that it might be necessary to change the exposed API
of TS_execute(), but in the event it seems we need not.
Possibly the most controversial aspect of this patch is that I changed
the test data file tsearch.data, by attaching lexeme position info
to a couple dozen rows. Without that, the test_tsvector test table is
next to useless for testing phrase search, since it has no rows with
position data. This does result in minor changes in one existing test
case for ts_stat(), but I see little wrong with that.
Also, the new test cases in tstypes.sql are mostly to ensure that
this patch didn't cause any changes in the behavior of phrase match;
they all pass on the existing code.
(Charles, if you want to test this locally to verify it fixes your
problem, the code changes should apply to v11; though there are a
couple of hunks in the regression test files that will not apply
because they change test stanzas that are new in HEAD.)
regards, tom lane