MySQL bug or module bug ? Sorry, to post about the same issue, but I am really trying to understand this. ---1--- This query (as seen in event.module): SELECT n.nid, e.event_start FROM event e INNER JOIN node n ON n.nid = e.nid When passed thru the db_rewrite_sql() function, the function returns this query: SELECT DISTINCT(n.nid), e.event_start FROM event e INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n ON n.nid = e.nid ------- ---2--- This query (the proposed fix): SELECT n.nid, e.event_start FROM node n INNER JOIN event e ON n.nid = e.nid When passed thru the db_rewrite_sql() function, the function returns this query: SELECT DISTINCT(n.nid), e.event_start FROM node n INNER JOIN node_access na ON na.nid = n.nid INNER JOIN event e ON n.nid = e.nid ------- Query 1 and 2 prior to being passed to the db_rewrite_sql() function are essentially the same. After being passed to the db_rewrite_sql() function... Query 1 produces an error in MySQL 5.0 and Query 2 does not. So, is this a MySQL bug or module bug ? Thanks Randall Original Message: ----------------- From: Chris Johnson chris@tinpixel.com Date: Mon, 13 Feb 2006 10:10:43 -0600 To: development@drupal.org Subject: Re: [development] touchy mysql ordering 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. -------------------------------------------------------------------- mail2web - Check your email from the web at http://mail2web.com/ .