[development] touchy mysql ordering

[randall at thinkingoutloud.org]

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/ .