Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
| От | David G. Johnston |
|---|---|
| Тема | Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST) |
| Дата | |
| Msg-id | CAKFQuwYSj4x+dTZrK935XtxKm7fWUABSWX4U_t-ivfUS1xNrzg@mail.gmail.com обсуждение исходный текст |
| Ответ на | BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST) (PG Bug reporting form <noreply@postgresql.org>) |
| Ответы |
Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST) |
| Список | pgsql-bugs |
On Thu, Aug 1, 2024 at 10:19 AM PG Bug reporting form <noreply@postgresql.org> wrote:
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.
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.
В списке pgsql-bugs по дате отправления: