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>
 <CAD21AoC=92zAEmaGt+3U1jSD77jgdQT_HXUwPPtZEze2sJ=vWA@mail.gmail.com>
In-Reply-To: 
 <CAD21AoC=92zAEmaGt+3U1jSD77jgdQT_HXUwPPtZEze2sJ=vWA@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 25 Mar 2026 20:13:55 -0700
Message-ID: 
 <CAD21AoCd9e5YvVWQY5JyEzK6tGLtJJWgw1+zgGpAwTOnJ7X1GQ@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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoCd9e5YvVWQY5JyEzK6tGLtJJWgw1%2BzgGpAwTOnJ7X1GQ%40mail.gmail.com>
Precedence: bulk

On Wed, Mar 25, 2026 at 6:21=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail.=
com> wrote:
>
> On Wed, Mar 25, 2026 at 6:09=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmai=
l.com> wrote:
> >
> > On Wed, Mar 25, 2026 at 5:35=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wr=
ote:
> > >
> > > 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 t=
he
> > > > 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.

Pushed the fix without the documentation changes to make the buildfarm
animals happy first.

Regards,

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