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>
In-Reply-To: <62156cde-b2c5-4375-b47a-9730aa8f7b5e@eisentraut.org>
From: jian he <jian.universality@gmail.com>
Date: Fri, 15 Nov 2024 12:26:24 +0800
Message-ID: <CACJufxFeOuBbkHfp=0-0rwamydjYY4ky1A+CPr6s3WUABC9_Rg@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/CACJufxFeOuBbkHfp%3D0-0rwamydjYY4ky1A%2BCPr6s3WUABC9_Rg%40mail.gmail.com>
Precedence: bulk

On Tue, Nov 12, 2024 at 3:45=E2=80=AFPM Peter Eisentraut <peter@eisentraut.=
org> wrote:
>
> On 11.11.24 14:25, Heikki Linnakangas wrote:
> > Sadly the algorithm is O(n^2) with non-deterministic collations.Is ther=
e
> > any way this could be optimized? We make no claims on how expensive any
> > functions or operators are, so I suppose a slow implementation is
> > nevertheless better than throwing an error.
>
> Yeah, maybe someone comes up with new ideas in the future.
>

/*
* Now build a substring of the text and try to match it against
* the subpattern.  t is the start of the text, t1 is one past the
* last byte.  We start with a zero-length string.
*/
t1 =3D t
t1len =3D tlen;
for (;;)
{
int cmp;
CHECK_FOR_INTERRUPTS();
cmp =3D pg_strncoll(subpat, subpatlen, t, (t1 - t), locale);

select '.foo.' LIKE '_oo' COLLATE ign_punct;
pg_strncoll's iteration of the first 4 argument values.
oo      2       foo. 0
oo      2       foo. 1
oo      2       foo. 2
oo      2       foo. 3
oo      2       foo. 4

seems there is a shortcut/optimization.
if subpat don't have wildcard(percent sign, underscore)
then we can have less pg_strncoll calls?


minimum case to trigger error within GenericMatchText
since no related tests.
create table t1(a text collate case_insensitive, b text collate "C");
insert into t1 values ('a','a');
select a like b from t1;


at 9.7.1. LIKE  section, we still don't know what "wildcard" is.
we mentioned it at 9.7.2.
maybe we can add a sentence at the end of:
    <para>
     If <replaceable>pattern</replaceable> does not contain percent
     signs or underscores, then the pattern only represents the string
     itself; in that case <function>LIKE</function> acts like the
     equals operator.  An underscore (<literal>_</literal>) in
     <replaceable>pattern</replaceable> stands for (matches) any single
     character; a percent sign (<literal>%</literal>) matches any sequence
     of zero or more characters.
    </para>

saying underscore and percent sign are wildcards in LIKE.
other than that, I can understand the doc.