[development] touchy mysql ordering

[randall at thinkingoutloud.org]

Hi, this is my first post to this list, please let me know if I am I out of
line in any way.

This may be a bigger issue than just the event.module

If the suggested fix for event.module works, then logically, the same sort
of fix will need to be made to image.module and forum.module and probably
some other modules.

I think killes is correct with his statement of
"The rewriting of the query fails as it isn't in the form the regexp
expects"

The regexp in question is in the db_rewrite_sql function.

This function and its associated hook IMHO provide one of the greatest
capabilities to this CMS helping to set Drupal high above the rest.  And I
am so excited to see that it has been expanded to include vocabularies and
terms in version 4.7 (thank you! drupal developers).

Here are some thoughts on the issue, it is a little mind-numbing I know and
I am sorry.  
Can someone let me know if I am being clear and also let me know if I am
just way off base.

I think that the db_rewrite_sql function needs to be more explicit in terms
of what sort of SQL statements it accepts for the $query value.  I also
think that it needs to be very specific in terms of what values are allowed
for the $primary_table and $primary_field values.  

For example, with node list queries, I think the $primary_table must be
'n', the $primary_field must be 'nid' and the $query value must be in the
format SELECT X...X FROM {node} n X...X or there are potential problems.  

Looking at the example code posted in drupaldocs for hook_db_rewrite_sql()
at http://drupaldocs.org/api/head/function/hook_db_rewrite_sql.  The sample
asks if table 'n' is the $primary_table and if not adds it to the join
clause. If two modules implemented the hook following that example,
checking for the existence of table 'n' and both were enabled on the same
site and the $primary_table was 'f' (or 'forum', or 'node' etc. as outlined
as acceptable values for db_rewrite_query) the resulting query would be
SELECT X...X FROM {forum} f INNER JOIN {node} n ON f.nid=n.nid INNER JOIN
{node} n ON f.nid=n.nid X...X which results in ERROR #1066 - Not unique
table/alias: 'n'  So, therefore, I also think that any module implementing
the hook needs to be aware of potential alias conflicts and should make
sure to alias tables with a name that won't conflict with someone else’s
module.

If interested in more information and more thoughts, please see these three
posts...

Bad interaction with MySQL 5 & Image & Event modules
http://drupal.org/node/40623

SQL error when anonymous: Unknown column 'n.nid'
http://drupal.org/node/46864

Error with node access modules and mysql 5
http://drupal.org/node/43735

Thank you, I hope I haven't wasted anyone’s time.

Randall


--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .