Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEFv1-0005b5-0D for pgsql-sql@arkaria.postgresql.org; Sat, 07 Jan 2023 20:35:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEFuw-00008C-Sv for pgsql-sql@arkaria.postgresql.org; Sat, 07 Jan 2023 20:35:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEFuw-00007D-Ki for pgsql-sql@lists.postgresql.org; Sat, 07 Jan 2023 20:35:50 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEFup-0008Rq-PW for pgsql-sql@lists.postgresql.org; Sat, 07 Jan 2023 20:35:49 +0000 Received: from [10.221.133.171] ([142.105.135.230]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LzuPF-1ojXYL1swN-0150AC for ; Sat, 07 Jan 2023 21:35:42 +0100 Message-ID: <25e94e6e-5b18-9d45-0b7b-c23bd4c9a17b@sopchak.me> Date: Sat, 7 Jan 2023 15:35:41 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.0 Content-Language: en-US To: pgsql-sql@lists.postgresql.org From: Carl Sopchak Subject: Order By weirdness? Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Provags-ID: V03:K1:IX/NyBPTbBywK3Gx0huCUk50B/gmC9FJaX81iQSz4Vl00UrATjb 6mNojmvfZjp52/QWQLtszNkAHMfRV46wBwg1NKbpsQQqCSCQ1FUMqMBil910SvBlb0Kt3Yi OKwWoxxtIV+3j2IqBpX3IHNlqBDGwIuFzDaItWzpu1Icw7F7h+lMAQpKCb8qCiDi07OKDO6 QN//bXeVLiXJMsI6niULw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:pYP97nmZWUQ=;wMKtsGUHC6nlOHpSGoq1mhqjvWc jGuKvGQrpxlLHDHUd79IrQKQWNR0JF+QjnKHpuD+4ciEtFRNKKcLSizGbtQSi1nKK8VbrpSpH RvvfIYEXOB1sGKPnN/rmRFX5xAe0TISLcyf/hYdSAffQMWsbW13DsoMVSWUkfd/4KGW7Xpfsv ZPDF97aKrXGmFnWTaeLP/igK1sxXSi2XhGkTJ4sCXsGDuehC4No8mELQ+Jp7CcpJjqYdHbtVt lohsIs81ig/ByCf/uv4VLKdPJMW/mrSdEAfcFXpv5z+d9+NZvay5J8MXKyRSPZj61RKO3Y9xC zb938dN3SeMBpta910XWErpXIaI1yDmPBorpoRK9qpBmCK51CoiFIqvVAvt+3KDvzpPkO2bRo 32uqhymdND2o7rs0TvCbwSFO6DO3eDG/A2oqLmy3NraauvLHhvHE5YefZlon6lkjf4F6+rf2g Ci4VaTnch1sAdO4m5sZTixAo3ZPKdRyCP3sTVzxDvTqopu4foUXjAeE1ZUm+9e4Hxb5RikvIW 0bnMSDB436860Z2RK6/dgBn6fl9pgtAT99ZUeig8FzILbDA+Ufiy7jM3D7x+PHpCS4iMkzgD4 w6Z3jVsmXELd/PAV32imi7rapZGXBhd1SSV/KaA928mhWAEHM62ztqsyl/0s9aUhpGZULuMiP 4ouBrJ0osgAODDfZB1f1Y7yxVYGS+AS1hvp7BAM/IA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk

I'm seeing something (very) unexpected with ORDER BY. If I run this query:

select txt
from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'), ('x34567'), ('xz3456') ) a(txt)
order by txt;

I get expected results with x<#> being sorted before xz. However, if I replace the z's with ~, giving

select txt
from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'), ('x34567'), ('x~3456') ) a(txt)
order by txt;

I get this???

txt
--------
x~1234
x12345
x~2345
x23456
x~3456
x34567

Which appears to mean that ~ is treated differently than z (basically ~ is ignored). Same if I use other special characters, such as @.

Up until stumbling into this, I have never seen such behavior from a database. (Windows OS, yes, but I won't go there...) Character-based text always sorted in an alphabetic order (which puts special characters in different places in the ordering depending on encoding, but it's consistent).

Two questions (which may be the same way of asking the same question):

- How is this correct? I can see where this could be useful in limited scenarios, but IMHO it makes no sense as a default sort order.

- What do I need to do to get a strictly character-based sort in ORDER BY?

I am using postgres version 14.3 on Fedora 37.

Thanks for the help.

Carl