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

On 2024-11-18 Mo 9:25 AM, Yan Chengpeng wrote:
>
> 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