Re: Support LIKE with nondeterministic collations - Mailing list pgsql-hackers

On Tue, Nov 19, 2024 at 9:51 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 18.11.24 04:30, jian he wrote:
> > we can optimize when trailing (last character) is not  wildcards.
> >
> > SELECT 'Ha12foo' LIKE '%foo' COLLATE ignore_accents;
> > within the for loop
> > for(;;)
> > {
> > int            cmp;
> > CHECK_FOR_INTERRUPTS();
> > ....
> > }
> >
> > pg_strncoll comparison will become
> > Ha12foo    foo
> > a12foo      foo
> > 12foo        foo
> > 2foo          foo
> > foo            foo
> >
> > it's safe because in MatchText we have:
> > else if (*p == '%')
> > {
> > while (tlen > 0)
> > {
> >      if (GETCHAR(*t, locale) == firstpat || (locale && !locale->deterministic))
> >      {
> >          int            matched = MatchText(t, tlen, p, plen, locale);
> >          if (matched != LIKE_FALSE)
> >              return matched; /* TRUE or ABORT */
> >      }
> >      NextChar(t, tlen);
> > }
> > }
> >
> > please check attached.
>
> I see, good idea.  I implemented it a bit differently.  See "Shortcut:
> If this is the end of the pattern ..." in this patch.  Please check if
> this is what you had in mind.

your implementation is far more simpler than mine.
I think I understand it.

i am trying to optimize case where pattern is begin_with like `pattern%`
but failed on case like:
SELECT U&'\0061\0308bc' LIKE U&'\00E4bc%' COLLATE ignore_accents;
basically the_string like the_pattern%. the length of the_string  and
length of the_pattern
can vary, we can not just do one pg_strncoll.


in match_pattern_prefix maybe change
    if (expr_coll && !get_collation_isdeterministic(expr_coll))
        return NIL;
to
    if (OidIsValid(expr_coll) && !get_collation_isdeterministic(expr_coll))
        return NIL;

other than that, I didn't find any issue.