From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 798604270@qq.com
Subject: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
Date: Tue, 16 Dec 2025 03:06:11 +0000
Message-ID: <19356-931e11b7f7a4db2b@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19356
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, the WHERE condition of UPDATE is true; however,
the value in the table is not updated:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
c0
--------------------------
[-1372225904,-410785016)
(1 row)
```
This is the result of the condition:
```
PREPARE prepare_query (text, int4range, text, text, inet) AS SELECT
((((quote_literal($3) SIMILAR TO (($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
?column?
----------
f
(1 row)
```
The equivalent normal UPDATE can execute correctly `UPDATE t2 SET c0=DEFAULT
WHERE NOT (((((CAST('-2073583882'::text AS
int4range))&&(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
SIMILAR TO (('j&Q'::text)||('171.191.143.34'::inet))))));`
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 798604270@qq.com
Subject: Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
In-Reply-To: <19356-931e11b7f7a4db2b@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox