[development] touchy mysql ordering

[Tao Starbow]

I have followed up on this issue at: 
http://drupal.org/node/43735#comment-92865
I will be submitting patches to event.module, image.module & 
taxonomy.module.

It would be great if someone would patch db_rewrite_sql() itself to warn 
when it is being used incorrectly.  I think the correct behavior is to 
warn if the $primary_table does not match the first table in the FROM 
clause.  I will take a pass at it, but it's going to take me a while to 
figure out how the function works.

The problem with the USING clause is harder ("Column 'nid' in from 
clause is ambiguous").  This is not a case of db_rewrite_sql being 
called incorrectly.  Using USING is fine for a select with two tables,  
but the rewrite converts the select into a three (or more?) table join.  
Mysql used to guess which of the tables to apply the USING clause to, 
now it throws an error.  My guess is that db_rewrite_sql is going to be 
forced to reject the use of USING altogether.

-tao

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