MIME-Version: 1.0
References: <a827cf68-608e-45cf-9b94-e46f5e9d4171@manitou-mail.org> <b0ff0233-b9e2-48ef-ac27-f69bca73f8f4@eisentraut.org>
In-Reply-To: <b0ff0233-b9e2-48ef-ac27-f69bca73f8f4@eisentraut.org>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 2 May 2024 20:11:48 -0400
Message-ID: <CA+TgmoYntBJZnnZrRBXMgbXEXrp0Bm8yje4VAjE5LX6aXwj9_w@mail.gmail.com>
Subject: Re: Support LIKE with nondeterministic collations
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Daniel Verite <daniel@manitou-mail.org>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BTgmoYntBJZnnZrRBXMgbXEXrp0Bm8yje4VAjE5LX6aXwj9_w%40mail.gmail.com>
Precedence: bulk

On Thu, May 2, 2024 at 9:38=E2=80=AFAM Peter Eisentraut <peter@eisentraut.o=
rg> wrote:
> On 30.04.24 14:39, Daniel Verite wrote:
> >    postgres=3D# SELECT '.foo.' like '_oo' COLLATE ign_punct;
> >     ?column?
> >    ----------
> >     f
> >    (1 row)
> >
> > The first two results look fine, but the next one is inconsistent.
>
> This is correct, because '_' means "any single character".  This is
> independent of the collation.

Seems really counterintuitive. I had to think for a long time to be
able to guess what was happening here. Finally I came up with this
guess:

If the collation-aware matching tries to match up f with the initial
period, the period is skipped and the f matches f. But when the
wildcard is matched to the initial period, that uses up the wildcard
and then we're left trying to match o with f, which doesn't work.

Is that right?

It'd probably be good to use something like this as an example in the
documentation. My intuition is that if foo matches a string, then _oo
f_o and fo_ should also match that string. Apparently that's not the
case, but I doubt I'll be the last one who thinks it should be.

--=20
Robert Haas
EDB: http://www.enterprisedb.com