Postgres Pro
Downloads
Home   >   mailing lists

Re: Explicit deterministic COLLATE fails with pattern matchingoperations on column with non-deterministic collation - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: Explicit deterministic COLLATE fails with pattern matchingoperations on column with non-deterministic collation
Date
Msg-id CAKFQuwZakFfXX3m5K5oMg9WJgQT6FffLRjYQzJQgTH1pGOOz5Q@mail.gmail.com
Whole thread Raw
In response to Explicit deterministic COLLATE fails with pattern matching operationson column with non-deterministic collation  (James Lucas <jlucasdba@gmail.com>)
Responses Re: Explicit deterministic COLLATE fails with pattern matchingoperations on column with non-deterministic collation  (James Lucas <jlucasdba@gmail.com>)
List pgsql-bugs
Tree view
On Wed, May 27, 2020 at 8:23 AM James Lucas <jlucasdba@gmail.com> wrote:

create table ctestnd (id numeric, t text collate mycollation);

create index ctestnd_idx02 on ctestnd (t collate "C");
 
Test on ctestnd:
explain select * from ctestnd where t = 'aAa' collate "C";
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using ctestnd_idx02 on ctestnd  (cost=0.42..4.44 rows=1 width=10)
   Index Cond: (t = 'aAa'::text COLLATE "C")
COMMENT: Works as expected.

Uses an index scan which is where the deterministic collation exists
 

explain select * from ctestnd where t like 'a%';
ERROR:  nondeterministic collations are not supported for LIKE
COMMENT: Fails as expected.

explain select * from ctestnd where t like 'a%' collate "C";
ERROR:  nondeterministic collations are not supported for LIKE


Your schema is inherently unstable in this respect because the planner has to be allowed to choose a sequential scan and as soon as it does it attempts to perform like comparisons with table data that is stored using a non-deterministic collation.

I don't know what kinds of promises we make about implicit collation manipulation here but absent such a transformation the sequential scan plan with LIKE generates an invalid plan choice.  That it doesn't go find the index that happens to have a workable collation for the query is unsurprising - whether that is even a possibility is beyond me.

David J.

pgsql-bugs by date:

Previous
From: James Lucas
Date:
Subject: Explicit deterministic COLLATE fails with pattern matching operationson column with non-deterministic collation
Next
From: James Lucas
Date:
Subject: Re: Explicit deterministic COLLATE fails with pattern matchingoperations on column with non-deterministic collation