From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: sunnybluemoon050@gmail.com
Subject: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
Date: Thu, 01 Aug 2024 17:01:36 +0000
Message-ID: <18564-5985f90678ed7512@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, sunnybluemoon050@gmail.com
Subject: Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
In-Reply-To: <18564-5985f90678ed7512@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox