MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Thu, 9 Apr 2026 11:43:09 -0700
Message-ID: 
 <CAHg+QDfaAipL6YzOq2H=gAhKBbcUTYmfbAv+W1zueOfRKH43FQ@mail.gmail.com>
Subject: Bug: var_is_nonnullable() gives wrong results for old/new in
 RETURNING
To: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000ffb966064f0b6376"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDfaAipL6YzOq2H%3DgAhKBbcUTYmfbAv%2BW1zueOfRKH43FQ%40mail.gmail.com>
Precedence: bulk

--000000000000ffb966064f0b6376
Content-Type: multipart/alternative; boundary="000000000000ffb964064f0b6374"

--000000000000ffb964064f0b6374
Content-Type: text/plain; charset="UTF-8"

Hi hackers,

It appears the optimizer incorrectly simplifies old.<col> IS NULL to FALSE
in RETURNING clauses when the underlying column has a NOT NULL constraint.

The issue is that var_is_nonnullable() in clauses.c doesn't check
Var.varreturningtype. It sees a NOT NULL column and concludes the Var can
never be NULL.
But this assumption is wrong for old.* and new.* references. Because the
old tuple doesn't exist on INSERT, and the new tuple doesn't exist on
DELETE
I am not super familiar with this area, so I attempted to fix this as in
the patch attached.

Repro:

postgres=# CREATE TABLE t (id INT NOT NULL PRIMARY KEY, val INT);
INSERT INTO t VALUES (1, 10);

MERGE INTO t
USING (VALUES (1, 99), (2, 50)) AS s(id, val) ON t.id = s.id
WHEN MATCHED THEN UPDATE SET val = s.val
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.val)
RETURNING merge_action(),
          old.id IS NULL AS is_new_row;
CREATE TABLE
INSERT 0 1

 merge_action | is_new_row
--------------+------------
 UPDATE       | f
 INSERT       | f  -- (this should be true)
(2 rows)

MERGE 2


Thanks,
Satya

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

<div dir=3D"ltr">Hi hackers,<br><br>It appears the optimizer incorrectly si=
mplifies old.&lt;col&gt; IS NULL to FALSE in RETURNING clauses when the und=
erlying column has a NOT NULL constraint.<br><br>The issue is that var_is_n=
onnullable() in clauses.c doesn&#39;t check Var.varreturningtype. It sees a=
 NOT NULL column and concludes the Var can never be NULL.<div>But this assu=
mption is wrong for old.* and new.* references. Because the old tuple doesn=
&#39;t exist on INSERT, and the new tuple doesn&#39;t exist on DELETE=C2=A0=
<br>I am not super familiar with this area, so I attempted to fix this as i=
n the patch attached.</div><div><br></div><div>Repro:</div><div><br></div><=
div>postgres=3D# CREATE TABLE t (id INT NOT NULL PRIMARY KEY, val INT);<br>=
INSERT INTO t VALUES (1, 10);<br><br>MERGE INTO t<br>USING (VALUES (1, 99),=
 (2, 50)) AS s(id, val) ON <a href=3D"http://t.id">t.id</a> =3D <a href=3D"=
http://s.id">s.id</a><br>WHEN MATCHED THEN UPDATE SET val =3D s.val<br>WHEN=
 NOT MATCHED THEN INSERT VALUES (<a href=3D"http://s.id">s.id</a>, s.val)<b=
r>RETURNING merge_action(),<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <a href=
=3D"http://old.id">old.id</a> IS NULL AS is_new_row;<br>CREATE TABLE<br>INS=
ERT 0 1<br><br>=C2=A0merge_action | is_new_row <br>--------------+---------=
---<br>=C2=A0UPDATE =C2=A0 =C2=A0 =C2=A0 | f<br>=C2=A0INSERT =C2=A0 =C2=A0 =
=C2=A0 | f=C2=A0 -- (this should be true)<br>(2 rows)<br><br>MERGE 2</div><=
div><br></div><div><br></div><div>Thanks,</div><div>Satya<br><br></div></di=
v>

--000000000000ffb964064f0b6374--

--000000000000ffb966064f0b6376
Content-Type: application/octet-stream;
	name="0001-test-var_is_nonnullable-returning-old-new.patch"
Content-Disposition: attachment;
	filename="0001-test-var_is_nonnullable-returning-old-new.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnrtr1540>
X-Attachment-Id: f_mnrtr1540

ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvbWVyZ2Uub3V0IGIvc3JjL3Rl
c3QvcmVncmVzcy9leHBlY3RlZC9tZXJnZS5vdXQKaW5kZXggOWNiMWQ4NzAuLjQ0NDFlMzk5IDEw
MDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL21lcmdlLm91dAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL2V4cGVjdGVkL21lcmdlLm91dApAQCAtMTYwOSw2ICsxNjA5LDMxIEBAIExB
VEVSQUwgKFNFTEVDVCByX2FjdGlvbiwgcl90aWQsIHJfYmFsYW5jZSBGUk9NIG1lcmdlX2ludG9f
c3FfdGFyZ2V0KHNpZCwgYmFsYW5jCiAgSU5TRVJUICAgfCAgICAgNCB8ICAgICAgIDExMAogKDMg
cm93cykKIAorUk9MTEJBQ0s7CistLSBUZXN0IHRoYXQgb2xkL25ldyBjb2x1bW4gSVMgTlVMTCBp
cyBjb25zdGFudC1mb2xkZWQKKy0tIGZvciBOT1QgTlVMTCBjb2x1bW5zIGluIE1FUkdFIFJFVFVS
TklORworQkVHSU47CitNRVJHRSBJTlRPIHNxX3RhcmdldCB0CitVU0lORyBzcV9zb3VyY2UgcyBP
TiB0aWQgPSBzaWQKK1dIRU4gTUFUQ0hFRCBBTkQgdGlkID49IDIgVEhFTgorICAgIFVQREFURSBT
RVQgYmFsYW5jZSA9IHQuYmFsYW5jZSArIGRlbHRhCitXSEVOIE5PVCBNQVRDSEVEIFRIRU4KKyAg
ICBJTlNFUlQgKGJhbGFuY2UsIHRpZCkgVkFMVUVTIChiYWxhbmNlICsgZGVsdGEsIHNpZCkKK1dI
RU4gTUFUQ0hFRCBBTkQgdGlkIDwgMiBUSEVOCisgICAgREVMRVRFCitSRVRVUk5JTkcgbWVyZ2Vf
YWN0aW9uKCksCisgICAgb2xkLnRpZCBJUyBOVUxMIEFTIG9sZF90aWRfaXNfbnVsbCwKKyAgICBu
ZXcudGlkIElTIE5VTEwgQVMgbmV3X3RpZF9pc19udWxsLAorICAgIG9sZCBJUyBOVUxMIEFTIG9s
ZF9yb3dfaXNfbnVsbCwKKyAgICBuZXcgSVMgTlVMTCBBUyBuZXdfcm93X2lzX251bGw7CisgbWVy
Z2VfYWN0aW9uIHwgb2xkX3RpZF9pc19udWxsIHwgbmV3X3RpZF9pc19udWxsIHwgb2xkX3Jvd19p
c19udWxsIHwgbmV3X3Jvd19pc19udWxsIAorLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0t
LS0rLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0K
KyBERUxFVEUgICAgICAgfCBmICAgICAgICAgICAgICAgfCB0ICAgICAgICAgICAgICAgfCBmICAg
ICAgICAgICAgICAgfCB0CisgVVBEQVRFICAgICAgIHwgZiAgICAgICAgICAgICAgIHwgZiAgICAg
ICAgICAgICAgIHwgZiAgICAgICAgICAgICAgIHwgZgorIElOU0VSVCAgICAgICB8IHQgICAgICAg
ICAgICAgICB8IGYgICAgICAgICAgICAgICB8IHQgICAgICAgICAgICAgICB8IGYKKygzIHJvd3Mp
CisKIFJPTExCQUNLOwogLS0gRVhQTEFJTgogQ1JFQVRFIFRBQkxFIGV4X210YXJnZXQgKGEgaW50
LCBiIGludCkKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL21lcmdlLnNxbCBiL3Ny
Yy90ZXN0L3JlZ3Jlc3Mvc3FsL21lcmdlLnNxbAppbmRleCAyNjYwYjE5Zi4uNjc4OWZjZDUgMTAw
NjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL21lcmdlLnNxbAorKysgYi9zcmMvdGVzdC9y
ZWdyZXNzL3NxbC9tZXJnZS5zcWwKQEAgLTEwNjAsNiArMTA2MCwyNSBAQCBGUk9NIChWQUxVRVMg
KDEsIDAsIDApLCAoMywgMCwgMjApLCAoNCwgMTAwLCAxMCkpIEFTIHYoc2lkLCBiYWxhbmNlLCBk
ZWx0YSksCiBMQVRFUkFMIChTRUxFQ1Qgcl9hY3Rpb24sIHJfdGlkLCByX2JhbGFuY2UgRlJPTSBt
ZXJnZV9pbnRvX3NxX3RhcmdldChzaWQsIGJhbGFuY2UsIGRlbHRhKSkgbTsKIFJPTExCQUNLOwog
CistLSBUZXN0IHRoYXQgb2xkL25ldyBjb2x1bW4gSVMgTlVMTCBpcyBjb25zdGFudC1mb2xkZWQK
Ky0tIGZvciBOT1QgTlVMTCBjb2x1bW5zIGluIE1FUkdFIFJFVFVSTklORworQkVHSU47CitNRVJH
RSBJTlRPIHNxX3RhcmdldCB0CitVU0lORyBzcV9zb3VyY2UgcyBPTiB0aWQgPSBzaWQKK1dIRU4g
TUFUQ0hFRCBBTkQgdGlkID49IDIgVEhFTgorICAgIFVQREFURSBTRVQgYmFsYW5jZSA9IHQuYmFs
YW5jZSArIGRlbHRhCitXSEVOIE5PVCBNQVRDSEVEIFRIRU4KKyAgICBJTlNFUlQgKGJhbGFuY2Us
IHRpZCkgVkFMVUVTIChiYWxhbmNlICsgZGVsdGEsIHNpZCkKK1dIRU4gTUFUQ0hFRCBBTkQgdGlk
IDwgMiBUSEVOCisgICAgREVMRVRFCitSRVRVUk5JTkcgbWVyZ2VfYWN0aW9uKCksCisgICAgb2xk
LnRpZCBJUyBOVUxMIEFTIG9sZF90aWRfaXNfbnVsbCwKKyAgICBuZXcudGlkIElTIE5VTEwgQVMg
bmV3X3RpZF9pc19udWxsLAorICAgIG9sZCBJUyBOVUxMIEFTIG9sZF9yb3dfaXNfbnVsbCwKKyAg
ICBuZXcgSVMgTlVMTCBBUyBuZXdfcm93X2lzX251bGw7CitST0xMQkFDSzsKKwogLS0gRVhQTEFJ
TgogQ1JFQVRFIFRBQkxFIGV4X210YXJnZXQgKGEgaW50LCBiIGludCkKICAgV0lUSCAoYXV0b3Zh
Y3V1bV9lbmFibGVkPW9mZik7Cg==
--000000000000ffb966064f0b6376
Content-Type: application/octet-stream;
	name="0001-fix-var_is_nonnullable-returning-old-new.patch"
Content-Disposition: attachment;
	filename="0001-fix-var_is_nonnullable-returning-old-new.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnrtripx1>
X-Attachment-Id: f_mnrtripx1

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL2NsYXVzZXMuYyBiL3NyYy9i
YWNrZW5kL29wdGltaXplci91dGlsL2NsYXVzZXMuYwppbmRleCA5ZmIyNjZkMC4uZjI5MGE0Mzkg
MTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL2NsYXVzZXMuYworKysgYi9z
cmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9jbGF1c2VzLmMKQEAgLTQ2MzEsNiArNDYzMSwxNSBA
QCB2YXJfaXNfbm9ubnVsbGFibGUoUGxhbm5lckluZm8gKnJvb3QsIFZhciAqdmFyLCBOb3ROdWxs
U291cmNlIHNvdXJjZSkKIAlpZiAodmFyLT52YXJsZXZlbHN1cCAhPSAwKQogCQlyZXR1cm4gZmFs
c2U7CiAKKwkvKgorCSAqIEEgVmFyIHdpdGggVkFSX1JFVFVSTklOR19PTEQgb3IgVkFSX1JFVFVS
TklOR19ORVcgcmVmZXJzIHRvIHRoZSBPTEQgb3IKKwkgKiBORVcgdHVwbGUgaW4gYSBSRVRVUk5J
TkcgbGlzdC4gIFN1Y2ggYSBWYXIgY2FuIGJlIE5VTEwgZXZlbiBpZiB0aGUKKwkgKiB1bmRlcmx5
aW5nIGNvbHVtbiBpcyBkZWZpbmVkIE5PVCBOVUxMLCBiZWNhdXNlIHRoZSBPTEQgdHVwbGUgZG9l
c24ndAorCSAqIGV4aXN0IGZvciBJTlNFUlQgYW5kIHRoZSBORVcgdHVwbGUgZG9lc24ndCBleGlz
dCBmb3IgREVMRVRFLgorCSAqLworCWlmICh2YXItPnZhcnJldHVybmluZ3R5cGUgIT0gVkFSX1JF
VFVSTklOR19ERUZBVUxUKQorCQlyZXR1cm4gZmFsc2U7CisKIAkvKiBjb3VsZCB0aGUgVmFyIGJl
IG51bGxlZCBieSBhbnkgb3V0ZXIgam9pbnMgb3IgZ3JvdXBpbmcgc2V0cz8gKi8KIAlpZiAoIWJt
c19pc19lbXB0eSh2YXItPnZhcm51bGxpbmdyZWxzKSkKIAkJcmV0dXJuIGZhbHNlOwo=
--000000000000ffb966064f0b6376--