public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: UPDATE-FROM and INNER-JOIN
Date: Mon, 5 Aug 2024 17:09:20 +0200
Message-ID: <CAFCRh-8RrdEpi+iWKuUcYSBhWJo5YJojXxKAFD0bW9aK_fxz2A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
	<[email protected]>
	<CAFCRh-8-DsfASidDtLPSkx10JMer_AuYOrXSa8HrqQgqEKvyNA@mail.gmail.com>
	<[email protected]>

On Mon, Aug 5, 2024 at 5:01 PM Tom Lane <[email protected]> wrote:
>
> Dominique Devienne <[email protected]> writes:
> > The reason I find the restriction damaging is that `FROM t1, t2 WHERE
> > t1.c1 = t2.c2`
> > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
> > ON t1.c1 = 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: UPDATE-FROM and INNER-JOIN
  In-Reply-To: <CAFCRh-8RrdEpi+iWKuUcYSBhWJo5YJojXxKAFD0bW9aK_fxz2A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox