The following bug has been logged on the website:
Bug reference: 17689
Logged by: Eugene Pliskin
Email address: eugene.pliskin@gmail.com
PostgreSQL version: 14.5
Operating system: Windows 10
Description:
The sample code below contains two UPDATE steps in a chain of common table
expressions (CTE).
First UPDATE step "t2b" modifies a row in a sample table.
Later down an unconditional UPDATE step "t5" fails to affect that row.
If step "t2a" is uncommented instead of "t2b" then step "t5" performs as
expected .
create table table1(id int, value1 int);
insert into table1 values
(1, 10),
(2, 20)
;
select * from table1 order by 1;
-- id value1
-- 1 10
-- 2 20
do $$declare -- anonymous plpgsql block
n2 int; n5 int;
begin
with t1 as ( -- t1 has one row with (id = 1)
select * from table1 where id = 1
), t2 as (
-- Experiment (t2a) does nothing:
-- select * from t1
-- Experiment (t2b) updates the first row of table1 with (value1 = 11):
update table1 set value1 = 11
from t1
where table1.id = t1.id
returning table1.id
), t3 as (
select count(*) as n2 from t2 -- n2 = 1
), t4 as ( -- t4 = table1, two rows
select * from table1
), t5 as ( -- expect to update both rows of table1 with (value1 = 22)
and so it does after t2a
-- but only one row gets updated after t2b
update table1 set value1 = 22
from t4
where table1.id = t4.id
returning table1.id
), t6 as (
select count(*) as n5 from t5 -- expect n5 = 2
) select t3.n2, t6.n5
from t3, t6
into n2, n5;
raise notice 'n2: %, n5: %', n2, n5; -- expected (n2: 1, n5: 2) but have
(n2: 1, n5: 1) in t2b
end$$ ;
-- t2a: (n5 = 2) as expected
-- t2b: unexpected (n5 = 1)
select * from table1 order by 1;
-- t2a: expected result, both rows have (value1 = 22)
-- id value1
-- 1 22
-- 2 22
--
-- t2b: unexpected (value1 = 11) in the first row
-- id value1
-- 1 11
-- 2 22
--------------------------------------------------