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

The following bug has been logged on the website:

Bug reference:      18564
Logged by:          Svetlana
Email address:      sunnybluemoon050@gmail.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.

Details:

Field Type: Nullable float within a jsonb column.
Desired Behavior: Sort by the float field with NULLS LAST.
Observed Behavior: NULL values appear at the beginning of the results.
Steps to Reproduce:
Create a table `example_table` with jsonb column `example_jsonb_column`.
Insert data into the table
```
INSERT INTO example_table (example_jsonb_column)
VALUES
('{"sorting_param": 1.0}'::jsonb),
('{"sorting_param": 2.0}'::jsonb),
('{"sorting_param": null}'::jsonb),
('{"sorting_param": null}'::jsonb);
```
Perform a query with sorting on the nullable float field within the jsonb
column, specifying NULLS LAST.
```
SELECT example_jsonb_column->'sorting_param'
FROM example_table
ORDER BY example_jsonb_column->'sorting_param' ASC NULLS LAST
```
Observe that NULL values are incorrectly placed at the beginning of the
results.
Cast the field to text and observe that NULL values are correctly placed at
the end, but the sorting order is incorrect.
```
SELECT example_jsonb_column->>'sorting_param'
FROM example_table
ORDER BY example_jsonb_column->>'sorting_param' ASC NULLS LAST
```

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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Aug 1, 2024 at 10:19 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> I am encountering a bug when performing a query with sorting on a nullable
>> float field within a jsonb column, using NULLS LAST.

> 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

I agree it's not a bug, because a json null isn't in itself a SQL
null.

You can get the desired result by

=# SELECT example_jsonb_column->'sorting_param'
FROM example_table
ORDER BY (example_jsonb_column->>'sorting_param')::float ASC NULLS LAST;
 ?column? 
----------
 1.0
 2.0
 null
 null
(4 rows)

This is fairly awkward though because it's converting to text and
thence to numeric.  I initially tried

=# SELECT example_jsonb_column->'sorting_param'
FROM example_table
ORDER BY (example_jsonb_column->'sorting_param')::float ASC NULLS LAST;
ERROR:  cannot cast jsonb null to type double precision

which seems to me, if not a bug, at least very poorly-chosen behavior.
If we allow casting of json null to a SQL null for text values, why
not for values of other types?

            regards, tom lane

    David G. Johnston wrote:

> 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.

It looks like a good use case for NULLIF.

SELECT example_jsonb_column->'sorting_param'
FROM example_table
ORDER BY NULLIF(example_jsonb_column->'sorting_param', 'null'::jsonb)
  ASC NULLS LAST ;

The doc says that primitive JSON values are compared using the same
comparison rules as for the underlying PostgreSQL data type, so the
non-null values will be sorted as "numeric", which meets the OP's
expectations:

> Expected Behavior:
> NULL values should appear at the end of the sorted results, and the sorting
> should be accurate based on the numeric values.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite