[development] touchy mysql ordering

[Tao Starbow]

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.

ps. It was great meeting you all in Vancouver.

-- 
Tao Starbow
Web Architect, CITRIS