Re: LEFT JOIN, entry can not be referenced

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com>
> wrote:

>> SELECT
>> wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl.
>> docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue
>> FROM worklogs AS wl, workactions AS wa, users AS u
>> LEFT JOIN documents AS d ON wl.doc_id=d.id
>> LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
>> LEFT JOIN files AS f ON wl.file_id=f.id
>> WHERE wl.action_id=wa.id AND wl.user_id=u.id
>> ORDER BY wl.ts DESC;
>>
>> When I run this I get the error:
>>
>> --------------------
>> ERROR: invalid reference to FROM-clause entry for table "wl"
>> LINE 3: LEFT JOIN documents AS d ON wl.doc_id=d.id
>> ^
>> HINT: There is an entry for table "wl", but it cannot be referenced
>> from this part of the query.
>> --------------------

> You should write out all of your joins explicitly.
> ...
> Mixing "FROM tbl1, tbl2 WHERE" and "FROM tbl1 JOIN tbl2 ON" syntax just
> causes grief.

More specifically, the commas can be read as CROSS JOINs of the lowest
syntactic priority, so that what you wrote is equivalent to

SELECT ... FROM
worklogs AS wl
CROSS JOIN workactions AS wa
CROSS JOIN (users AS u
LEFT JOIN documents AS d ON wl.doc_id=d.id
LEFT JOIN docrevs AS dr ON wl.docrev_id=dr.id
LEFT JOIN files AS f ON wl.file_id=f.id)
WHERE ...

You could further parenthesize that, understanding that JOIN operators
bind left-to-right when not parenthesized, but I think it would just
add clutter not clarity. Anyway, the point is that that first ON
clause can only refer to "u" and "d", because only those two tables
are in-scope for it.

There are other RDBMSes (mumble ... ancient mysql versions ... mumble)
that give the commas a different syntactic priority and would allow
that ON clause to reference "wl". But they're wrong per SQL spec.

regards, tom lane