Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST) - Mailing list pgsql-bugs

"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