Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> 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.

Okay, but ...

> 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.

... doesn't that point disqualify it from being back-patched?
People don't like unprompted behavioral changes in minor releases.
"This is what the standard says" is not strong enough to justify
changing behavior that was not obviously broken (like, say, crashing).

Another point is that the previous coding already failed to
be round-trippable, ie you wrote JSON_ARRAY() but what comes
out in view decompilation is JSON_ARRAYAGG(). This makes that
situation considerably worse. We should endeavor to not expose
implementation details like that. (To be clear, I don't object
if EXPLAIN shows that sort of thing. But it shouldn't creep
into view dumps. We've regretted doing that in the past.)

regards, tom lane