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>
In-Reply-To: <07fdbb85-c530-48d0-adc0-7d43d7951e1b@eisentraut.org>
From: jian he <jian.universality@gmail.com>
Date: Mon, 18 Nov 2024 11:30:20 +0800
Message-ID: <CACJufxHVcgt6ybYLX+R6YYcK=Hc0ctTD_wFfJvrR37yrjYyrww@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: multipart/mixed; boundary="000000000000f3befb0627278962"
Archived-At: <https://www.postgresql.org/message-id/CACJufxHVcgt6ybYLX%2BR6YYcK%3DHc0ctTD_wFfJvrR37yrjYyrww%40mail.gmail.com>
Precedence: bulk

--000000000000f3befb0627278962
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Nov 15, 2024 at 11:42=E2=80=AFPM Peter Eisentraut <peter@eisentraut=
.org> wrote:
>
> On 15.11.24 05:26, jian he wrote:
> > /*
> > * 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?
>
> How would you do that?  You need to try all combinations to find one
> that matches.
>

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->determin=
istic))
    {
        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.
> > 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;
>
> This results in
>
> ERROR:  42P22: could not determine which collation to use for LIKE
> HINT:  Use the COLLATE clause to set the collation explicitly.
>
> which is the expected behavior.
>
sorry, didn't mention it clearly, i mean we can add it to the regress test.

--000000000000f3befb0627278962
Content-Type: application/octet-stream; 
	name="v8-0001-LIKE-with-nondeterministic-collations-no-trail.no-cfbot"
Content-Disposition: attachment; 
	filename="v8-0001-LIKE-with-nondeterministic-collations-no-trail.no-cfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_m3mgvtb30>
X-Attachment-Id: f_m3mgvtb30

RnJvbSAzZjgyYWQ0MzZkZWFmODEyMThkYzRiNDg5OGY2ZDZlZGIzMDA1Yzc4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IE1vbiwgMTggTm92IDIwMjQgMTE6MDY6MTcgKzA4MDAKU3ViamVjdDogW1BBVENIIHY4
IDEvMV0gTElLRSB3aXRoIG5vbmRldGVybWluaXN0aWMgY29sbGF0aW9ucyBubyB0cmFpbGluZyB3
aWxkCiBjYXJkcwoKb3B0aW1pemUgd2hlcmUgdGhlcmUgaXMgbm8gdHJhaWxpbmcgd2lsZGNhcmRz
LgotLS0KIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9saWtlX21hdGNoLmMgfCAzOCArKysrKysrKysr
KysrKysrKysrKysrKysrKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCAzNiBpbnNlcnRpb25zKCspLCAy
IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9saWtlX21h
dGNoLmMgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvbGlrZV9tYXRjaC5jCmluZGV4IGYzY2E0ZDMw
ODIuLmRkMWIxMzYyNTUgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9saWtlX21h
dGNoLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2xpa2VfbWF0Y2guYwpAQCAtMjEyLDYg
KzIxMiw3IEBAIE1hdGNoVGV4dChjb25zdCBjaGFyICp0LCBpbnQgdGxlbiwgY29uc3QgY2hhciAq
cCwgaW50IHBsZW4sIHBnX2xvY2FsZV90IGxvY2FsZSkKIAkJCWNvbnN0IGNoYXIgKnQxOwogCQkJ
c2l6ZV90CQl0MWxlbjsKIAkJCWJvb2wJCWZvdW5kX2VzY2FwZTsKKwkJCWJvb2wJCXRyYWlsaW5n
X3dpbGRjYXJkcyA9IGZhbHNlOwogCQkJY29uc3QgY2hhciAqc3VicGF0OwogCQkJc2l6ZV90CQlz
dWJwYXRsZW47CiAJCQljaGFyCSAgICpidWYgPSBOVUxMOwpAQCAtMjc5LDcgKzI4MCwzMyBAQCBN
YXRjaFRleHQoY29uc3QgY2hhciAqdCwgaW50IHRsZW4sIGNvbnN0IGNoYXIgKnAsIGludCBwbGVu
LCBwZ19sb2NhbGVfdCBsb2NhbGUpCiAKIAkJCQlDSEVDS19GT1JfSU5URVJSVVBUUygpOwogCi0J
CQkJY21wID0gcGdfc3RybmNvbGwoc3VicGF0LCBzdWJwYXRsZW4sIHQsICh0MSAtIHQpLCBsb2Nh
bGUpOworCQkJCWlmICghdHJhaWxpbmdfd2lsZGNhcmRzKQorCQkJCXsKKwkJCQkJLyogd2UgY2hl
Y2sgc3VicGF0bGVuIGFuZCBuZXh0IGNoYXJhY3RlciBpcyB3aWxkLWNhcmQgb3IKKwkJCQkJICog
bm90LiBpZiBzdWJwYXRsZW4gPT0gMSwgdGhlbiB3ZSBkb24ndCBoYXZlIHRvIGNoZWNrIHRoZQor
CQkJCQkgKiBuZXh0IGNoYXIuIGlmIHdlIGRpZG4ndCBmb3VuZCB0cmFpbGluZ193aWxkY2FyZHMg
dGhlbiB3ZSBjYW4KKwkJCQkJICogc2FmZWx5IGNvbXBhcmUgd2hvbGUgc3VicGF0IHdpdGggdC4g
aWYgY21wICE9IDAgd2UgcmV0dXJuCisJCQkJCSAqIExJS0VfRkFMU0UuCisJCQkJCSovCisJCQkJ
CWlmIChzdWJwYXRsZW4gPT0gMSAmJiAoc3VicGF0WzBdID09ICclJyB8fCBzdWJwYXRbMF0gPT0g
J18nKSkKKwkJCQkJeworCQkJCQkJdHJhaWxpbmdfd2lsZGNhcmRzID0gdHJ1ZTsKKwkJCQkJCWJy
ZWFrOworCQkJCQl9CisJCQkJCWZvciAoaW50IGkgPSAwOyBpIDw9c3VicGF0bGVuOyBpKyspCisJ
CQkJCXsKKwkJCQkJCWlmIChzdWJwYXRbaV0gPT0gJyUnIHx8IHN1YnBhdFtpXSA9PSAnXycpCisJ
CQkJCQl7CisJCQkJCQkJdHJhaWxpbmdfd2lsZGNhcmRzID0gdHJ1ZTsKKwkJCQkJCQlicmVhazsK
KwkJCQkJCX0KKwkJCQkJfQorCQkJCX0KKworCQkJCWlmICghdHJhaWxpbmdfd2lsZGNhcmRzKQor
CQkJCQljbXAgPSBwZ19zdHJuY29sbChzdWJwYXQsIHN1YnBhdGxlbiwgdCwgdGxlbiwgbG9jYWxl
KTsKKwkJCQllbHNlCisJCQkJCWNtcCA9IHBnX3N0cm5jb2xsKHN1YnBhdCwgc3VicGF0bGVuLCB0
LCAodDEgLSB0KSwgbG9jYWxlKTsKIAogCQkJCS8qCiAJCQkJICogSWYgd2UgZm91bmQgYSBtYXRj
aCwgd2UgaGF2ZSB0byB0ZXN0IGlmIHRoZSByZXN0IG9mIHBhdHRlcm4KQEAgLTI5OSw3ICszMjYs
MTIgQEAgTWF0Y2hUZXh0KGNvbnN0IGNoYXIgKnQsIGludCB0bGVuLCBjb25zdCBjaGFyICpwLCBp
bnQgcGxlbiwgcGdfbG9jYWxlX3QgbG9jYWxlKQogCQkJCSAqLwogCQkJCWlmIChjbXAgPT0gMCkK
IAkJCQl7Ci0JCQkJCWludAkJCW1hdGNoZWQgPSBNYXRjaFRleHQodDEsIHQxbGVuLCBwMSwgcDFs
ZW4sIGxvY2FsZSk7CisJCQkJCWludAkJCW1hdGNoZWQ7CisKKwkJCQkJaWYgKCF0cmFpbGluZ193
aWxkY2FyZHMpCisJCQkJCQlyZXR1cm4gTElLRV9UUlVFOworCisJCQkJCW1hdGNoZWQgPSBNYXRj
aFRleHQodDEsIHQxbGVuLCBwMSwgcDFsZW4sIGxvY2FsZSk7CiAKIAkJCQkJaWYgKG1hdGNoZWQg
PT0gTElLRV9UUlVFKQogCQkJCQl7CkBAIC0zMDgsNiArMzQwLDggQEAgTWF0Y2hUZXh0KGNvbnN0
IGNoYXIgKnQsIGludCB0bGVuLCBjb25zdCBjaGFyICpwLCBpbnQgcGxlbiwgcGdfbG9jYWxlX3Qg
bG9jYWxlKQogCQkJCQkJcmV0dXJuIG1hdGNoZWQ7CiAJCQkJCX0KIAkJCQl9CisJCQkJZWxzZSBp
ZiAoIXRyYWlsaW5nX3dpbGRjYXJkcykKKwkJCQkJcmV0dXJuIExJS0VfRkFMU0U7CiAKIAkJCQkv
KgogCQkJCSAqIERpZG4ndCBtYXRjaC4gIElmIHdlIHVzZWQgdXAgdGhlIHdob2xlIHRleHQsIHRo
ZW4gdGhlIG1hdGNoCi0tIAoyLjM0LjEKCg==
--000000000000f3befb0627278962--