Thread: 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]. 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] https://www.postgresql.org/message-id/flat/20170202175023.GA30233%40localhost#95ca7fad07b30fd0e2205075f3fc04c5

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.
> 
> [1] https://www.postgresql.org/message-id/flat/20170202175023.GA30233%40localhost#95ca7fad07b30fd0e2205075f3fc04c5

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