Message-ID: <898752524b1ad658c5fdae789f823a0dc52e6171.camel@j-davis.com>
Subject: Re: Add CASEFOLD() function.
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Eisentraut <peter@eisentraut.org>, Joe Conway
 <mail@joeconway.com>,  Ian Lawrence Barwick <barwick@gmail.com>
Cc: pgsql-hackers@postgresql.org
Date: Thu, 19 Dec 2024 09:51:32 -0800
In-Reply-To: <8c384b0d-00f2-4515-8e60-ff7d0d4c093a@eisentraut.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>
	 <8c384b0d-00f2-4515-8e60-ff7d0d4c093a@eisentraut.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/898752524b1ad658c5fdae789f823a0dc52e6171.camel%40j-davis.com>
Precedence: bulk

On Thu, 2024-12-19 at 17:18 +0100, Peter Eisentraut wrote:
> Can you explain this in further detail?=C2=A0 I don't quite follow why
> this=20
> would be required.

I am unsure now.

My initial reasoning was based on the idea that users would want to use
CASEFOLD(t) in a unique expression index as an improvement over
LOWER(t). And if you do that, you'd be surprised if some equivalent
strings ended up in the index.=C2=A0I don't think that's a huge problem,
because in other contexts we leave it up to the user to keep things
normalized consistently, and a CHECK(t IS NFC NORMALIZED) is a good way
to do that.

But there's a problem: full case folding doesn't preserve the normal
form, so even if the input is NFC normalized, the output might not be.
If we solve this problem, then we can just say that CASEFOLD()
preserves the normal form, consistently with how the spec defines
LOWER()/UPPER(), and I think that would be the best outcome.

I'm not sure if that problem is solvable, though, because what if the
input string is in both NFC and NFD, how do we know which normal form
to preserve?

We could tell users to use an expression index on
NORMALIZE(CASEFOLD(t)) instead, but that feels like inefficient
boilerplate.

>=20
> Another might be that's not entirely clear how this should work in=20
> encodings other than UTF-8.=C2=A0 For example, the normalized string migh=
t
> not be representable in the encoding.

That's a good point.

Regards,
	Jeff Davis