MIME-Version: 1.0
From: Matt Blewitt <mble@planetscale.com>
Date: Fri, 13 Mar 2026 11:02:01 +0000
Message-ID: 
 <CACy-Nv0UaChW+mNd2-iDnsKbuhohymcKtC+xoo-rV1OcCvnK6g@mail.gmail.com>
Subject: [PATCH] Fix JSON_SERIALIZE() coercion placeholder type for jsonb
 input
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000269bfa064ce5cdf6"
Archived-At: 
 <https://www.postgresql.org/message-id/CACy-Nv0UaChW%2BmNd2-iDnsKbuhohymcKtC%2Bxoo-rV1OcCvnK6g%40mail.gmail.com>
Precedence: bulk

--000000000000269bfa064ce5cdf6
Content-Type: multipart/alternative; boundary="000000000000269bf8064ce5cdf4"

--000000000000269bf8064ce5cdf4
Content-Type: text/plain; charset="UTF-8"

Hi folks,

Related to:
https://www.postgresql.org/message-id/03583FE7-A5EE-407C-907D-67865F3F8BFC%40gmail.com

When JSON_SERIALIZE() receives a jsonb-typed argument, the CaseTestExpr
placeholder used to set up coercion was unconditionally assigned JSONOID
(derived from the RETURNING format, which defaults to JS_FORMAT_JSON).
However, the executor passes the input argument value through directly
for JSON_SERIALIZE (see ExecInitExprRec in execExpr.c), so the actual
datum at runtime is jsonb, not json.  This type mismatch between the
placeholder and the runtime value caused the wrong coercion path to be
selected.

As Dirkjan mentioned, this results in confusing output.

Attached is a patch to address this, by deriving the placeholder type from
the
actual argument type via exprType(linitial(args)) when the constructor type
is
JSCTOR_JSON_SERIALIZE, rather than from returning->format->format_type.

Added a regression test to ensure JSONB is serialized correctly.

Cheers,

Matt

--000000000000269bf8064ce5cdf4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi folks,<div><br></div><div>Related to:=C2=A0<a href=3D"h=
ttps://www.postgresql.org/message-id/03583FE7-A5EE-407C-907D-67865F3F8BFC%4=
0gmail.com">https://www.postgresql.org/message-id/03583FE7-A5EE-407C-907D-6=
7865F3F8BFC%40gmail.com</a></div><div><br></div><div>When JSON_SERIALIZE() =
receives a jsonb-typed argument, the CaseTestExpr<br>placeholder used to se=
t up coercion was unconditionally assigned JSONOID<br>(derived from the RET=
URNING format, which defaults to JS_FORMAT_JSON).<br>However, the executor =
passes the input argument value through directly<br>for JSON_SERIALIZE (see=
 ExecInitExprRec in execExpr.c), so the actual<br>datum at runtime is jsonb=
, not json.=C2=A0 This type mismatch between the<br>placeholder and the run=
time value caused the wrong coercion path to be<br>selected.</div><div><br>=
</div><div>As Dirkjan mentioned, this results in confusing output.</div><di=
v><br></div><div>Attached is a patch to address this, by deriving the place=
holder type from the<br>actual argument type via exprType(linitial(args)) w=
hen the constructor type is<br>JSCTOR_JSON_SERIALIZE, rather than from retu=
rning-&gt;format-&gt;format_type.</div><div><br></div><div>Added a regressi=
on test to ensure JSONB is serialized correctly.</div><div><br></div><div>C=
heers,</div><div><br></div><div>Matt</div></div>

--000000000000269bf8064ce5cdf4--

--000000000000269bfa064ce5cdf6
Content-Type: application/octet-stream;
	name="0001-Fix-JSON_SERIALIZE-coercion-placeholder-type-for-jso.patch"
Content-Disposition: attachment;
	filename="0001-Fix-JSON_SERIALIZE-coercion-placeholder-type-for-jso.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mmoseb970>
X-Attachment-Id: f_mmoseb970

RnJvbSA0MTMwNDJiODA2ODk2NmExNjNjMDg1ZTlmN2Y1ZWViZDUxNjg5MjQ1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYXR0IEJsZXdpdHQgPG1ibGVAcGxhbmV0c2NhbGUuY29tPgpE
YXRlOiBUdWUsIDEwIE1hciAyMDI2IDIxOjM5OjM2ICswMDAwClN1YmplY3Q6IFtQQVRDSF0gRml4
IEpTT05fU0VSSUFMSVpFKCkgY29lcmNpb24gcGxhY2Vob2xkZXIgdHlwZSBmb3IganNvbmIKIGlu
cHV0CgpXaGVuIEpTT05fU0VSSUFMSVpFKCkgcmVjZWl2ZXMgYSBqc29uYi10eXBlZCBhcmd1bWVu
dCwgdGhlIENhc2VUZXN0RXhwcgpwbGFjZWhvbGRlciB1c2VkIHRvIHNldCB1cCBjb2VyY2lvbiB3
YXMgdW5jb25kaXRpb25hbGx5IGFzc2lnbmVkIEpTT05PSUQKKGRlcml2ZWQgZnJvbSB0aGUgUkVU
VVJOSU5HIGZvcm1hdCwgd2hpY2ggZGVmYXVsdHMgdG8gSlNfRk9STUFUX0pTT04pLgpIb3dldmVy
LCB0aGUgZXhlY3V0b3IgcGFzc2VzIHRoZSBpbnB1dCBhcmd1bWVudCB2YWx1ZSB0aHJvdWdoIGRp
cmVjdGx5CmZvciBKU09OX1NFUklBTElaRSAoc2VlIEV4ZWNJbml0RXhwclJlYyBpbiBleGVjRXhw
ci5jKSwgc28gdGhlIGFjdHVhbApkYXR1bSBhdCBydW50aW1lIGlzIGpzb25iLCBub3QganNvbi4g
IFRoaXMgdHlwZSBtaXNtYXRjaCBiZXR3ZWVuIHRoZQpwbGFjZWhvbGRlciBhbmQgdGhlIHJ1bnRp
bWUgdmFsdWUgY2F1c2VkIHRoZSB3cm9uZyBjb2VyY2lvbiBwYXRoIHRvIGJlCnNlbGVjdGVkLgoK
Rml4IGJ5IGRlcml2aW5nIHRoZSBwbGFjZWhvbGRlciB0eXBlIGZyb20gdGhlIGFjdHVhbCBhcmd1
bWVudCB0eXBlIHZpYQpleHByVHlwZShsaW5pdGlhbChhcmdzKSkgd2hlbiB0aGUgY29uc3RydWN0
b3IgdHlwZSBpcyBKU0NUT1JfSlNPTl9TRVJJQUxJWkUsCnJhdGhlciB0aGFuIGZyb20gcmV0dXJu
aW5nLT5mb3JtYXQtPmZvcm1hdF90eXBlLgoKQWRkIGFuIEFzc2VydCB0byBndWFyZCB0aGUgYXNz
dW1wdGlvbiB0aGF0IGFyZ3MgaXMgbm9uLWVtcHR5IGZvciB0aGlzCnBhdGgsIGFuZCB1cGRhdGUg
dGhlIGJsb2NrIGNvbW1lbnQgdG8gZXhwbGFpbiB3aHkgSlNPTl9TRVJJQUxJWkUgZGlmZmVycwpm
cm9tIHRoZSBvdGhlciBjb25zdHJ1Y3RvciB0eXBlcyAoaXQgY29uc3VtZXMganNvbi9qc29uYiBy
YXRoZXIgdGhhbgpwcm9kdWNpbmcgaXQpLgoKRXh0ZW5kIHRoZSBzcWxqc29uIHJlZ3Jlc3Npb24g
dGVzdHMgd2l0aCBFWFBMQUlOIG91dHB1dCwgYWRkaXRpb25hbApSRVRVUk5JTkcgdmFyaWFudHMg
KHZhcmNoYXIsIGJ5dGVhKSwgYW5kIGVycm9yIGNhc2VzIChSRVRVUk5JTkcgaW50LApSRVRVUk5J
TkcganNvbmIpIGZvciBqc29uYi10eXBlZCBpbnB1dCB0byBKU09OX1NFUklBTElaRSgpLgotLS0K
IHNyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMgICAgICAgfCAxOCArKysrKysrKy0tCiBz
cmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3NxbGpzb24ub3V0IHwgNTIgKysrKysrKysrKysrKysr
KysrKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9zcWxqc29uLnNxbCAgICAgIHwgMTMg
KysrKysrKwogMyBmaWxlcyBjaGFuZ2VkLCA4MCBpbnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygt
KQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMgYi9zcmMvYmFj
a2VuZC9wYXJzZXIvcGFyc2VfZXhwci5jCmluZGV4IGRjZmUxYWNjLi5iYTAyYjExNyAxMDA2NDQK
LS0tIGEvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2V4cHIuYworKysgYi9zcmMvYmFja2VuZC9w
YXJzZXIvcGFyc2VfZXhwci5jCkBAIC0zNjk0LDcgKzM2OTQsMTMgQEAgbWFrZUpzb25Db25zdHJ1
Y3RvckV4cHIoUGFyc2VTdGF0ZSAqcHN0YXRlLCBKc29uQ29uc3RydWN0b3JUeXBlIHR5cGUsCiAJ
ICogQ29lcmNlIHRvIHRoZSBSRVRVUk5JTkcgdHlwZSBhbmQgZm9ybWF0LCBpZiBuZWVkZWQuICBX
ZSBhYnVzZQogCSAqIENhc2VUZXN0RXhwciBoZXJlIGFzIHBsYWNlaG9sZGVyIHRvIHBhc3MgdGhl
IHJlc3VsdCBvZiBlaXRoZXIKIAkgKiBldmFsdWF0aW5nICdmZXhwcicgb3Igd2hhdGV2ZXIgaXMg
cHJvZHVjZWQgYnkgRXhlY0V2YWxKc29uQ29uc3RydWN0b3IoKQotCSAqIHRoYXQgaXMgb2YgdHlw
ZSBKU09OIG9yIEpTT05CIHRvIHRoZSBjb2VyY2lvbiBmdW5jdGlvbi4KKwkgKiB0byB0aGUgY29l
cmNpb24gZnVuY3Rpb24uCisJICoKKwkgKiBGb3IgbW9zdCBjb25zdHJ1Y3RvciB0eXBlcyB0aGUg
cGxhY2Vob2xkZXIgdHlwZSBpcyBKU09OIG9yIEpTT05CLAorCSAqIGRldGVybWluZWQgYnkgdGhl
IFJFVFVSTklORyBmb3JtYXQuICBKU09OX1NFUklBTElaRSBpcyBkaWZmZXJlbnQ6IGl0CisJICog
ZG9lc24ndCBwcm9kdWNlIGpzb24vanNvbmIgYnV0IHJhdGhlciBjb25zdW1lcyBpdCwgYW5kIHRo
ZSBleGVjdXRvcgorCSAqIHBhc3NlcyB0aGUgaW5wdXQgYXJndW1lbnQgdGhyb3VnaCBkaXJlY3Rs
eSAoc2VlIGV4ZWNFeHByLmMpLCBzbyB0aGUKKwkgKiBwbGFjZWhvbGRlciBtdXN0IHJlZmxlY3Qg
dGhlIGFjdHVhbCBhcmd1bWVudCB0eXBlLgogCSAqLwogCWlmIChmZXhwcikKIAl7CkBAIC0zNzEw
LDggKzM3MTYsMTQgQEAgbWFrZUpzb25Db25zdHJ1Y3RvckV4cHIoUGFyc2VTdGF0ZSAqcHN0YXRl
LCBKc29uQ29uc3RydWN0b3JUeXBlIHR5cGUsCiAJewogCQlDYXNlVGVzdEV4cHIgKmN0ZSA9IG1h
a2VOb2RlKENhc2VUZXN0RXhwcik7CiAKLQkJY3RlLT50eXBlSWQgPSByZXR1cm5pbmctPmZvcm1h
dC0+Zm9ybWF0X3R5cGUgPT0gSlNfRk9STUFUX0pTT05CID8KLQkJCUpTT05CT0lEIDogSlNPTk9J
RDsKKwkJaWYgKHR5cGUgPT0gSlNDVE9SX0pTT05fU0VSSUFMSVpFKQorCQl7CisJCQlBc3NlcnQo
YXJncyAhPSBOSUwpOworCQkJY3RlLT50eXBlSWQgPSBleHByVHlwZShsaW5pdGlhbChhcmdzKSk7
CisJCX0KKwkJZWxzZQorCQkJY3RlLT50eXBlSWQgPSByZXR1cm5pbmctPmZvcm1hdC0+Zm9ybWF0
X3R5cGUgPT0gSlNfRk9STUFUX0pTT05CID8KKwkJCQlKU09OQk9JRCA6IEpTT05PSUQ7CiAJCWN0
ZS0+dHlwZU1vZCA9IC0xOwogCQljdGUtPmNvbGxhdGlvbiA9IEludmFsaWRPaWQ7CiAKZGlmZiAt
LWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3FsanNvbi5vdXQgYi9zcmMvdGVzdC9y
ZWdyZXNzL2V4cGVjdGVkL3NxbGpzb24ub3V0CmluZGV4IGM3YjllNTc1Li5lOGU2YmNmNyAxMDA2
NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9zcWxqc29uLm91dAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL2V4cGVjdGVkL3NxbGpzb24ub3V0CkBAIC0yODgsNiArMjg4LDU4IEBAIEVY
UExBSU4gKFZFUkJPU0UsIENPU1RTIE9GRikgU0VMRUNUIEpTT05fU0VSSUFMSVpFKCd7fScgUkVU
VVJOSU5HIGJ5dGVhKTsKICAgIE91dHB1dDogSlNPTl9TRVJJQUxJWkUoJ3t9Jzo6anNvbiBSRVRV
Uk5JTkcgYnl0ZWEpCiAoMiByb3dzKQogCistLSBKU09OX1NFUklBTElaRSgpIHdpdGgganNvbmIg
aW5wdXQKK1NFTEVDVCBKU09OX1NFUklBTElaRSgnWzEsMiw0LDVdJzo6anNvbmIpOworIGpzb25f
c2VyaWFsaXplIAorLS0tLS0tLS0tLS0tLS0tLQorIFsxLCAyLCA0LCA1XQorKDEgcm93KQorCitT
RUxFQ1QgSlNPTl9TRVJJQUxJWkUoJ3siYSI6IDF9Jzo6anNvbmIpOworIGpzb25fc2VyaWFsaXpl
IAorLS0tLS0tLS0tLS0tLS0tLQorIHsiYSI6IDF9CisoMSByb3cpCisKK1NFTEVDVCBKU09OX1NF
UklBTElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRVUk5JTkcgdGV4dCk7CisganNvbl9zZXJpYWxp
emUgCistLS0tLS0tLS0tLS0tLS0tCisgeyJhIjogMX0KKygxIHJvdykKKworU0VMRUNUIEpTT05f
U0VSSUFMSVpFKCd7ImEiOiAxfSc6Ompzb25iIFJFVFVSTklORyBieXRlYSk7CisgICBqc29uX3Nl
cmlhbGl6ZSAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyBceDdiMjI2MTIyM2EyMDMxN2QKKygx
IHJvdykKKworU0VMRUNUIEpTT05fU0VSSUFMSVpFKCd7ImEiOiAxfSc6Ompzb25iIFJFVFVSTklO
RyB2YXJjaGFyKTsKKyBqc29uX3NlcmlhbGl6ZSAKKy0tLS0tLS0tLS0tLS0tLS0KKyB7ImEiOiAx
fQorKDEgcm93KQorCitFWFBMQUlOIChWRVJCT1NFLCBDT1NUUyBPRkYpIFNFTEVDVCBKU09OX1NF
UklBTElaRSgneyJhIjogMX0nOjpqc29uYik7CisgICAgICAgICAgICAgICAgICAgICAgICAgUVVF
UlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIFJlc3VsdAorICAgT3V0cHV0
OiBKU09OX1NFUklBTElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRVUk5JTkcgdGV4dCkKKygyIHJv
d3MpCisKK0VYUExBSU4gKFZFUkJPU0UsIENPU1RTIE9GRikgU0VMRUNUIEpTT05fU0VSSUFMSVpF
KCd7ImEiOiAxfSc6Ompzb25iIFJFVFVSTklORyBieXRlYSk7CisgICAgICAgICAgICAgICAgICAg
ICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgUmVzdWx0
CisgICBPdXRwdXQ6IEpTT05fU0VSSUFMSVpFKCd7ImEiOiAxfSc6Ompzb25iIFJFVFVSTklORyBi
eXRlYSkKKygyIHJvd3MpCisKKy0tIGpzb25iIGlucHV0OiBlcnJvciBjYXNlcworU0VMRUNUIEpT
T05fU0VSSUFMSVpFKCd7ImEiOiAxfSc6Ompzb25iIFJFVFVSTklORyBpbnQpOworRVJST1I6ICBj
YW5ub3QgdXNlIHR5cGUgaW50ZWdlciBpbiBSRVRVUk5JTkcgY2xhdXNlIG9mIEpTT05fU0VSSUFM
SVpFKCkKK0hJTlQ6ICBUcnkgcmV0dXJuaW5nIGEgc3RyaW5nIHR5cGUgb3IgYnl0ZWEuCitTRUxF
Q1QgSlNPTl9TRVJJQUxJWkUoJ3siYSI6IDF9Jzo6anNvbmIgUkVUVVJOSU5HIGpzb25iKTsKK0VS
Uk9SOiAgY2Fubm90IHVzZSB0eXBlIGpzb25iIGluIFJFVFVSTklORyBjbGF1c2Ugb2YgSlNPTl9T
RVJJQUxJWkUoKQorSElOVDogIFRyeSByZXR1cm5pbmcgYSBzdHJpbmcgdHlwZSBvciBieXRlYS4K
IC0tIEpTT05fT0JKRUNUKCkKIFNFTEVDVCBKU09OX09CSkVDVCgpOwogIGpzb25fb2JqZWN0IApk
aWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvc3FsanNvbi5zcWwgYi9zcmMvdGVzdC9y
ZWdyZXNzL3NxbC9zcWxqc29uLnNxbAppbmRleCAzNDNkMzQ0ZC4uNGE0NWZjZGMgMTAwNjQ0Ci0t
LSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3NxbGpzb24uc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jl
c3Mvc3FsL3NxbGpzb24uc3FsCkBAIC02Miw2ICs2MiwxOSBAQCBTRUxFQ1QgSlNPTl9TRVJJQUxJ
WkUoJ3sgImEiIDogMSB9ICcgUkVUVVJOSU5HIGpzb25iKTsKIEVYUExBSU4gKFZFUkJPU0UsIENP
U1RTIE9GRikgU0VMRUNUIEpTT05fU0VSSUFMSVpFKCd7fScpOwogRVhQTEFJTiAoVkVSQk9TRSwg
Q09TVFMgT0ZGKSBTRUxFQ1QgSlNPTl9TRVJJQUxJWkUoJ3t9JyBSRVRVUk5JTkcgYnl0ZWEpOwog
CistLSBKU09OX1NFUklBTElaRSgpIHdpdGgganNvbmIgaW5wdXQKK1NFTEVDVCBKU09OX1NFUklB
TElaRSgnWzEsMiw0LDVdJzo6anNvbmIpOworU0VMRUNUIEpTT05fU0VSSUFMSVpFKCd7ImEiOiAx
fSc6Ompzb25iKTsKK1NFTEVDVCBKU09OX1NFUklBTElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRV
Uk5JTkcgdGV4dCk7CitTRUxFQ1QgSlNPTl9TRVJJQUxJWkUoJ3siYSI6IDF9Jzo6anNvbmIgUkVU
VVJOSU5HIGJ5dGVhKTsKK1NFTEVDVCBKU09OX1NFUklBTElaRSgneyJhIjogMX0nOjpqc29uYiBS
RVRVUk5JTkcgdmFyY2hhcik7CitFWFBMQUlOIChWRVJCT1NFLCBDT1NUUyBPRkYpIFNFTEVDVCBK
U09OX1NFUklBTElaRSgneyJhIjogMX0nOjpqc29uYik7CitFWFBMQUlOIChWRVJCT1NFLCBDT1NU
UyBPRkYpIFNFTEVDVCBKU09OX1NFUklBTElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRVUk5JTkcg
Ynl0ZWEpOworCistLSBqc29uYiBpbnB1dDogZXJyb3IgY2FzZXMKK1NFTEVDVCBKU09OX1NFUklB
TElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRVUk5JTkcgaW50KTsKK1NFTEVDVCBKU09OX1NFUklB
TElaRSgneyJhIjogMX0nOjpqc29uYiBSRVRVUk5JTkcganNvbmIpOworCiAtLSBKU09OX09CSkVD
VCgpCiBTRUxFQ1QgSlNPTl9PQkpFQ1QoKTsKIFNFTEVDVCBKU09OX09CSkVDVChSRVRVUk5JTkcg
anNvbik7Ci0tIAoyLjUyLjAKCg==
--000000000000269bfa064ce5cdf6--