From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | James Lucas <jlucasdba(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Explicit deterministic COLLATE fails with pattern matching operations on column with non-deterministic collation |
Date: | 2020-05-27 15:53:04 |
Message-ID: | CAKFQuwZakFfXX3m5K5oMg9WJgQT6FffLRjYQzJQgTH1pGOOz5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Wed, May 27, 2020 at 8:23 AM James Lucas <jlucasdba(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | James Lucas | 2020-05-27 16:19:31 | Re: Explicit deterministic COLLATE fails with pattern matching operations on column with non-deterministic collation |
Previous Message | James Lucas | 2020-05-27 15:23:06 | Explicit deterministic COLLATE fails with pattern matching operations on column with non-deterministic collation |