sql - Mixing explicit and implicit joins fails with "There is an entry for table ... but it cannot be referenced from this part of the query"

Question

SELECT
      i.*, 
      r.name AS roomname, 
      c.name AS cat, 
      p.key AS imgkey, 
      p.extension AS imgext
   FROM 
      items i, 
      rooms r, 
      categories c 
         LEFT JOIN photos p 
            ON p.referencekey = i.key 
   WHERE 
          i.room = r.key 
      AND r.key = 663308 
      AND i.sitekey = 32201 
      AND c.key = i.categorykey

The above query when executed returns following error.

ERROR: invalid reference to FROM-clause entry for table "i"

LINE 1: ...tegory c LEFT JOIN photos p ON p.referencekey = i.key WHER...

HINT: There is an entry for table "i", but it cannot be referenced from this part of the query.

Answer 1

The SQL spec states that explicit joins are performed before implicit joins. This is an implicit join:

FROM table1 t1, table2 t2 WHERE t1.id=t2.t1id

This is an explicit join:

FROM table1 t1 JOIN table2 t2 ON (t1.id=t2.t1id)

This code bit:

categories c 
     LEFT JOIN photos p 
        ON p.referencekey = i.key 

is an explicit join and is run first. Note that at this point the table aliased as i hasn't been looked at yet, so it can't be joined yet. Note that MySQL fixed this behaviour in 5.2 I believe, and this query will no longer work there either.