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

On 2024-12-03 Tu 9:11 AM, Andrew Dunstan wrote:
>
>
> 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.
>

Nobody else has commented, so I propose to apply this patch documenting
the anomaly.

cheers

andrew

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