touchy mysql ordering

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 christinpixel.com
Date: Mon, 13 Feb 2006 10:10:43 -0600
To: developmentdrupal.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  e INNER JOIN
 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  n INNER JOIN
 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/4
0623).  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/ .

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 N| clause
could refer to columns in tables named to 
its right. Now an N| 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?


randallthinkingoutloud.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 christinpixel.com
>Date: Mon, 13 Feb 2006 10:10:43 -0600
>To: developmentdrupal.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  e INNER
JOIN  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  n INNER JOIN
 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/4
0623).  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

I have followed up on this issue at: 
http://dru
pal.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

randallthinkingoutloud.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 christinpixel.com
>Date: Mon, 13 Feb 2006 10:10:43 -0600
>To: developmentdrupal.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  e INNER
JOIN  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  n INNER JOIN
 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/4
0623).  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

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://dru
pal.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
>
> randallthinkingoutloud.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 christinpixel.com
>> Date: Mon, 13 Feb 2006 10:10:43 -0600
>> To: developmentdrupal.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  e
INNER JOIN  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  n INNER
JOIN  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/4
0623).  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

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

I can make that a debug option. Sounds good and simple.

Cool.
How are debug options triggered?  Is that something
controlled by the 
devel module?

Karoly Negyesi wrote:

>>> 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
>>
>
> I can make that a debug option. Sounds good and simple.

Also, if you are going to touch the db_rewrite_sql source,
maybe you 
could expand the description of param $primary_table to
include "This 
value must match the first table listed in the FROM clause
for rewriting 
to be successful". 

Which bring up the question of why have this as a parameter
at all as 
opposed to just pulling it out of the regex?  I should
probably just 
keep quiet until I have figured out how db_rewrite_sql
really works.

-t

Karoly Negyesi wrote:

>>> 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
>>
>
> I can make that a debug option. Sounds good and simple.