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

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;&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))))));`