Message-ID: <af6b575335ffeea36db3189fe7031adf67c250d2.camel@j-davis.com>
Subject: Re: Add CASEFOLD() function.
From: Jeff Davis <pgsql@j-davis.com>
To: Vik Fearing <vik@postgresfriends.org>, Joe Conway <mail@joeconway.com>,
 Ian Lawrence Barwick
	 <barwick@gmail.com>
Cc: pgsql-hackers@postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 17 Jun 2025 11:14:58 -0700
In-Reply-To: <36f69fa9-db3f-44a1-8c8a-95ff09704a9c@postgresfriends.org>
References: <a1886ddfcd8f60cb3e905c93009b646b4cfb74c5.camel@j-davis.com>
	 <CAB8KJ=jgnpbDTJS+jOQMzo7b1mPTwM0hCqQBBRgoX_kvyxWVog@mail.gmail.com>
	 <ab1bdabcf64d799692ddaa5b06b5ef8afe94a0ef.camel@j-davis.com>
	 <47716a78-eb0b-4728-8991-fc0d958a01c9@joeconway.com>
	 <c3801da038f8f59ee63deb9104af1147d49454c8.camel@j-davis.com>
	 <36f69fa9-db3f-44a1-8c8a-95ff09704a9c@postgresfriends.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/af6b575335ffeea36db3189fe7031adf67c250d2.camel%40j-davis.com>
Precedence: bulk

On Tue, 2025-06-17 at 17:37 +0200, Vik Fearing wrote:
> If the character set of <character factor> is UTF8, UTF16, or UTF32,
> then FR is replaced by
> =C2=A0=C2=A0=C2=A0=C2=A0 Case:
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 i) If the <search condit=
ion> S IS NORMALIZED evaluates to
> True, then NORMALIZE (FR)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ii) Otherwise, FR.

I read that as "if the input is normalized, then the output should be
normalized", IOW preserve the normalization. But does it mean "preserve
whatever the input normal form is" or "preserve NFC if the input is
NFC, otherwise the normalization is undefined"?

The above wording seems to mean "preserve NFC if the input is NFC",
because that's what NORMALIZE(FR) does when the normal form is
unspecified.

> It does not appear to me that our LOWER and UPPER functions obey this
> rule,

You are correct:

   WITH s(t) AS
   (SELECT NORMALIZE(U&'\00C1\00DF\0301' COLLATE "en-US-x-icu"))
   SELECT UPPER(t) =3D NORMALIZE(UPPER(t)) FROM s;
    ?column?=20
   ----------
    f

>  so there is a valid argument that we should continue to ignore it.
> Or, we can say that we have at least one of three compliant.

What do other databases do?

Given how costly normalization can be, imposing that on every caller
seems like a bit much. And favoring NFC for the user unconditionally
might not be the best thing. Then again, NFC is good most of the time,
and there are patches to speed up normalization.

I tend to think that a lot of users who want casefolding would also
want normalization, but it's hard to weigh that against the performance
cost. It might not matter outside of a few edge cases, though I'm not
sure exactly how many.

Regards,
	Jeff Davis