From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: sunnybluemoon050@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
Date: Thu, 01 Aug 2024 15:34:55 -0400
Message-ID: <3783688.1722540895@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwYSj4x+dTZrK935XtxKm7fWUABSWX4U_t-ivfUS1xNrzg@mail.gmail.com>
References: <18564-5985f90678ed7512@postgresql.org>
<CAKFQuwYSj4x+dTZrK935XtxKm7fWUABSWX4U_t-ivfUS1xNrzg@mail.gmail.com>
"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
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: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, sunnybluemoon050@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)
In-Reply-To: <3783688.1722540895@sss.pgh.pa.us>
* 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