From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | sunnybluemoon050(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST) |
Date: | 2024-08-01 19:34:55 |
Message-ID: | 3783688.1722540895@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thu, Aug 1, 2024 at 10:19 AM PG Bug reporting form <
> noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-08-01 19:46:34 | Re: BUG #18565: Job finishes successfully but reruns unwanted!!! |
Previous Message | Tom Lane | 2024-08-01 19:23:29 | Re: BUG #18563: Where is tha "FIRST" aggregate function?? |