Re: Bug in comparison of empty jsonb arrays to scalars

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Thu, Nov 10, 2016 at 7:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Given that nobody actually cares what that sort order is, I think that
>> having to jump through hoops in pg_upgrade in order to fix it is not a
>> great tradeoff. I suggest changing the documentation to match the code.

> Yes, definitely.
> So that's object > boolean > integer > string > NULL > array.

No, because the issue is that empty and nonempty arrays sort differently.

regression=# create table json_data (a jsonb);
CREATE TABLE
regression=# INSERT INTO json_data values ('{}'::jsonb), ('[]'::jsonb),
regression-# ('null'::jsonb), ('true'::jsonb), ('1'::jsonb), ('""'::jsonb),
regression-# ('[42]'::jsonb),('[[43]]'::jsonb);
INSERT 0 8
regression=# SELECT * FROM json_data ORDER BY 1 DESC;
a
--------
{}
[[43]]
[42]
true
1
""
null
[]
(8 rows)

> And attached is a patch.

If we go with the fix-the-docs approach, we'll need to have two entries
for empty and nonempty arrays. It's definitely ugly. Still, my judgement
is that it's not worth the pain of changing the behavior. It was never
intended that this sort order be anything but an implementation detail.

(I guess another approach is to not document the order at all ...)

regards, tom lane