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