Re: Support LIKE with nondeterministic collations

On 03.05.24 02:11, Robert Haas wrote:
> On Thu, May 2, 2024 at 9:38 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>> On 30.04.24 14:39, Daniel Verite wrote:
>>> postgres=# 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.

Formally, what

X like '_oo'

means is, can X be partitioned into substrings such that the first
substring is a single character and the second substring is equal to
'oo' under the applicable collation? This is false in this case, there
is no such partitioning.

What the implementation does is, it walks through the pattern. It sees
'_', so it steps over one character in the input string, which is '.'
here. Then we have 'foo.' left to match in the input string. Then it
takes from the pattern the next substring up to but not including either
a wildcard character or the end of the string, which is 'oo', and then
it checks if a prefix of the remaining input string can be found that is
"equal to" 'oo'. So here it would try in turn

'' = 'oo' collate ign_punct ?
'f' = 'oo' collate ign_punct ?
'fo' = 'oo' collate ign_punct ?
'foo' = 'oo' collate ign_punct ?
'foo.' = 'oo' collate ign_punct ?

and they all fail, so the match fails.

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

This intuition fails because with nondeterministic collations, strings
of different lengths can be equal, and so the question arises, what does
the pattern '_' mean. It could mean either, (1) a single character, or
perhaps something like, (2) a string that is equal to some other string
of length one.

The second definition would satisfy the expectation here, because then
'.f' matches '_' because '.f' is equal to some string of length one,
such as 'f'. (And then 'oo.' matches 'oo' for the rest of the pattern.)
However, off the top of my head, this definition has three flaws: (1)
It would make the single-character wildcard effectively an
any-number-of-characters wildcard, but only in some circumstances, which
could be confusing, (2) it would be difficult to compute, because you'd
have to check equality against all possible single-character strings,
and (3) it is not what the SQL standard says.

In any case, yes, some explanation and examples should be added.