BUG #16462: Update Statement destructive behaviour with joins
The following bug has been logged on the website:
Bug reference: 16462
Logged by: Aditya Srivastava
Email address: srivastava.adi24@gmail.com
PostgreSQL version: 9.6.2
Operating system: MAC OSX
Description:
Let's say I have a table orange and a temp table temp, and i want to update
the records after joining the columns in temp table. I used the following
syntax to update the records which ended up updating the entire table
"orange".
UPDATE orange
SET fruit_flag = 'okay'
FROM temp as t
INNER JOIN portal_users p on t.fruit_id = p.fruit_id
WHERE p.id = '123';
I know that the correct syntax should be the following but judging from the
destructive nature of this query i honestly feel we should throw validation
error if the above syntax is not correct.
UPDATE orange
SET fruit_flag = 'okay'
FROM temp t
WHERE t.fruit_id = orange.fruit_id
Thanks in advance.
On Wed, 27 May 2020 at 00:15, PG Bug reporting form
<noreply@postgresql.org> wrote:
Let's say I have a table orange and a temp table temp, and i want to update
the records after joining the columns in temp table. I used the following
syntax to update the records which ended up updating the entire table
"orange".UPDATE orange
SET fruit_flag = 'okay'
FROM temp as t
INNER JOIN portal_users p on t.fruit_id = p.fruit_id
WHERE p.id = '123';I know that the correct syntax should be the following but judging from the
destructive nature of this query i honestly feel we should throw validation
error if the above syntax is not correct.
That's an unfortunate mistake.
Unfortunately, SQL is full of these trip hazards. The join syntax was
once revised to try to reduce the pain of accidental cartesian joins
by missed join clauses in the WHERE clause. The JOIN ON syntax was
born because of that. Maybe we didn't get the UPDATE FROM syntax
perfect, as it does still allow users to easily miss the join clause,
but I'm not all that sure what we can realistically do about that, It
does not seem like a good thing to go raising an error as it might
block some genuine use case.
Thinking back, there was some discussion around looking for ways to
block such mistakes in [1]/messages/by-id/20170202175023.GA30233@localhost. As I recall it was going to be an
extension that created triggers to block mistakes like this. However,
that thread has not moved in over 3 years.
[1]: /messages/by-id/20170202175023.GA30233@localhost
David
On Wed, May 27, 2020 at 12:48:19AM +1200, David Rowley wrote:
On Wed, 27 May 2020 at 00:15, PG Bug reporting form
<noreply@postgresql.org> wrote:Let's say I have a table orange and a temp table temp, and i want to update
the records after joining the columns in temp table. I used the following
syntax to update the records which ended up updating the entire table
"orange".UPDATE orange
SET fruit_flag = 'okay'
FROM temp as t
INNER JOIN portal_users p on t.fruit_id = p.fruit_id
WHERE p.id = '123';I know that the correct syntax should be the following but judging from the
destructive nature of this query i honestly feel we should throw validation
error if the above syntax is not correct.That's an unfortunate mistake.
Unfortunately, SQL is full of these trip hazards. The join syntax was
once revised to try to reduce the pain of accidental cartesian joins
by missed join clauses in the WHERE clause. The JOIN ON syntax was
born because of that. Maybe we didn't get the UPDATE FROM syntax
perfect, as it does still allow users to easily miss the join clause,
but I'm not all that sure what we can realistically do about that, It
does not seem like a good thing to go raising an error as it might
block some genuine use case.Thinking back, there was some discussion around looking for ways to
block such mistakes in [1]. As I recall it was going to be an
extension that created triggers to block mistakes like this. However,
that thread has not moved in over 3 years.
I have alawys wanted a 'novice' mode which warned/errored on such things.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee