From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dominique Devienne <ddevienne@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: UPDATE-FROM and INNER-JOIN
In-reply-to: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
References: <CAFCRh-_Hm87rHcGNvScJ8AW9wdOekBnCP3yfGMWQdyoQYxHcZw@mail.gmail.com>
Comments: In-reply-to Dominique Devienne <ddevienne@gmail.com>
	message dated "Mon, 05 Aug 2024 13:33:36 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1064260.1722866217.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 05 Aug 2024 09:56:57 -0400
Message-ID: <1064261.1722866217@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1064261.1722866217%40sss.pgh.pa.us>
Precedence: bulk

Dominique Devienne <ddevienne@gmail.com> writes:
> In https://sqlite.org/forum/forumpost/df23d80682
> Richard Hipp (Mr SQLite) shows an example of something
> that used to be supported by SQLite, but then wasn't, to be
> compatible with PostgreSQL.

For the archives' sake:

CREATE TABLE t1(aa INT,	bb INT);
CREATE TABLE t2(mm INT,	nn INT);
CREATE TABLE t3(xx INT,	yy INT);
UPDATE t1 SET bb =3D mm+xx FROM t2 INNER JOIN t3 ON nn=3Dxx AND mm=3Daa;

yields

ERROR:  column "aa" does not exist
LINE 1: ... t1 SET bb =3D mm+xx FROM t2 INNER JOIN t3 ON nn=3Dxx AND mm=3D=
aa;
                                                                    ^
DETAIL:  There is a column named "aa" in table "t1", but it cannot be refe=
renced from this part of the query.


> Thus I'm curious as to why PostgreSQL refuses the first formulation.
> Could anyone provide any insights? Thanks, --DD

This seems correct to me.  The scope of the ON clause is just
the relations within the INNER JOIN, which does not include t1.
You would get the same from

SELECT * FROM t1, t2 INNER JOIN t3 ON nn=3Dxx AND mm=3Daa;
ERROR:  column "aa" does not exist
LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=3Dxx AND mm=3Daa;
                                                           ^

because again t1 is not part of the JOIN sub-clause.  (MySQL used
to get this wrong, many years ago, and it seems that has taught
a lot of people some strange ideas about syntactic precedence
within FROM clauses.  Postgres' behavior agrees with the SQL
spec here.)

			regards, tom lane