BUG #19689: MERGE INSERT accepts a set-returning function during PREPARE but fails at EXECUTE

Started by PG Bug reporting form2 months ago3 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 19689
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux x86-64
Description:

Description
-----------
A set-returning function can be placed in the VALUES expression of a MERGE
WHEN NOT MATCHED INSERT action. PREPARE accepts the statement, but EXECUTE
fails with "set-valued function called in context that cannot accept a set"
and inserts no rows. This violates the analysis/execution contract: an
expression that the MERGE action cannot execute should be rejected while the
statement is analyzed, rather than being accepted into a prepared plan that
deterministically fails only when executed.

Impact: Applications can successfully prepare an unusable MERGE statement
and encounter a localized execution failure later. The statement inserts no
rows. This was reproduced consistently on all tested versions.

Steps to reproduce
------------------
```sql
CREATE TABLE merge_target (id integer);
CREATE TABLE merge_source (id integer);
INSERT INTO merge_source VALUES (1);

PREPARE merge_srf AS
MERGE INTO merge_target AS t
USING merge_source AS s
ON false
WHEN NOT MATCHED THEN
INSERT VALUES (generate_series(1, 2));

EXECUTE merge_srf;

SELECT count(*) AS rows_after_execution
FROM merge_target;
```

Actual result
-------------
```text
ERROR: set-valued function called in context that cannot accept a set

rows_after_execution
----------------------
0
(1 row)
```

Expected result
---------------
PREPARE should reject the set-returning function because a MERGE INSERT
action cannot execute that expression in this context. It should not create
a prepared statement that is accepted successfully and then
deterministically fails during EXECUTE.

Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11. Inference: the MERGE INSERT action's specialized
expression handling does not propagate or enforce the restriction on
set-returning functions during analysis, leaving the executor to detect the
unsupported context.

#2Tender Wang
tndrwang@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19689: MERGE INSERT accepts a set-returning function during PREPARE but fails at EXECUTE

PG Bug reporting form <noreply@postgresql.org> 于2026年9月15日周二 17:36写道:

Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11. Inference: the MERGE INSERT action's specialized
expression handling does not propagate or enforce the restriction on
set-returning functions during analysis, leaving the executor to detect the
unsupported context.

I looked into this issue.

MERGE INSERT values are transformed using EXPR_KIND_VALUES_SINGLE,
which permits set-returning functions and sets
pstate->p_hasTargetSRFs. However, transformMergeStmt() later
unconditionally sets qry->hasTargetSRFs to false, and a MERGE action
target list is not expanded using a ProjectSet plan node.

Consequently, the SRF is accepted during PREPARE, but later fails
during executor expression initialization with:

ERROR: set-valued function called in context that cannot accept a set

The attached patch is one possible fix. It checks
pstate->p_hasTargetSRFs immediately after transforming the MERGE
INSERT values and reports an error during parse analysis. The check
is placed specifically in the CMD_INSERT case, so it does not affect
the other MERGE actions or set-returning functions used as the MERGE
data source.

I have not included a regression test in this version, since I would
first like to confirm whether rejecting SRFs in MERGE INSERT actions
is the intended behavior. If so, I can add a test in the next
version.

Thoughts?

--
Thanks,
Tender Wang

Attachments:

t253798_2
0001-Reject-set-returning-functions-in-MERGE-INSERT-actio.patchtext/plain; charset=US-ASCII; name=0001-Reject-set-returning-functions-in-MERGE-INSERT-actio.patchDownload+6-1
#3Joao Rito
joaovictor.rito@gmail.com
In reply to: Tender Wang (#2)
Re: BUG #19689: MERGE INSERT accepts a set-returning function during PREPARE but fails at EXECUTE

Hi Tender,

Thank you for the patch. I tried to review it. I may be wrong on some points, so please correct me.

What I did:
- Built current master (19ab0ba) with --enable-cassert, and reproduced the report. PREPARE succeeds and EXECUTE fails with "set-valued function called in context that cannot accept a set".
- Applied the patch (it applied cleanly) and rebuilt. PREPARE and direct MERGE now fail at parse time with "set-returning functions are not allowed in MERGE INSERT actions", and the error position points at the function call.
- Checked SRFs nested in an expression (`generate_series(1,2) + 1`), in a multi-column INSERT, and `unnest(...)`. All are rejected the same way.
- Checked that other cases did not change: SRF in WHEN MATCHED UPDATE and in WHEN NOT MATCHED BY SOURCE UPDATE (already rejected with the UPDATE message), SRF in the WHEN condition, in ON, and in RETURNING (same errors as before). An SRF in the USING subquery and an SRF inside a scalar sub-SELECT in INSERT VALUES still work as before. Plain INSERT with an SRF still works.
- Ran `make check`: all 243 tests passed.

Some small thoughts, which you may know better than me:
1. The patch has no regression test yet. I think a test in src/test/regress/sql/merge.sql with the PREPARE case from the report would be useful.
2. The error text is "set-returning functions are not allowed in MERGE INSERT actions". Other messages use the form "... not allowed in UPDATE", "... not allowed in MERGE WHEN conditions". Maybe a name from the existing ParseExprKindName() style would be more consistent, but I am not sure.
3. I did not check the back branches, so I do not know whether a backpatch is wanted.

I think the direction looks right to me, because I could not find a valid MERGE that reaches the executor error.

What I did not test:
- Back branches (17, 18). Only master was tested.
- Other build setups (no assertions, Windows, meson), and other test suites (`make check-world`, isolation, TAP).
- MERGE on partitioned tables, views, and inherited tables.
- MERGE inside PL/pgSQL or a SQL function body, and with parameters ($1) instead of constants.
- Other ways to produce a set in INSERT VALUES (ROWS FROM, SRF returning composite types, SRF with `OVERRIDING`).
- Documentation wording. I did not check the docs for any note about SRFs in MERGE.
- Code style (pgindent) and the placement of the new `#include`.

Thanks again for the patch,

Joao Rito