| From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
|---|---|
| To: | Vik Fearing <vik(at)postgresfriends(dot)org> |
| Cc: | lukas(dot)eder(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org, PG Bug reporting form <noreply(at)postgresql(dot)org>, Álvaro Herrera <alvherre(at)kurilemu(dot)de> |
| Subject: | Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> |
| Date: | 2026-03-02 05:45:03 |
| Message-ID: | CAMbWs4-y4CEy66yA-Hh+6Snqbok3XTMFj5h87gUk_15Hhjd3qw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Mon, Mar 2, 2026 at 2:09 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > I wonder if we can fix it by wrapping the JSON_ARRAYAGG in a COALESCE
> > to catch the NULL and convert it to an empty array; ie:
> >
> > SELECT COALESCE(
> > JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),
> > '[]'::[RETURNING_TYPE]
> > ) FROM (query) q(a)
> The attached patch seems to fix it.
(cc-ing Álvaro who committed 7081ac46a)
Regarding back-patching, I believe this fix is safe to back-patch to
stable branches. However, similar to a nearby bug fix, this will only
apply to newly created views. Existing views will continue to exhibit
the old behavior until recreated. Additionally, this changes the
user-facing output from NULL to [], so users may need to update any
application code that relied on the NULL behavior.
- Richard
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Guo | 2026-03-02 05:53:55 | Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types |
| Previous Message | Richard Guo | 2026-03-02 05:09:46 | Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> |