Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan

Hi Tender Wang,

Thank you very much for your reply. When I first ran this test case, I did not observe any error. However, I am currently unable to reproduce the issue. I apologize for submitting a false report.

Best,
Chi

ZhangChi
798604270(at)qq(dot)com

Original


From: Tender Wang <tndrwang(at)gmail(dot)com&gt;
Date: 2025-12-26 15:20
To: ZhangChi <798604270(at)qq(dot)com&gt;
Cc: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
Subject: Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan

ZhangChi <798604270(at)qq(dot)com&gt; 于2025年12月26日周五 15:00写道：
Hi,

Has anyone had a look at this report?

Best,
Chi

Original

From: PG Bug reporting form <noreply(at)postgresql(dot)org&gt;
Date: 2025-12-16 11:06
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
Cc: 798604270 <798604270(at)qq(dot)com&gt;
Subject: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan

The&nbsp;following&nbsp;bug&nbsp;has&nbsp;been&nbsp;logged&nbsp;on&nbsp;the&nbsp;website:

Bug&nbsp;reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19356
Logged&nbsp;by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chi&nbsp;Zhang
Email&nbsp;address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 798604270(at)qq(dot)com
PostgreSQL&nbsp;version:&nbsp;17.6
Operating&nbsp;system:&nbsp;&nbsp;&nbsp;ubuntu&nbsp;24.04&nbsp;with&nbsp;docker
Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Hi,

In&nbsp;the&nbsp;following&nbsp;test&nbsp;case,&nbsp;the&nbsp;WHERE&nbsp;condition&nbsp;of&nbsp;UPDATE&nbsp;is&nbsp;true;&nbsp;however,
the&nbsp;value&nbsp;in&nbsp;the&nbsp;table&nbsp;is&nbsp;not&nbsp;updated:

```
SET&nbsp;plan_cache_mode&nbsp;=&nbsp;force_generic_plan;
CREATE&nbsp;TABLE&nbsp;t2(c0&nbsp;int4range);
INSERT&nbsp;INTO&nbsp;t2(c0)&nbsp;VALUES('[-1372225904,-410785016)'::int4range)&nbsp;ON&nbsp;CONFLICT
DO&nbsp;NOTHING;
PREPARE&nbsp;prepare_query&nbsp;(text,&nbsp;int4range,&nbsp;text,&nbsp;text,&nbsp;inet)&nbsp;AS&nbsp;UPDATE&nbsp;t2&nbsp;SET
c0=DEFAULT&nbsp;WHERE&nbsp;NOT&nbsp;(((((CAST($1&nbsp;AS
int4range))&amp;&amp;(((t2.c0)-($2)))))AND((quote_literal($3)&nbsp;SIMILAR&nbsp;TO
(($4)||($5))))));
EXECUTE&nbsp;prepare_query('-2073583882',&nbsp;'[-1190073754,1650158810)'::int4range,
'?',&nbsp;'j&amp;Q',&nbsp;'171.191.143.34');
DEALLOCATE&nbsp;prepare_query;
SELECT&nbsp;DISTINCT&nbsp;*&nbsp;FROM&nbsp;t2;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;c0
--------------------------
&nbsp;[-1372225904,-410785016)
(1&nbsp;row)
```

This&nbsp;is&nbsp;the&nbsp;result&nbsp;of&nbsp;the&nbsp;condition:
```
PREPARE&nbsp;prepare_query&nbsp;(text,&nbsp;int4range,&nbsp;text,&nbsp;text,&nbsp;inet)&nbsp;AS&nbsp;SELECT
((((quote_literal($3)&nbsp;SIMILAR&nbsp;TO&nbsp;(($4)||($5))))));
EXECUTE&nbsp;prepare_query('-2073583882',&nbsp;'[-1190073754,1650158810)'::int4range,
'?',&nbsp;'j&amp;Q',&nbsp;'171.191.143.34');
&nbsp;?column?
----------
&nbsp;f
(1&nbsp;row)
```

The&nbsp;equivalent&nbsp;normal&nbsp;UPDATE&nbsp;can&nbsp;execute&nbsp;correctly&nbsp;`UPDATE&nbsp;t2&nbsp;SET&nbsp;c0=DEFAULT
WHERE&nbsp;NOT&nbsp;(((((CAST('-2073583882'::text&nbsp;AS
int4range))&amp;&amp;(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
SIMILAR&nbsp;TO&nbsp;(('j&amp;Q'::text)||('171.191.143.34'::inet))))));`

I run your SQL on 17.6, I got this:
postgres=# select version();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;version
---------------------------------------------------------------------------------------------------------
&nbsp;PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit
(1 row)

postgres=# 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))&amp;&amp;(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&amp;Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
SET
CREATE TABLE
INSERT 0 1
PREPARE
ERROR: &nbsp;malformed range literal: "-2073583882"
DETAIL: &nbsp;Missing left parenthesis or bracket.
DEALLOCATE
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c0
--------------------------
&nbsp;[-1372225904,-410785016)
(1 row)

EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&amp;Q', '171.191.143.34');
This SQL reported error: malformed range literal: "-2073583882", so it did nothing.

--
Thanks,
Tender Wang