RE: BUG #18223: There is a confusing result where an update statement can reference itself and execute successfully.

Hello! A little confused by the statement:
You do UPDATE and FROM without WHERE, it's about the same as CROSS JOIN

explain
select ( select tt3.c6 from tb1 tt2 where tt1.c1 = tt2.c1 )
from tb3 tt1
cross join tb3 tt3

Nested Loop (cost=0.00..1759763.36 rows=184900 width=8)
-> Seq Scan on tb3 tt1 (cost=0.00..14.30 rows=430 width=4)
-> Materialize (cost=0.00..16.45 rows=430 width=8)
-> Seq Scan on tb3 tt3 (cost=0.00..14.30 rows=430 width=8)
SubPlan 1
-> Bitmap Heap Scan on tb1 tt2 (cost=4.16..9.50 rows=2 width=8)
Recheck Cond: (tt1.c1 = c1)
-> Bitmap Index Scan on idx_tb1_c1 (cost=0.00..4.16 rows=2 width=0)
Index Cond: (c1 = tt1.c1)

You're sure it's exactly what you need!

-----Original Message-----
From: PG Bug reporting form <noreply(at)postgresql(dot)org>
Sent: Monday, December 4, 2023 9:28 AM
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dafoer_x(at)163(dot)com
Subject: BUG #18223: There is a confusing result where an update statement can reference itself and execute successfully.

The following bug has been logged on the website:

Bug reference: 18223
Logged by: There is a confusing result where an update statement can reference itself
and execute successfully.
Email address: dafoer_x(at)163(dot)com
PostgreSQL version: 14.10
Operating system: centos_x86
Description:

The problem SQL is as follows, and the reproduction method is provided below.

test1=# explain UPDATE tb3 tt1
SET c6 = (
SELECT tt3.c6
FROM tb1 tt2
WHERE tt1.c1 = tt2.c1
)
FROM tb3 tt3;
QUERY PLAN

---------------------------------------------------------------------------------------
Update on tb3 tt1 (cost=0.00..1759763.36 rows=0 width=0)
-> Nested Loop (cost=0.00..1759763.36 rows=184900 width=20)
-> Seq Scan on tb3 tt1 (cost=0.00..14.30 rows=430 width=10)
-> Materialize (cost=0.00..16.45 rows=430 width=14)
-> Seq Scan on tb3 tt3 (cost=0.00..14.30 rows=430
width=14)
SubPlan 1
-> Bitmap Heap Scan on tb1 tt2 (cost=4.16..9.50 rows=2
width=8)
Recheck Cond: (tt1.c1 = c1)
-> Bitmap Index Scan on idx_tb1_c1 (cost=0.00..4.16
rows=2 width=0)
Index Cond: (c1 = tt1.c1)
(10 rows)

drop table tb1;
drop table tb2;

CREATE TABLE tb1 (
c0 int,
c1 int,
c2 text,
c3 text,
c4 date,
c5 date,
c6 timestamp,
c7 timestamp,
c8 numeric,
c9 numeric) ;
alter table tb1 alter column c0 drop not null; CREATE INDEX idx_tb1_c1 ON tb1(c1); CREATE INDEX idx_tb1_c3 ON tb1(c3); CREATE INDEX idx_tb1_c5 ON tb1(c5); CREATE INDEX idx_tb1_c7 ON tb1(c7); CREATE INDEX idx_tb1_c9 ON tb1(c9); INSERT INTO tb1 VALUES (2, 8, 'iqeddsjatqqpgwacmrrmjwcfdmusscpxdahbrka',
'foo', '2012-01-14 07:43:11', '1985-05-01', '1971-05-24 11:10:21',
'2030-09-20 02:22:26.042113', 1.23456789123457e+43, 0.476699829101562) , (8, 7, 'foo', NULL, '2008-03-13', '2010-09-08', '2028-09-17', '1985-04-14', -1.23456789123457e+39, 1.23456789123457e+39) , (5, 0, 'qeddsjatqqpgwacmrrmjwcfdmusscpxdahbrkastfrhz', 'foo', '1990-05-21',
'1977-12-25 14:26:18', '2003-03-26 17:50:08', '2000-11-24 03:18:56.014647', -625934336, -1.23456789123457e+43) , (3, 6, NULL, 'foo', '1978-07-05', '2024-07-08', '2023-10-12', '1986-02-21 12:36:21', 4.45968953100363e+18,
-1.23456789123457e+39) , (NULL, 5, 'e', 'foo', '2033-04-02', '1990-04-05',
'2008-02-19 17:47:36.059051', '1975-11-18 17:45:52', 1.23456789123457e+30,
1.23456789123457e+30) , (3, NULL, NULL, NULL, '1997-04-02', '2023-06-20 03:00:59', '2014-03-01', '1991-11-21 09:42:37.007626', 1.23456789123457e+43,
1.23456789123457e+39) , (7, 2, 'ddsj', NULL, '1998-08-03', '2004-11-11 11:13:14', '1977-06-13 02:40:19.061846', NULL, -4.10964965761409e+125,
1.23456789123457e+43) , (1, 9, 'foo', 'foo', '1996-02-28', '1984-08-18',
'1996-08-14 16:52:29.027074', '1984-03-12', -1.23456789123457e+25,
0.947128295898438) , (2, 4, 'dsjatqqp', 'bar', '1995-08-17 06:12:42',
'1977-04-08 01:19:06', '2006-11-09 23:20:44', '2005-07-06 06:09:48.050867', 9.0966796875e+80, -2.32086181640625e+80) , (3, 7, NULL, 'bar', '1982-04-17 05:49:40', '1994-10-02', '1973-10-26', '1977-09-08 13:39:07', 1.23456789123457e+43, 1.23456789123457e-09) ;

CREATE TABLE tb3 (
c0 int,
c1 int,
c2 text,
c3 text,
c4 date,
c5 date,
c6 timestamp,
c7 timestamp,
c8 numeric,
c9 numeric);
alter table tb3 alter column c0 drop not null; CREATE INDEX idx_tb3_c1 ON tb3(c1); CREATE INDEX idx_tb3_c3 ON tb3(c3); CREATE INDEX idx_tb3_c5 ON tb3(c5); CREATE INDEX idx_tb3_c7 ON tb3(c7); CREATE INDEX idx_tb3_c9 ON tb3(c9); INSERT INTO tb3 VALUES (5, NULL, NULL, 'acmrrmjwcfdmusscpxdahbrkastfrhzzdrldtkwxjgufkrifuexwbyiqwzzhdvgvjmqbgesagtwviffcourqjszmkwlpgkfxmvzijfqlnzgvywqpgviyvbesdfntmstbrygsnebkfwfzuxyqabpxkqqmhaudernvhmpjjdz',
'2032-11-20 12:13:22', '2013-11-28 16:23:36', '1977-05-04 12:25:28',
'2010-06-15 11:51:42.009325', -1.23456789123457e-09, -1008992256) , (9, 5, 'cmrrmjwcfdmusscpxdahbrkastfrhzzdrldtkwxjgufkrifuexwbyiqwzzhdvgvjmqbgesagtwviffcourqjszmkwlpgkfxmvzijfql',
'mrrmjwcfdmusscpxdahbrkastfrhzzdrldtk', '2031-11-07 02:15:41', '1996-09-04 01:05:43', '1977-12-07 03:39:43.046601', '1977-08-10', -0.123456789123457,
-1.23456789123457e-09) , (NULL, 9, 'foo', 'rrm', '1990-03-03 18:51:15', '1983-03-04', '1993-05-07 20:37:08.019895', '1987-10-18', 1.23456789123457e-09, 1.23456789123457e+39) , (9, 0, NULL, 'rmjw',
'2003-11-13 10:50:30', '2003-01-03', '2016-11-08 18:58:40', '1971-12-24 00:50:16', 1.23456789123457e+44, -1.29759964263612e+18) , (NULL, 2, 'bar', NULL, '2012-01-28 21:08:09', '2010-03-21', '2011-11-12 21:49:34.033953', '2035-02-24', -1.23456789123457e+39, -1.23456789123457e+25) , (0, NULL, 'bar', 'mjwcfdmusscpxdahbrkastfrhzzdrldtkwxjgufkrifuexwbyiqwzzhdvgvjmqbgesagtwviffcourqjszmkwlpgkfxmvzijfqlnzgvywqpgviyvbesdfntmstbrygsnebkfwfzuxyqabpxkqqmhaudernvhmpjjdzfhkxldcvhr',
'1982-11-08', '2018-08-12', '1982-08-27 13:44:17', NULL, -1805320192,
-0.123456789123457) , (1, 6, 'foo',
'jwcfdmusscpxdahbrkastfrhzzdrldtkwxjgufkrifuexwbyiqwzzhdvgvjmqbgesagtwviffcourqjszmkwlpgkfxmvzijfqlnzgvywqpgviyvbesdfntmstbrygsnebkfwfzuxyqabpxkqqmhaudernvhmpjjdzfhkxldcvhrcwgxtlubryvkjxmmns',
'2029-08-25', '2029-10-17', '2032-11-05 08:24:18', '1973-06-03 17:06:34.030032', -1.23456789123457e+25, NULL) , (NULL, 6, 'wcfdmussc', 'cfdmu', '1982-03-08 06:50:51', '2000-10-14 00:24:11', '2008-12-15 19:05:48', '1985-08-03 21:58:08.061038', 0.185836791992188,
-5.67459106402059e+125) , (0, 4, NULL, 'bar', '2005-01-28', '2022-05-18 08:12:28', '2015-04-26 23:38:22', '1983-06-14 14:36:36.011877', 1.23456789123457e+25, -0.123456789123457) , (NULL, 4, 'bar', 'foo',
'1972-09-18 16:40:20', '2013-07-10 04:51:05', '1988-01-22 21:05:37',
'1975-02-06 09:10:30.062407', -1.23456789123457e+25, 1.23456789123457e+25) ;

BEGIN ;
SELECT c6 FROM tb3 ORDER BY c6 ;

UPDATE tb3 tt1
SET c6 = (
SELECT tt3.c6
FROM tb1 tt2
WHERE tt1.c1 = tt2.c1
)
FROM tb3 tt3;

SELECT c6 FROM tb3 ORDER BY c6 ;
ROLLBACK;

result：
test1=# BEGIN ;
BEGIN
test1=*# SELECT c6 FROM tb3 ORDER BY c6 ;
c6
----------------------------
1977-05-04 12:25:28
1977-12-07 03:39:43.046601
1982-08-27 13:44:17
1988-01-22 21:05:37
1993-05-07 20:37:08.019895
2008-12-15 19:05:48
2011-11-12 21:49:34.033953
2015-04-26 23:38:22
2016-11-08 18:58:40
2032-11-05 08:24:18
(10 rows)

test1=*#
test1=*# UPDATE tb3 tt1
test1-*# SET c6 = (
test1(*# SELECT tt3.c6
test1(*# FROM tb1 tt2
test1(*# WHERE tt1.c1 = tt2.c1
test1(*# )
test1-*# FROM tb3 tt3;
UPDATE 10
test1=*#
test1=*# -- 数据不一致
test1=*# SELECT c6 FROM tb3 ORDER BY c6 ;
c6
---------------------
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28
1977-05-04 12:25:28


(10 rows)

test1=*# ROLLBACK;
ROLLBACK