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

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