Message-ID: <a17da3ebe17101ef330434237d2fef1cd1d0f30e.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: Wed, 08 Jan 2025 15:19:58 -0800
In-Reply-To: <898752524b1ad658c5fdae789f823a0dc52e6171.camel@j-davis.com>
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>
	 <898752524b1ad658c5fdae789f823a0dc52e6171.camel@j-davis.com>
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/a17da3ebe17101ef330434237d2fef1cd1d0f30e.camel%40j-davis.com>
Precedence: bulk

On Thu, 2024-12-19 at 09:51 -0800, Jeff Davis wrote:
> 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.
>=20
> 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?

The options as I see it are:

1. Normalize the output (either by using an extra parameter or just
always normalizing to NFC). As you said, the problem here is that the
encoding might not work with normalization. One solution might be that
CASEFOLD() only works in UTF8, like NORMALIZE().

2. Try to preserve normalization as long as the encoding supports it.
The problem here is that we don't know what normal form to preserve,
because the input string might be in both NFC and NFD. We could
document that it preserves NFC form iff the input is NFC.

3. Allow CASEFOLD() to break the normal form of the input string. The
problem here is that the user may be surprised that the output is not
normalized even when all of their data is normalized. It's not clear to
me whether it still works for caseless matching -- it might if the
string is in a consistent form, even if not normalized.

Out of those I think #1 is the most appealing. Most users, and
especially users that care about these edge cases enough to use Full
Case Folding, are almost certainly going to be on UTF8 anyway. It's
also the most user-friendly.

Regards,
	Jeff Davis