[development] touchy mysql ordering

[Tao Starbow]

Hope I am not just talking to myself here.
I have submitted patches to event, image & taxonomy for 4.6.  If they 
get reviewed and get positive feedback, I will see if similar patches 
need to be created for 4.7/cvs versions.

Tao Starbow wrote:

> 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