MIME-Version: 1.0
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
 <1064261.1722866217@sss.pgh.pa.us> <CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
 <1073701.1722870110@sss.pgh.pa.us>
In-Reply-To: <1073701.1722870110@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 5 Aug 2024 17:09:20 +0200
Message-ID: <CAFCRh-8RrdEpi+iWKuUcYSBhWJo5YJojXxKAFD0bW9aK_fxz2A@mail.gmail.com>
Subject: Re: UPDATE-FROM and INNER-JOIN
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8RrdEpi%2BiWKuUcYSBhWJo5YJojXxKAFD0bW9aK_fxz2A%40mail.gmail.com>
Precedence: bulk

On Mon, Aug 5, 2024 at 5:01=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dominique Devienne <ddevienne@gmail.com> writes:
> > The reason I find the restriction damaging is that `FROM t1, t2 WHERE
> > t1.c1 =3D t2.c2`
> > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
> > ON t1.c1 =3D t2.c2`
> > which IMHO better separates "filtering" from "joining" columns. FWIW.
>
> But the whole point of that syntax is to be explicit about which
> tables the ON clause(s) can draw from.  If we had a more complex
> FROM clause, with say three or four JOINs involved, which part of
> that would you argue the UPDATE target table should be implicitly
> inserted into?

Wherever an update-target-column was referenced in an ON clause.
Like SQLite used to support. I.e. possibly multiple times even, I guess.
Yes that does imply the update-target table in not explicitly named
in the FROM clause, specifically in the UPDATE-FROM case.

Personally I don't find that "offensive", it's explicitly part of an UPDATE=
.

> The only thing that would be non-ambiguous would
> be to require the target table to be explicitly named in FROM
> (and not treat that as a self-join, but as the sole scan of the
> target table).  Some other RDBMSes do it like that, but it seems
> like too much of a compatibility break for us.

The (old for now) SQLite way would be lifting a restriction,
so that wouldn't be a backward incompatible change IMHO

> Sadly, the SQL spec doesn't provide for a FROM clause in UPDATE,
> so they offer no guidance.

And that's precisely why SQLite and PostgreSQL agreeing on a precedent
would be nice.

> But I doubt we are going to change
> this unless the standard defines it and does so in a way that
> doesn't match what we're doing.

OK. Fair enough. I'm just expressing a personal opinion above.
Which the SQLite Forum OP also supports I'd guess. FWIW.

Thanks for your input Tom. --DD