Re: UPDATE-FROM and INNER-JOIN

Dominique Devienne <ddevienne(at)gmail(dot)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 = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa;

yields

ERROR: column "aa" does not exist
LINE 1: ... t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa;
^
DETAIL: There is a column named "aa" in table "t1", but it cannot be referenced 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=xx AND mm=aa;
ERROR: column "aa" does not exist
LINE 1: SELECT * FROM t1, t2 INNER JOIN t3 ON nn=xx AND mm=aa;
^

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