Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)

On Thu, Aug 1, 2024 at 10:19 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18564
> Logged by: Svetlana
> Email address: sunnybluemoon050(at)gmail(dot)com
> PostgreSQL version: 16.3
> Operating system: macOS
> Description:
>
> Issue Description:
>
> I am encountering a bug when performing a query with sorting on a nullable
> float field within a jsonb column, using NULLS LAST. Despite this, NULL
> values appear at the beginning of the sorted results. When casting the
> value
> to text, NULL values correctly appear at the end of the results. However,
> sorting is incorrect because the values are then treated as text, which
> affects the sorting order.
>
>
> Expected Behavior:
> NULL values should appear at the end of the sorted results, and the sorting
> should be accurate based on the numeric values.
>
> Actual Behavior:
> NULL values appear at the beginning when sorting numerically.
>
>
Interesting...

Since a JSON typed null value is not an SQL NULL value the order by
machinery sees a perfectly valid non-null value to be sorted alongside the
non-null data. When forcing the json to be text the cast does convert a
json null value to a SQL text NULL value

In short, while this can be controlled at the SQL scope, the comparison
operator for the json data type provides no such ability for the user to
control the result of the comparison between null and non-null json
values. null values apparently always sort lower than non-null values.

You will probably need to sort on two expressions, the first one to place
all json null values after non-null ones, then a second expression to
numerically sort the non-null rows in the desired order.

It is not a bug, and I have no clue if it is even reasonable for
the comparison function for json to consider the context in which it is
being performed.

David J.