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.
