Re: Wrong results with equality search using trigram index and non-deterministic collation

On 17.09.2024 08:00, Laurenz Albe wrote:
> Using a trigram index with an non-deterministic collation can
> lead to wrong query results:
> 
>   CREATE COLLATION faux_cn (PROVIDER = icu, LOCALE = 'und', DETERMINISTIC = FALSE, RULES = '&l = r');
> 
>   CREATE TABLE boom (id integer PRIMARY KEY, t text COLLATE faux_cn);
> 
>   INSERT INTO boom VALUES (1, 'right'), (2, 'light');
> 
>   SELECT * FROM boom WHERE t = 'right';
> 
>    id │   t   
>   ════╪═══════
>     1 │ right
>     2 │ light
>   (2 rows)
> 
>   CREATE INDEX ON boom USING gin (t gin_trgm_ops);
> 
>   SET enable_seqscan = off;
> 
>   SELECT * FROM boom WHERE t = 'right';
> 
>    id │   t   
>   ════╪═══════
>     1 │ right
>   (1 row)
> 
> I also see questionable results with the similarity operator (with and
> without the index):
> 
>   SELECT * FROM boom WHERE t % 'rigor';
> 
>    id │   t   
>   ════╪═══════
>     1 │ right
>   (1 row)
> 
> But here you could argue that the operator ignores the collation, so
> the result is correct.  With equality, there is no such loophole.

I think we should change that. It's very counter intuitive that a query
can change behavior when the planner flips from using e.g. a Seq Scan to
a Bitmap Index Scan or the other way around. There's already a patch for
that, see [1].

> I don't know what the correct fix would be.  Perhaps just refusing to use
> the index for equality comparisons with non-deterministic collations.

If we merge [1], then not only = but also LIKE would be incorrect. How
about disabling CREATE INDEX USING gin on columns with non-deterministic
collations?

Or is there maybe a way to make these cases work correctly for
non-deterministic collations by applying the collation when extracting
the search trigrams? I take a look into that.

[1]
https://www.postgresql.org/message-id/flat/db087c3e-230e-4119-8a03-8b5d74956bc2%40gmail.com

--
David Geier