Bug: var_is_nonnullable() gives wrong results for old/new in RETURNING
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
Attachments:
0001-test-var_is_nonnullable-returning-old-new.patchapplication/octet-stream; name=0001-test-var_is_nonnullable-returning-old-new.patchDownload+42-0
0001-fix-var_is_nonnullable-returning-old-new.patchapplication/octet-stream; name=0001-fix-var_is_nonnullable-returning-old-new.patchDownload+9-0
SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> 于2026年4月10日周五 02:43写道：
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.
Yes, the current var_is_nonnullable() ignores this case. The
attached patch seems ok to me.
Add Richard to the cc list. He may know more about this.
--
Thanks,
Tender Wang
On Fri, Apr 10, 2026 at 10:30 AM Tender Wang <tndrwang@gmail.com> wrote:
SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> 于2026年4月10日周五 02:43写道：
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
Nice catch.
Yes, the current var_is_nonnullable() ignores this case. The
attached patch seems ok to me.
The patch also LGTM. I also checked if has_notnull_forced_var() has
the same issue, but it doesn't: Vars with non-default returning type
only appear in the RETURNING clause, so they never show up in WHERE/ON
clauses.
- Richard
On Fri, Apr 10, 2026 at 2:48 PM Richard Guo <guofenglinux@gmail.com> wrote:
The patch also LGTM. I also checked if has_notnull_forced_var() has
the same issue, but it doesn't: Vars with non-default returning type
only appear in the RETURNING clause, so they never show up in WHERE/ON
clauses.
I pushed the patch after a bit cosmetic tweaks. I also decided to put
the test cases in returning.sql, which I think is a better place.
Thanks for the report and the patch.
- Richard