MIME-Version: 1.0
References: <1791665551.452444.1761209220211.ref@mail.yahoo.com>
 <aa24964a-f0d7-435e-9254-bb69823948d6@gmail.com>
 <CAJ7c6TNCGT2eZ8JttAZKUhVv3jJFTHvjFw6typzSGkM5_vePow@mail.gmail.com>
 <CAD21AoCcikUgU5coyvM7PLJg5M-75LzQhm-Uxmqn_7M=DHkj+w@mail.gmail.com>
 <CAJ7c6TPu7NGrSj-V2-ns0GTNGx9cmoKQgzt-p2MpJepiGjQnYw@mail.gmail.com>
 <CAD21AoC2Ooy=W=4YjQ_GO5nEYZ2tsn43=TkLf7ysE-96ygohKg@mail.gmail.com>
 <878qbn51kb.fsf@wibble.ilmari.org>
 <CAD21AoA7UMWG8YO2Edf1UWSdtaof6gOnF98bJGoz1z1JBzozuQ@mail.gmail.com>
 <CAJ7c6TMd4ebu7oha2C9QZ_aKNE5-keEJ=bE_k_-vrMuw2p1LNg@mail.gmail.com>
 <CAJ7c6TNzQES3EhzoftNUEg=mfi69SYEQ=Fh-=rr84b7zHECHeg@mail.gmail.com>
 <CAD21AoAj+KOc74wOr04oGo9ySYmf_TYkMi5fByLF80cqyZmB=g@mail.gmail.com>
 <1F13AF7E-B90A-40FB-B47C-DC38FBB08353@gmail.com>
 <CAJ7c6TN7fjWDWSVmhQuNCPYLVL9cs9ifc4D_Hj6wUQd2Lw2oNQ@mail.gmail.com>
 <CAD21AoAMST1V1OE=yvg3vQn=p6DDJBAaaFDx39z=P-+Z9iXwOQ@mail.gmail.com>
 <01aac5bb-63b1-420f-b396-fdc2027fbb50@vondra.me>
 <688741.1774485357@sss.pgh.pa.us>
 <CAD21AoAwX1D6baSGuQXm0mzPXPWB07kgaoaaahjNHHenbdY24A@mail.gmail.com>
In-Reply-To: 
 <CAD21AoAwX1D6baSGuQXm0mzPXPWB07kgaoaaahjNHHenbdY24A@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 25 Mar 2026 18:21:38 -0700
Message-ID: 
 <CAD21AoC=92zAEmaGt+3U1jSD77jgdQT_HXUwPPtZEze2sJ=vWA@mail.gmail.com>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in
 functions
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tomas Vondra <tomas@vondra.me>,
 Aleksander Alekseev <aleksander@tigerdata.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
 Chao Li <li.evan.chao@gmail.com>,
	=?UTF-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org>,
	Andrey Borodin <x4mmm@yandex-team.ru>
Content-Type: multipart/mixed; boundary="000000000000f3b9cd064de33668"
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoC%3D92zAEmaGt%2B3U1jSD77jgdQT_HXUwPPtZEze2sJ%3DvWA%40mail.gmail.com>
Precedence: bulk

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

On Wed, Mar 25, 2026 at 6:09=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail.=
com> wrote:
>
> On Wed, Mar 25, 2026 at 5:35=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrot=
e:
> >
> > Tomas Vondra <tomas@vondra.me> writes:
> > > On 3/26/26 00:40, Tom Lane wrote:
> > >> I believe what's happening there is that in cs_CZ locale,
> > >> "V" doesn't follow simple ASCII sort ordering.
> >
> > > With cs_CZ all letters sort *before* numbers, while in en_US it's the
> > > other way around. V is not special in any way.
> >
> > Ah, sorry, I should have researched a bit instead of relying on
> > fading memory.  The quirk I was thinking of is that in cs_CZ,
> > "ch" sorts after "h":
> >
> > u8=3D# select 'h' < 'ch'::text collate "en_US";
> >  ?column?
> > ----------
> >  f
> > (1 row)
> >
> > u8=3D# select 'h' < 'ch'::text collate "cs_CZ";
> >  ?column?
> > ----------
> >  t
> > (1 row)
> >
> > Regular hex encoding isn't bitten by that because it doesn't
> > use 'h' in the text form ... but this base32hex thingie does.
> >
> > However, your point is also correct:
> >
> > u8=3D# select '0' < 'C'::text ;
> >  ?column?
> > ----------
> >  t
> > (1 row)
> >
> > u8=3D# select '0' < 'C'::text collate "cs_CZ";
> >  ?column?
> > ----------
> >  f
> > (1 row)
> >
> > and that breaks "text ordering matches numeric ordering"
> > for both traditional hex and base32hex.  So maybe this
> > is not as big a deal as I first thought.  We need a fix
> > for the new test though.  Probably adding COLLATE "C"
> > would be enough.
>
> Thank you for the report and the analysis.
>
> I've reproduced the issue with "cs_CZ" collation and adding COLLATE
> "C" to the query resolves it. It seems also a good idea to add a note
> in the documentation too as users might face the same issue. For
> example,
>
> To maintain the lexicographical sort order of the encoded data, ensure
> that the text is sorted using the C collation (e.g., using COLLATE
> "C"). Natural language collations may sort characters differently and
> break the ordering.
>

Attached the patch doing the above idea.

Regards,

--=20
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

--000000000000f3b9cd064de33668
Content-Type: text/x-patch; charset="US-ASCII";
	name="0001-Fix-UUID-sortability-tests-in-base32hex-encoding.patch"
Content-Disposition: attachment;
	filename="0001-Fix-UUID-sortability-tests-in-base32hex-encoding.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mn6scnjo0>
X-Attachment-Id: f_mn6scnjo0

RnJvbSBhNjRmM2Y2NGE5ZjA0YzFmNWRhOWE1MWZlNzYwYzQwNDgwNTg1ZmQ0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXNhaGlrbyBTYXdhZGEgPHNhd2FkYS5tc2hrQGdtYWlsLmNv
bT4KRGF0ZTogV2VkLCAyNSBNYXIgMjAyNiAxODowNzoxMSAtMDcwMApTdWJqZWN0OiBbUEFUQ0hd
IEZpeCBVVUlEIHNvcnRhYmlsaXR5IHRlc3RzIGluIGJhc2UzMmhleCBlbmNvZGluZy4KClRoZSBy
ZWNlbnRseSBhZGRlZCB0ZXN0IGZvciBiYXNlMzJoZXggZW5jb2Rpbmcgb2YgVVVJRHMgZmFpbGVk
IG9uCmJ1aWxkZmFybSBtZW1iZXIgaGlwcG9wb3RhbXVzIHVzaW5nIG5hdHVyYWwgbGFuZ3VhZ2Ug
bG9jYWxlcyAoc3VjaCBhcwpjc19DWikuIFRoaXMgaGFwcGVuZWQgYmVjYXVzZSB0aG9zZSBjb2xs
YXRpb25zIG1heSBzb3J0IGNoYXJhY3RlcnMKZGlmZmVyZW50bHksIHdoaWNoIGJyZWFrcyB0aGUg
c3RyaWN0IGJ5dGUtd2lzZSBsZXhpY29ncmFwaGljYWwKb3JkZXJpbmcgZXhwZWN0ZWQgYnkgYmFz
ZTMyaGV4IGVuY29kaW5nLgoKVGhpcyBjb21taXQgZml4ZXMgdGhlIHJlZ3Jlc3Npb24gdGVzdHMg
YnkgZXhwbGljaXRseSB1c2luZyB0aGUgQwpjb2xsYXRpb24uIEFkZGl0aW9uYWxseSwgYWRkIGEg
bm90ZSB0byB0aGUgZG9jdW1lbnRhdGlvbiB0byB3YXJtIHVzZXJzCnRoYXQgdGhleSBtdXN0IHVz
ZSB0aGUgQyBjb2xsYXRpb24gaWYgdGhleSB3YW50IHRvIG1haW50YWluIHRoZQpsZXhpY29ncmFw
aGljYWwgc29ydCBvcmRlciBvZiB0aGUgZW5jb2RlZCBkYXRhLgoKUGVyIGJ1aWxkZmFybSBtZW1i
ZXIgaGlwcG9wb3RhbXVzLgoKQW5hbHl6ZWQtYnk6IFRvbSBMYW5lIDx0Z2xAc3NzLnBnaC5wYS51
cz4KRGlzY3Vzc2lvbjogaHR0cHM6Ly9wb3N0Z3IuZXMvbS82ODI0MTcuMTc3NDQ4MjA0N0Bzc3Mu
cGdoLnBhLnVzCi0tLQogZG9jL3NyYy9zZ21sL2Z1bmMvZnVuYy1iaW5hcnlzdHJpbmcuc2dtbCB8
IDkgKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3V1aWQub3V0ICAgICAgIHwg
NyArKysrKy0tCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC91dWlkLnNxbCAgICAgICAgICAgIHwgNyAr
KysrKy0tCiAzIGZpbGVzIGNoYW5nZWQsIDE5IGluc2VydGlvbnMoKyksIDQgZGVsZXRpb25zKC0p
CgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL2Z1bmMvZnVuYy1iaW5hcnlzdHJpbmcuc2dtbCBi
L2RvYy9zcmMvc2dtbC9mdW5jL2Z1bmMtYmluYXJ5c3RyaW5nLnNnbWwKaW5kZXggMGFhZjliYzY4
ZjEuLjJhZDJjZGJlYTgyIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvZnVuYy9mdW5jLWJpbmFy
eXN0cmluZy5zZ21sCisrKyBiL2RvYy9zcmMvc2dtbC9mdW5jL2Z1bmMtYmluYXJ5c3RyaW5nLnNn
bWwKQEAgLTc5MCw2ICs3OTAsMTUgQEAKICAgICAgICBwcm9kdWNlcyBhIDI2LWNoYXJhY3RlciBz
dHJpbmcgY29tcGFyZWQgdG8gdGhlIHN0YW5kYXJkIDM2LWNoYXJhY3RlcgogICAgICAgIFVVSUQg
cmVwcmVzZW50YXRpb24uCiAgICAgICA8L3BhcmE+CisKKyAgICAgIDxub3RlPgorICAgICAgIDxw
YXJhPgorICAgICAgICBUbyBtYWludGFpbiB0aGUgbGV4aWNvZ3JhcGhpY2FsIHNvcnQgb3JkZXIg
b2YgdGhlIGVuY29kZWQgZGF0YSwKKyAgICAgICAgZW5zdXJlIHRoYXQgdGhlIHRleHQgaXMgc29y
dGVkIHVzaW5nIHRoZSBDIGNvbGxhdGlvbgorICAgICAgICAoZS5nLiwgdXNpbmcgPGxpdGVyYWw+
Q09MTEFURSAiQyI8L2xpdGVyYWw+KS4gTmF0dXJhbCBsYW5ndWFnZQorICAgICAgICBjb2xsYXRp
b25zIG1heSBzb3J0IGNoYXJhY3RlcnMgZGlmZmVyZW50bHkgYW5kIGJyZWFrIHRoZSBvcmRlcmlu
Zy4KKyAgICAgICA8L3BhcmE+CisgICAgICA8L25vdGU+CiAgICAgIDwvbGlzdGl0ZW0+CiAgICAg
PC92YXJsaXN0ZW50cnk+CiAKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQv
dXVpZC5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3V1aWQub3V0CmluZGV4IDE0MmM1
MjllNjkzLi45YzVkZGE5ZTlhYiAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3Rl
ZC91dWlkLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3V1aWQub3V0CkBAIC0y
MzYsOCArMjM2LDExIEBAIFNFTEVDVCBhcnJheV9hZ2coaWQgT1JERVIgQlkgZ3VpZF9maWVsZCkg
RlJPTSBndWlkMzsKICB7MSwyLDMsNCw1LDYsNyw4LDksMTAsMTEsMTJ9CiAoMSByb3cpCiAKLS0t
IG1ha2Ugc3VyZSBiYXNlMzJoZXggZW5jb2Rpbmcgd29ya3Mgd2l0aCBVVUlEcyBhbmQgcHJlc2Vy
dmVzIG9yZGVyaW5nCi1TRUxFQ1QgYXJyYXlfYWdnKGlkIE9SREVSIEJZIGd1aWRfZW5jb2RlZCkg
RlJPTSBndWlkMzsKKy0tIFRlc3QgYmFzZTMyaGV4IGVuY29kaW5nIG9mIFVVSURzIGFuZCBpdHMg
bGV4aWNvZ3JhcGhpY2FsIHNvcnRpbmcgcHJvcGVydHkuCistLSBDT0xMQVRFICJDIiBpcyByZXF1
aXJlZCB0byBwcmV2ZW50IGJ1aWxkZmFybSBmYWlsdXJlcyBpbiBub24tQyBsb2NhbGVzCistLSB3
aGVyZSBuYXR1cmFsIGxhbmd1YWdlIGNvbGxhdGlvbnMgKHN1Y2ggYXMgY3NfQ1opIHdvdWxkIGJy
ZWFrIHN0cmljdAorLS0gYnl0ZS13aXNlIG9yZGVyaW5nLgorU0VMRUNUIGFycmF5X2FnZyhpZCBP
UkRFUiBCWSBndWlkX2VuY29kZWQgQ09MTEFURSAiQyIpIEZST00gZ3VpZDM7CiAgICAgICAgICAg
YXJyYXlfYWdnICAgICAgICAgICAKIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogIHsx
LDIsMyw0LDUsNiw3LDgsOSwxMCwxMSwxMn0KZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mv
c3FsL3V1aWQuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvdXVpZC5zcWwKaW5kZXggZjJmZjAw
ZjVkZGQuLjhjYzJhZDQwNjE0IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC91dWlk
LnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC91dWlkLnNxbApAQCAtMTIyLDggKzEyMiwx
MSBAQCBJTlNFUlQgSU5UTyBndWlkMyAoZ3VpZF9maWVsZCkgU0VMRUNUIHV1aWR2NygpIEZST00g
Z2VuZXJhdGVfc2VyaWVzKDEsIDEwKTsKIElOU0VSVCBJTlRPIGd1aWQzIChndWlkX2ZpZWxkKSBW
QUxVRVMgKCdmZmZmZmZmZi1mZmZmLWZmZmYtZmZmZi1mZmZmZmZmZmZmZmYnOjp1dWlkKTsKIFNF
TEVDVCBhcnJheV9hZ2coaWQgT1JERVIgQlkgZ3VpZF9maWVsZCkgRlJPTSBndWlkMzsKIAotLS0g
bWFrZSBzdXJlIGJhc2UzMmhleCBlbmNvZGluZyB3b3JrcyB3aXRoIFVVSURzIGFuZCBwcmVzZXJ2
ZXMgb3JkZXJpbmcKLVNFTEVDVCBhcnJheV9hZ2coaWQgT1JERVIgQlkgZ3VpZF9lbmNvZGVkKSBG
Uk9NIGd1aWQzOworLS0gVGVzdCBiYXNlMzJoZXggZW5jb2Rpbmcgb2YgVVVJRHMgYW5kIGl0cyBs
ZXhpY29ncmFwaGljYWwgc29ydGluZyBwcm9wZXJ0eS4KKy0tIENPTExBVEUgIkMiIGlzIHJlcXVp
cmVkIHRvIHByZXZlbnQgYnVpbGRmYXJtIGZhaWx1cmVzIGluIG5vbi1DIGxvY2FsZXMKKy0tIHdo
ZXJlIG5hdHVyYWwgbGFuZ3VhZ2UgY29sbGF0aW9ucyAoc3VjaCBhcyBjc19DWikgd291bGQgYnJl
YWsgc3RyaWN0CistLSBieXRlLXdpc2Ugb3JkZXJpbmcuCitTRUxFQ1QgYXJyYXlfYWdnKGlkIE9S
REVSIEJZIGd1aWRfZW5jb2RlZCBDT0xMQVRFICJDIikgRlJPTSBndWlkMzsKIAogLS0gQ2hlY2sg
dGhlIHRpbWVzdGFtcCBvZmZzZXRzIGZvciB2Ny4KIC0tCi0tIAoyLjUzLjAKCg==
--000000000000f3b9cd064de33668--