Re: [development] 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 chris@tinpixel.com Date: Mon, 13 Feb 2006 10:10:43 -0600 To: development@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/ .
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@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@tinpixel.com Date: Mon, 13 Feb 2006 10:10:43 -0600 To: development@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
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@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@tinpixel.com Date: Mon, 13 Feb 2006 10:10:43 -0600 To: development@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
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@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@tinpixel.com Date: Mon, 13 Feb 2006 10:10:43 -0600 To: development@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
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.
participants (3)
-
Karoly Negyesi -
randall＠thinkingoutloud.org -
Tao Starbow