From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: neil.chen@protonbase.io
Subject: BUG #18265: jsonb comparison order is inconsistent
Date: Tue, 02 Jan 2024 09:09:42 +0000
Message-ID: <18265-fd06fa2f9ad3d1db@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18265
Logged by: neil chen
Email address: neil.chen@protonbase.io
PostgreSQL version: 14.1
Operating system: operating system irrelevant
Description:
As the docuemnt saied, for jsonb type, Object > Array > Boolean > Number >
String > Null, but actually empty json array is sort to be the smallest
value.
And this is true only when it is the outmost json value, that is,
'[]'::jsonb is less than 'null'::jsonb.
but when it is a inner element of json array, the comparison result will
changed, that is, '[[]]'::jsonb is greater than '[null]'.
This behavior looks strange, that maybe a mistake in
compareJsonbContainers().
/*
* This could be a "raw scalar" pseudo array. That's
* a special case here though, since we still want the
* general type-based comparisons to apply, and as far
* as we're concerned a pseudo array is just a scalar.
*/
if (va.val.array.rawScalar != vb.val.array.rawScalar)
// Here one is json array, and another is not json array, maybe we should
simply compare their type.
// And the comparison result should not be overwritten by the following if
statement.
res = (va.val.array.rawScalar) ? -1 : 1;
if (va.val.array.nElems != vb.val.array.nElems)
res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
break;
postgres=# select * from
(values('null'::jsonb),('[]'::jsonb),('[null]'::jsonb),('[[]]'::jsonb)) as
t(j) order by j;
j
--------
[]
null
[null]
[[]]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, neil.chen@protonbase.io
Subject: Re: BUG #18265: jsonb comparison order is inconsistent
In-Reply-To: <18265-fd06fa2f9ad3d1db@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox