BUG #13465: multi update query use CTE, result & plan not equal, BUG?

The following bug has been logged on the website:

Bug reference: 13465
Logged by: digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS 6.x x64
Description:

PostgreSQL 9.5
when i use CTE update t1 two times, on problem : there is diffient results.
another problem : CTE update one table two times, which query exec first,
and how to isolation MVCC? why these result not same?

postgres=# create table t1(id int,info text);
CREATE TABLE
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# create table t3(id int,info text);
CREATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# insert into t2 values (2,'test'),(3,'abc');
INSERT 0 2
postgres=# insert into t3 values (1,'abc'),(2,'test');
INSERT 0 2
postgres=# update t1 set id=t2.id from t2 where t1.info=t2.info;
UPDATE 2
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,3) | 3 | abc
(0,4) | 2 | test
(2 rows)

postgres=# update t1 set info=t3.info from t3 where t1.id=t3.id;
UPDATE 1
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,3) | 3 | abc
(0,5) | 2 | test
(2 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
id | info
----+------
1 | abc
2 | test
(2 rows)

UPDATE 2
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,3) | 1 | abc
(0,4) | 2 | test
(2 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
c1 | c2 | c3 | c4
----+-----+----+-----
3 | abc | 1 | abc
(1 row)

postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,3) | 3 | abc
(0,4) | 1 | abc
(2 rows)

postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
QUERY PLAN

--------------------------------------------------------------------------------------
Nested Loop (cost=5471.31..374220205.11 rows=17045913600 width=72)
CTE t
-> Update on t1 (cost=751.71..2735.66 rows=130560 width=48)
-> Merge Join (cost=751.71..2735.66 rows=130560 width=48)
Merge Cond: (t1.info = t2_1.info)
-> Sort (cost=375.85..388.63 rows=5110 width=38)
Sort Key: t1.info
-> Seq Scan on t1 (cost=0.00..61.10 rows=5110
width=38)
-> Sort (cost=375.85..388.63 rows=5110 width=42)
Sort Key: t2_1.info
-> Seq Scan on t2 t2_1 (cost=0.00..61.10 rows=5110
width=42)
CTE t2
-> Update on t1 t1_1 (cost=751.71..2735.66 rows=130560 width=48)
-> Merge Join (cost=751.71..2735.66 rows=130560 width=48)
Merge Cond: (t1_1.id = t3.id)
-> Sort (cost=375.85..388.63 rows=5110 width=10)
Sort Key: t1_1.id
-> Seq Scan on t1 t1_1 (cost=0.00..61.10 rows=5110
width=10)
-> Sort (cost=375.85..388.63 rows=5110 width=42)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..61.10 rows=5110
width=42)
-> CTE Scan on t (cost=0.00..2611.20 rows=130560 width=36)
-> CTE Scan on t2 (cost=0.00..2611.20 rows=130560 width=36)
(23 rows)

postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
QUERY PLAN

--------------------------------------------------------------------------------------
Update on t1 (cost=3487.36..5471.31 rows=130560 width=48)
CTE t
-> Update on t1 t1_1 (cost=751.71..2735.66 rows=130560 width=48)
-> Merge Join (cost=751.71..2735.66 rows=130560 width=48)
Merge Cond: (t1_1.info = t2.info)
-> Sort (cost=375.85..388.63 rows=5110 width=38)
Sort Key: t1_1.info
-> Seq Scan on t1 t1_1 (cost=0.00..61.10 rows=5110
width=38)
-> Sort (cost=375.85..388.63 rows=5110 width=42)
Sort Key: t2.info
-> Seq Scan on t2 (cost=0.00..61.10 rows=5110
width=42)
-> Merge Join (cost=751.71..2735.66 rows=130560 width=48)
Merge Cond: (t1.id = t3.id)
-> Sort (cost=375.85..388.63 rows=5110 width=10)
Sort Key: t1.id
-> Seq Scan on t1 (cost=0.00..61.10 rows=5110 width=10)
-> Sort (cost=375.85..388.63 rows=5110 width=42)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..61.10 rows=5110 width=42)
(19 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2

postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
postgres=# analyze t3;
ANALYZE

postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
c1 | c2 | c3 | c4
----+------+----+------
2 | test | 2 | test
(1 row)

postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,7) | 2 | test
(0,8) | 2 | test
(2 rows)

postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
QUERY PLAN

----------------------------------------------------------------------------------
Nested Loop (cost=4.21..4.35 rows=4 width=72)
CTE t
-> Update on t1 (cost=0.00..2.10 rows=2 width=20)
-> Nested Loop (cost=0.00..2.10 rows=2 width=20)
Join Filter: (t1.info = t2_1.info)
-> Seq Scan on t1 (cost=0.00..1.02 rows=2 width=10)
-> Materialize (cost=0.00..1.03 rows=2 width=14)
-> Seq Scan on t2 t2_1 (cost=0.00..1.02 rows=2
width=14)
CTE t2
-> Update on t1 t1_1 (cost=0.00..2.10 rows=2 width=20)
-> Nested Loop (cost=0.00..2.10 rows=2 width=20)
Join Filter: (t1_1.id = t3.id)
-> Seq Scan on t1 t1_1 (cost=0.00..1.02 rows=2 width=10)
-> Materialize (cost=0.00..1.03 rows=2 width=14)
-> Seq Scan on t3 (cost=0.00..1.02 rows=2
width=14)
-> CTE Scan on t (cost=0.00..0.04 rows=2 width=36)
-> CTE Scan on t2 (cost=0.00..0.04 rows=2 width=36)
(17 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# analyze t1;
ANALYZE
postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
QUERY PLAN

-----------------------------------------------------------------------------
Update on t1 (cost=2.10..4.21 rows=2 width=20)
CTE t
-> Update on t1 t1_1 (cost=0.00..2.10 rows=2 width=20)
-> Nested Loop (cost=0.00..2.10 rows=2 width=20)
Join Filter: (t1_1.info = t2.info)
-> Seq Scan on t1 t1_1 (cost=0.00..1.02 rows=2 width=10)
-> Materialize (cost=0.00..1.03 rows=2 width=14)
-> Seq Scan on t2 (cost=0.00..1.02 rows=2
width=14)
-> Nested Loop (cost=0.00..2.10 rows=2 width=20)
Join Filter: (t1.id = t3.id)
-> Seq Scan on t1 (cost=0.00..1.02 rows=2 width=10)
-> Materialize (cost=0.00..1.03 rows=2 width=14)
-> Seq Scan on t3 (cost=0.00..1.02 rows=2 width=14)
(13 rows)

postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
id | info
----+------
1 | abc
2 | test
(2 rows)

UPDATE 2
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,3) | 1 | abc
(0,4) | 2 | test
(2 rows)