Hi,
I noticed some weird update behaviour today in one of our development environments. In my opinion this appears to be a bug, but before reporting it I thought I should seek the opinions of others in the community. Maybe this is known and/or seen before?
The behaviour is visible when a subquery is used in an update. More specifically when a subquery is rejected by the parser when executed on it's own. This causes the where condition to be ignored entirely and thus the whole table updated rather than the parser throwing an error.
Example below that can be use to replicate:
update_bug=# create table dummy_data (rowid bigserial primary key, type int);
CREATE TABLE
update_bug=# insert into dummy_data (type) select 1 from generate_series(1,10000);
INSERT 0 10000
update_bug=# insert into dummy_data (type) select 2 from generate_series(1,10000);
INSERT 0 10000
update_bug=# select type, count(*) from dummy_data group by 1 order by 1;
type | count
------+-------
1 | 10000
2 | 10000
(2 rows)
update_bug=# select rowid from (select rowid as idnumber from dummy_data where type = 1) q;
ERROR: column "rowid" does not exist
LINE 1: select rowid from (select rowid as idnumber from dummy_data ...
update_bug=# update dummy_data set type = 3 where rowid in (select rowid from (select rowid as idnumber from dummy_data where type = 1) q);
UPDATE 20000
update_bug=# select type, count(*) from dummy_data group by 1 order by 1;
type | count
------+-------
3 | 20000
(1 row)
I have managed to replicate this behaviour on both 9.5.16 & 10.6 and will continue to test further.
Any thoughts/opinions are obviously welcomed.
Best wishes,
Harry