Message-ID: <e26ae6f202aff57a76e14cfb8ba2202550257f27.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: Wed, 18 Jun 2025 19:53:01 -0700
In-Reply-To: <692d28a2-d5f8-4db5-a3ad-c7db9bab522f@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>
	 <af6b575335ffeea36db3189fe7031adf67c250d2.camel@j-davis.com>
	 <692d28a2-d5f8-4db5-a3ad-c7db9bab522f@postgresfriends.org>
Content-Type: multipart/mixed; boundary="=-2QF67nnUwCl0vN9qpsfO"
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/e26ae6f202aff57a76e14cfb8ba2202550257f27.camel%40j-davis.com>
Precedence: bulk

--=-2QF67nnUwCl0vN9qpsfO
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 2025-06-18 at 19:09 +0200, Vik Fearing wrote:
> I don't know.=C2=A0 I am just pointing out what the Standard says.=C2=A0 =
I
> think=20
> we should either comply, or say that we don't do it for LOWER and
> UPPER=20
> so let's keep things implementation-consistent.

For the standard, I see two potential philosophies:

I. CASEFOLD() is another variant of LOWER()/UPPER(), and it should
preserve NFC in the same way.

II. CASEFOLD() is not like LOWER()/UPPER(); it returns a semi-opaque
text value that is useful for caseless matching, but should not
ordinarily be used for display or sent to the application (those things
would be allowed, just not encouraged). For normalization, either:
  (A) Follow Unicode Default Caseless Matching (16.0 3.13.5 D144), and
don't require any kind of normalization; or
  (B) Follow Unicode Canonical Caseless Matching (D145), and require
that the input and output are normalized appropriately, but leave the
precise normal form as implementation-defined.


The current implementation could either be seen as philosophy (I) where
we've chosen to ignore the normalization part for the sake of
consistency with LOWER()/UPPER(); or it could be seen as philosophy
(II)(A).

> How much does it cost to check for NFC?=C2=A0 I honestly don't know the=
=20
> answer to that question, but that is the only case where we need to=20
> maintain normalization.

I attached a very rough patch and ran a very simple test on strings
averaging 36 bytes in length, all already in NFC and the result is also
NFC. Before the patch, doing a CASEFOLD() on 10M tuples took about 3
seconds, afterward about 8.

There's a patch to optimize some of the normalization paths, which I
haven't had a chance to review yet. So those numbers might come down.=20

>=20
> It's not unconditionally, it's only if the input was NFC.

Optimizing the case where the input is _not_ NFC seems strange to me.
If we are normalizing the output, I'd say we should just make the
output always NFC.=C2=A0Being more strict, this seems likely to comply with
the eventual standard.

Additionally, if we are normalizing the output, then we should also do
the input fixup for U+0345, which would make the result usable for
Canonical Caseless Matching. Again, this seems likely to comply with
the eventual standard.

>=20

So I only see two reasonable implementations:

1. The current CASEFOLD() implementation.

2. Do the input fixup for U+0345 and unconditionally normalize the
output in NFC.

If there's a case to be made for both implementations, we could also
consider having two functions, say, CASEFOLD() for #1 and NCASEFOLD()
for #2. I'm not sure whether we'd want to standardize one or both of
those functions.

And if you think there's likely to be a collision with the standard
that's hard to anticipate and fix now, then we should consider
reverting CASEFOLD() for 18 and wait for more progress on the
standardization. What's the likelihood that the name changes or
something like that?

Regards,
	Jeff Davis


--=-2QF67nnUwCl0vN9qpsfO
Content-Disposition: attachment; filename="casefold_normalize.diff"
Content-Type: text/x-patch; name="casefold_normalize.diff"; charset="UTF-8"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9mb3JtYXR0aW5nLmMgYi9zcmMvYmFj
a2VuZC91dGlscy9hZHQvZm9ybWF0dGluZy5jCmluZGV4IDViZDFlMDFmN2U0Li4xMmU2ODhhY2Vj
NiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2Zvcm1hdHRpbmcuYworKysgYi9z
cmMvYmFja2VuZC91dGlscy9hZHQvZm9ybWF0dGluZy5jCkBAIC03OSw2ICs3OSw3IEBACiAjaW5j
bHVkZSAiY29tbW9uL2ludC5oIgogI2luY2x1ZGUgImNvbW1vbi91bmljb2RlX2Nhc2UuaCIKICNp
bmNsdWRlICJjb21tb24vdW5pY29kZV9jYXRlZ29yeS5oIgorI2luY2x1ZGUgImNvbW1vbi91bmlj
b2RlX25vcm0uaCIKICNpbmNsdWRlICJtYi9wZ193Y2hhci5oIgogI2luY2x1ZGUgIm5vZGVzL21p
c2Nub2Rlcy5oIgogI2luY2x1ZGUgInBhcnNlci9zY2Fuc3VwLmgiCkBAIC0xODY2LDYgKzE4Njcs
OSBAQCBzdHJfY2FzZWZvbGQoY29uc3QgY2hhciAqYnVmZiwgc2l6ZV90IG5ieXRlcywgT2lkIGNv
bGxpZCkKIAkJc2l6ZV90CQlkc3RzaXplOwogCQljaGFyCSAgICpkc3Q7CiAJCXNpemVfdAkJbmVl
ZGVkOworCQlpbnQgbWJsZW4sIGk7CisJCXVuc2lnbmVkIGNoYXIgKnA7CisJCXBnX3djaGFyICAg
KmRlY29kZWQ7CiAKIAkJLyogZmlyc3QgdHJ5IGJ1ZmZlciBvZiBlcXVhbCBzaXplIHBsdXMgdGVy
bWluYXRpbmcgTlVMICovCiAJCWRzdHNpemUgPSBzcmNsZW4gKyAxOwpAQCAtMTg4Miw3ICsxODg2
LDU0IEBAIHN0cl9jYXNlZm9sZChjb25zdCBjaGFyICpidWZmLCBzaXplX3QgbmJ5dGVzLCBPaWQg
Y29sbGlkKQogCQl9CiAKIAkJQXNzZXJ0KGRzdFtuZWVkZWRdID09ICdcMCcpOwotCQlyZXN1bHQg
PSBkc3Q7CisKKwkJLyogY29udmVydCB0byBwZ193Y2hhciAqLworCQltYmxlbiA9IHBnX21ic3Ry
bGVuX3dpdGhfbGVuKGRzdCwgbmVlZGVkKTsKKwkJZGVjb2RlZCA9IHBhbGxvYygobWJsZW4gKyAx
KSAqIHNpemVvZihwZ193Y2hhcikpOworCQlwID0gKHVuc2lnbmVkIGNoYXIgKikgZHN0OworCQlm
b3IgKGkgPSAwOyBpIDwgbWJsZW47IGkrKykKKwkJeworCQkJZGVjb2RlZFtpXSA9IHV0ZjhfdG9f
dW5pY29kZShwKTsKKwkJCXAgKz0gcGdfdXRmX21ibGVuKHApOworCQl9CisJCWRlY29kZWRbaV0g
PSAocGdfd2NoYXIpICdcMCc7CisKKwkJaWYgKHVuaWNvZGVfaXNfbm9ybWFsaXplZF9xdWlja2No
ZWNrKFVOSUNPREVfTkZDLCBkZWNvZGVkKSA9PSBVTklDT0RFX05PUk1fUUNfWUVTKQorCQl7CisJ
CQlwZnJlZShkZWNvZGVkKTsKKwkJCXJlc3VsdCA9IGRzdDsKKwkJfQorCQllbHNlCisJCXsKKwkJ
CXBnX3djaGFyICpub3JtYWxpemVkOworCQkJdW5zaWduZWQgY2hhciAqbm9ybWFsaXplZF91dGY4
OworCisJCQlub3JtYWxpemVkID0gdW5pY29kZV9ub3JtYWxpemUoVU5JQ09ERV9ORkMsIGRlY29k
ZWQpOworCQkJcGZyZWUoZGVjb2RlZCk7CisKKwkJCS8qIGNvbnZlcnQgYmFjayB0byBVVEYtOCBz
dHJpbmcgKi8KKwkJCW1ibGVuID0gMDsKKwkJCWZvciAocGdfd2NoYXIgKndwID0gbm9ybWFsaXpl
ZDsgKndwOyB3cCsrKQorCQkJeworCQkJCXVuc2lnbmVkIGNoYXIgYnVmWzRdOworCisJCQkJdW5p
Y29kZV90b191dGY4KCp3cCwgYnVmKTsKKwkJCQltYmxlbiArPSBwZ191dGZfbWJsZW4oYnVmKTsK
KwkJCX0KKworCQkJbm9ybWFsaXplZF91dGY4ID0gcGFsbG9jKG1ibGVuICsgMSk7CisKKwkJCXAg
PSBub3JtYWxpemVkX3V0Zjg7CisJCQlmb3IgKHBnX3djaGFyICp3cCA9IG5vcm1hbGl6ZWQ7ICp3
cDsgd3ArKykKKwkJCXsKKwkJCQl1bmljb2RlX3RvX3V0ZjgoKndwLCBwKTsKKwkJCQlwICs9IHBn
X3V0Zl9tYmxlbihwKTsKKwkJCX0KKwkJCSpwID0gJ1wwJzsKKwkJCXBmcmVlKG5vcm1hbGl6ZWQp
OworCisJCQlyZXN1bHQgPSAoY2hhciAqKSBub3JtYWxpemVkX3V0Zjg7CisJCX0KIAl9CiAKIAly
ZXR1cm4gcmVzdWx0Owo=


--=-2QF67nnUwCl0vN9qpsfO--