Re: Issue with SQL query causing unintended consequences in database

On Fri, Jan 6, 2023 at 1:46 PM prevot morvan <prevotmorvan(at)yahoo(dot)fr> wrote:
>
> Dear PostgreSQL bug team,
>
> I am writing to report an issue I encountered when running an SQL query
on my database.
>
> I ran the following query:
>
> UPDATE
> nf_job
> SET
> status = 'TO_DO',
> error_message = NULL
> FROM
> nf_job n
> JOIN formality f ON n.formality_id = f.id
> WHERE
> f.liasse_number IN ('J00011156148', 'J00011416104');
>
>
> To my surprise, this query erased ALL of the status and error_messages in
the nf_jobs table, rather than just changing two lines as I had intended.
>
> Upon reviewing the documentation for SELECT and UPDATE at the following
links:
>
> SELECT: https://www.postgresql.org/docs/current/sql-select.html
> UPDATE: https://www.postgresql.org/docs/current/sql-update.html
>
> I noticed that the SELECT statement allows for the use of "JOIN", but the
UPDATE statement does not. This leads me to believe that it should have
been a syntax error to include "JOIN" in an UPDATE statement. However, no
syntax error was thrown and the query seemed to attempt to run anyway,
resulting in the unintended consequences described above.
>
> I would be grateful if the team could take a look into this issue and let
me know if there is any way to prevent this from happening in the future.
>
> Thank you in advance for your help.
>
> Sincerely,
> Émile PRÉVOT

This is not a bug. The statement did what it should do. Notice that in
https://www.postgresql.org/docs/current/sql-update.html , it mentions:

> from_item
>
> A table expression allowing columns from other tables to appear in the
WHERE condition and update expressions. This uses the same syntax as the
FROM clause of a SELECT statement; for example, an alias for the table name
can be specified. *Do not repeat the target table as a from_item unless you
intend a self-join* (in which case it must appear with an alias in the
from_item).

So the statement did a self-join of the updated table with itself.
You probably wanted to run this query instead (without repeating the
updated table in the FROM clause and moving the ON condition to the WHERE
clause):

UPDATE
nf_job
SET
status = 'TO_DO',
error_message = NULL
FROM
formality f
WHERE
f.liasse_number IN ('J00011156148', 'J00011416104')
AND nf_job.formality_id = f.id ;