Re: swap relations to be able to execute a left join

[Michael Fuhr <mike@xxxxxxxx>]

On Fri, Dec 09, 2005 at 12:47:19PM -0500, Tom Lane wrote:
> Right.  MySQL apparently thinks that JOIN has the same precedence as
> comma in a FROM-list, but anyone who has bothered to read the SQL
> standard knows that JOIN is supposed to bind tighter than comma.
> Your coworker is depending on a flat-out-incorrect behavior of MySQL.

Hmm...I get errors in MySQL 5.0.16 with the query as posted so I
have to wonder what the real query was.  With no changes at all the
query fails due to an extra comma at the end of the select list:

mysql> SELECT
    ->     AR.artifact_id,
    ->     AT.type_nm,
    ->     AR.title,
    ->     U.dept,
    -> FROM
    ->     Artifact_Revisions AR,
    ->     Revisions_to_Types RTT,
    ->     Artifact_Types AT
    -> LEFT JOIN
    ->     Users U
    ->     on (U.user_id = AR.principal_user_id)
    -> WHERE
    ->     AR.revision_id = RTT.revision_id
    ->     AND RTT.type_id = AT.type_id
    ->     AND AR.revision_id = 28403;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
    Artifact_Revisions AR,
    Revisions_to_Types RTT,
    Artifact_Types A' at line 6

If I remove the offending comma then I get an error similar to what
PostgreSQL would give:

mysql> SELECT
    ->     AR.artifact_id,
    ->     AT.type_nm,
    ->     AR.title,
    ->     U.dept 
    -> FROM
    ->     Artifact_Revisions AR,
    ->     Revisions_to_Types RTT,
    ->     Artifact_Types AT
    -> LEFT JOIN
    ->     Users U
    ->     on (U.user_id = AR.principal_user_id)
    -> WHERE
    ->     AR.revision_id = RTT.revision_id
    ->     AND RTT.type_id = AT.type_id
    ->     AND AR.revision_id = 28403;
ERROR 1054 (42S22): Unknown column 'AR.principal_user_id' in 'on clause'

Swapping AR and AT as suggested finally works (the test tables are
empty so the query returns no results):

mysql> SELECT
    ->     AR.artifact_id,
    ->     AT.type_nm,
    ->     AR.title,
    ->     U.dept
    -> FROM
    ->     Artifact_Types AT,
    ->     Revisions_to_Types RTT,
    ->     Artifact_Revisions AR 
    -> LEFT JOIN
    ->     Users U
    ->     on (U.user_id = AR.principal_user_id)
    -> WHERE
    ->     AR.revision_id = RTT.revision_id
    ->     AND RTT.type_id = AT.type_id
    ->     AND AR.revision_id = 28403;
Empty set (0.00 sec)

This leaves me wondering what the test case really was or if MySQL
behaves differently under different versions (I'm aware of the
sql_mode setting but it's empty on my system; I haven't checked if
any of its possible values would affect precedence in the from list).

-- 
Michael Fuhr