Feedback-ID: ie0a040ee:Fastmail
Message-ID: <07fdbb85-c530-48d0-adc0-7d43d7951e1b@eisentraut.org>
Date: Fri, 15 Nov 2024 16:42:31 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Support LIKE with nondeterministic collations
To: jian he <jian.universality@gmail.com>
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>
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>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <CACJufxFeOuBbkHfp=0-0rwamydjYY4ky1A+CPr6s3WUABC9_Rg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/07fdbb85-c530-48d0-adc0-7d43d7951e1b%40eisentraut.org>
Precedence: bulk

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 = t
> t1len = tlen;
> for (;;)
> {
> int cmp;
> CHECK_FOR_INTERRUPTS();
> cmp = 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.

> 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.

> 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.

Ok, I agree that could be clarified.