BUG #18223: There is a confusing result where an update statement can reference itself and execute successfully. - Mailing list pgsql-bugs

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@163.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