[development] touchy mysql ordering

Chris Johnson chris at tinpixel.com
Mon Feb 13 16:10:43 UTC 2006

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.

More information about the development mailing list