I did some googling around the mysql site and came up with: http://dev.mysql.com/doc/refman/5.0/en/join.html - specifically the section that begins: "*Note*: Beginning with MySQL 5.0.12, natural joins and joins with |USING|, including outer join variants, are processed according to the SQL:2003 standard." I think my problems with node_access_by_role are covered by: "Previously, the |ON| clause could refer to columns in tables named to its right. Now an |ON| clause can refer only to its operands." I did not find anything that seemed to cover the sql for the events queries thou, where the order of the inner join clauses is so important, but obviously there have been small but important changes intentionally made. It seems like, pre-5.0.12, module developers could be sloppy about using db_rewrite_sql and it would be ok, but as of 5.0.12 we've got problems. I will be trying to patch 4.6 versions event.module and image.module in the next couple of days because my sites need them. I bet this is going to be biting a lot of people. The db_rewrite_sql doc page is going to need more usage instructions. I wonder if it is possible to make db_rewrite_sql smart enough to detect improper usage? randall@thinkingoutloud.org wrote:
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/ .
-- Tao Starbow Web Architect, CITRIS