pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16462: Update Statement destructive behaviour with joins
3+ messages / 3 participants
[nested] [flat]
* BUG #16462: Update Statement destructive behaviour with joins
@ 2020-05-26 11:28 PG Bug reporting form <noreply@postgresql.org>
2020-05-26 12:48 ` Re: BUG #16462: Update Statement destructive behaviour with joins David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2020-05-26 11:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: srivastava.adi24@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16462: Update Statement destructive behaviour with joins
2020-05-26 11:28 BUG #16462: Update Statement destructive behaviour with joins PG Bug reporting form <noreply@postgresql.org>
@ 2020-05-26 12:48 ` David Rowley <dgrowleyml@gmail.com>
2020-06-02 17:52 ` Re: BUG #16462: Update Statement destructive behaviour with joins Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2020-05-26 12:48 UTC (permalink / raw)
To: srivastava.adi24@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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#95ca7fad07b30fd0e22050...
David
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16462: Update Statement destructive behaviour with joins
2020-05-26 11:28 BUG #16462: Update Statement destructive behaviour with joins PG Bug reporting form <noreply@postgresql.org>
2020-05-26 12:48 ` Re: BUG #16462: Update Statement destructive behaviour with joins David Rowley <dgrowleyml@gmail.com>
@ 2020-06-02 17:52 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2020-06-02 17:52 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: srivastava.adi24@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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#95ca7fad07b30fd0e22050...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-06-02 17:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-26 11:28 BUG #16462: Update Statement destructive behaviour with joins PG Bug reporting form <noreply@postgresql.org>
2020-05-26 12:48 ` David Rowley <dgrowleyml@gmail.com>
2020-06-02 17:52 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox