[development] touchy mysql ordering

[Chris Johnson]

Tao Starbow wrote:
> Stop me if you've heard this one before.
> 
> I recently upgraded the server running my Drupal site, and the events 
> calendar stopped working for everyone except user=1.  I tracked down the 
> problem to this (simplified) line of SQL:
> SELECT n.nid, e.event_start FROM {event} e INNER JOIN {node} n ON n.nid 
> = e.nid
> This returns all my events if the user=1, and zero if the user is anyone 
> else.
> However, if I rewrite the SQL as:
> SELECT n.nid, e.event_start FROM {node} n INNER JOIN {event} e ON n.nid 
> = e.nid
> then it behaves as I would expect, returning all events regardless of 
> who the user is.
> 
> I did not have this problem on my old server, which was running mysql 
> 4.0.20-max.  The new server is running mysql 5.0.17.
> 
> I suspect whatever is causing this is also at the root of my 
> node_access_by_role issue (http://drupal.org/node/40623).  I guess my 
> next move is to install the devel module and see if I can find a 
> difference between the sql actually being generated when user=1 vs user!=1.

If the actual generated SQL works/fails as you describe in the simplified SQL 
above, I think you have found a bug in MySQL.  INNER JOIN should be 
commutative, that is, it should not matter which order the two joined tables 
are listed, the results should be the same.