[development] mysql 5.0.12 (was: touchy mysql ordering)

[Tao Starbow]

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 at 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 at tinpixel.com
>Date: Mon, 13 Feb 2006 10:10:43 -0600
>To: development at 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