MIME-Version: 1.0
References: <a827cf68-608e-45cf-9b94-e46f5e9d4171@manitou-mail.org>
 <fdc379ce-4b88-4357-b622-2ae0ef08e295@eisentraut.org> <CA+renyWd-_sAj3YqBRaQVOOMr5uQoeBcA3tjCSyQFzvnbGrMYA@mail.gmail.com>
 <ba4dc9f5-fb14-433f-8d53-3dd291585161@eisentraut.org> <3104729e-1fbf-4368-ac21-1f670062de28@eisentraut.org>
 <CAOYmi+nqr4xCe9-g4BAupnu2rZmvLy1T3qq3ejOUWOCsoJ4ZdA@mail.gmail.com>
 <0ca761b5-7b62-42a1-bffd-8bedefad48dd@eisentraut.org> <bce1da9a-2975-462f-8946-c58c878ba82c@iki.fi>
 <62156cde-b2c5-4375-b47a-9730aa8f7b5e@eisentraut.org> <CACJufxFeOuBbkHfp=0-0rwamydjYY4ky1A+CPr6s3WUABC9_Rg@mail.gmail.com>
 <07fdbb85-c530-48d0-adc0-7d43d7951e1b@eisentraut.org> <CACJufxHVcgt6ybYLX+R6YYcK=Hc0ctTD_wFfJvrR37yrjYyrww@mail.gmail.com>
 <d57efd38-3db0-40b7-999c-d26bc9259616@eisentraut.org>
In-Reply-To: <d57efd38-3db0-40b7-999c-d26bc9259616@eisentraut.org>
From: jian he <jian.universality@gmail.com>
Date: Wed, 20 Nov 2024 15:29:22 +0800
Message-ID: <CACJufxGuBNQzx1LFBpEP01A1SuSndfzMWXHR9vr9bV3A6dB84g@mail.gmail.com>
Subject: Re: Support LIKE with nondeterministic collations
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, Jacob Champion <jacob.champion@enterprisedb.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>, Daniel Verite <daniel@manitou-mail.org>, 
	Paul A Jungwirth <pj@illuminatedcomputing.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxGuBNQzx1LFBpEP01A1SuSndfzMWXHR9vr9bV3A6dB84g%40mail.gmail.com>
Precedence: bulk

On Tue, Nov 19, 2024 at 9:51=E2=80=AFPM 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 =3D=3D '%')
> > {
> > while (tlen > 0)
> > {
> >      if (GETCHAR(*t, locale) =3D=3D firstpat || (locale && !locale->det=
erministic))
> >      {
> >          int            matched =3D MatchText(t, tlen, p, plen, locale)=
;
> >          if (matched !=3D 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.