Thread: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:DengXian; panose-1:2 1 6 0 3 1 1 1 1 1;}@font-face {font-family:Aptos; panose-1:2 11 0 4 2 2 2 2 2 4;}@font-face {font-family:"\@DengXian"; panose-1:2 1 6 0 3 1 1 1 1 1;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; font-size:12.0pt; font-family:"Aptos",sans-serif; mso-ligatures:standardcontextual;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#467886; text-decoration:underline;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt; mso-ligatures:none;}div.WordSection1 {page:WordSection1;}

Dear PostgreSQL Hackers,

 

Problem Description

 

I encountered an issue with the B-Tree ordering of `jsonb` values. According to the PostgreSQL documentation[1], the ordering should follow this precedence:

 

`Object > Array > Boolean > Number > String > Null`

 

However, empty arrays (`[]`) are currently considered smaller than `null`, which violates the documented rules. This occurs due to improper handling of the `rawScalar` flag when comparing arrays in the `compareJsonbContainers()` function in `src/backend/utils/adt/jsonb_util.c`.

I agree that this is a (10 year old) bug:

-                        if (va.val.array.nElems != vb.val.array.nElems)
+                        else if (va.val.array.nElems != vb.val.array.nElems)

But I don't think we can fix it, because there could well be indexes that would no longer be valid if we change the sort order. Given that, I think the best we can do is adjust the documentation to mention the anomaly.

So the actual sort order as implemented is, AIUI,

Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array

which is ugly, but fortunately not many apps rely on jsonb sort order.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <andrew@dunslane.net> wrote:
>
> So the actual sort order as implemented is, AIUI,
>
> Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
>
> which is ugly, but fortunately not many apps rely on jsonb sort order.
>
> Nobody else has commented, so I propose to apply this patch documenting the anomaly.
>

while at it. we can fix the appearance of jsonb null.

since
select jsonb 'Null';
select jsonb 'NULL';
will fail.

so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>

On 2024-12-09 Mo 11:16 AM, jian he wrote:
> On Mon, Dec 9, 2024 at 9:27 PM Yan Chengpeng <chengpeng_yan@outlook.com> wrote:
>> Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
>>
>>
> sorry. maybe i didn't mention it explicitly.
> i mean something like:
>
> diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
> index 54648c459c..d9b24e413e 100644
> --- a/doc/src/sgml/json.sgml
> +++ b/doc/src/sgml/json.sgml
> @@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE
> jdoc @@ '$.tags[*] == "qui"';
>       The <literal>btree</literal> ordering for <type>jsonb</type>
> datums is seldom
>       of great interest, but for completeness it is:
>   <synopsis>
> -<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
>> <replaceable>Boolean</replaceable> >
> <replaceable>Number</replaceable> > <replaceable>String</replaceable>
>> <replaceable>Null</replaceable>
> +<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
>> <replaceable>Boolean</replaceable> >
> <replaceable>Number</replaceable> > <replaceable>String</replaceable>
>> <replaceable>null</replaceable>
>   <replaceable>Object with n pairs</replaceable> > <replaceable>object
> with n - 1 pairs</replaceable>
>
>   <replaceable>Array with n elements</replaceable> > <replaceable>array
> with n - 1 elements</replaceable>
>   </synopsis>
> +      with the exception that (for historical reasons) an empty array
> sorts less than <replaceable>null</replaceable>.
>         Objects with equal numbers of pairs are compared in the order:
>   <synopsis>
>   <replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>,
> <replaceable>key-2</replaceable> ...



Pushed something along these lines. In master I also added a code 
comment so nobody might be tempted to "fix" the anomaly.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com