I am getting the following error (repeated 5 times when glossary page is displayed) since update from 6.x-1.3 to 6.x-1.4:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(n.nid)) FROM term_node tn JOIN node n USING (nid) WHERE tn.tid=33 AND n' at line 1 query: SELECT COUNT(t DISTINCT(n.nid)) FROM term_node tn JOIN node n USING (nid) WHERE tn.tid=33 AND n.status=1 in /var/www/html/beta.gewiss.si/sites/all/modules/glossary/glossary.module on line 768.

tn.nid in code somehow seems to be converted to t DISTINCT(n.nid). I've replaced td with t (as it was in version 1.3) and this error disappeared, but came out the new one (the same issue in line 774 query). Doing the same there, I've managed to get rid of error messages.

Here is what I've done:

--- glossary.module.ORIG	2008-05-17 00:20:56.000000000 +0200
+++ glossary.module	2008-05-19 11:01:28.000000000 +0200
@@ -765,13 +765,13 @@
       $output .= implode(', ', $term->synonyms) ."</span>\n";
     }
 
-    $detailed_exists = db_result(db_query(db_rewrite_sql('SELECT COUNT(tn.nid) FROM {term_node} tn JOIN {node} n USING (nid) WHERE tn.tid=%d AND n.status=1'), $term->tid));
+    $detailed_exists = db_result(db_query(db_rewrite_sql('SELECT COUNT(t.nid) FROM {term_node} t JOIN {node} n USING (nid) WHERE t.tid=%d AND n.status=1'), $term->tid));
     if ($detailed_exists) {
       // Do we want to show the teasers?
       if (variable_get('glossary_show_detailed', false)) {
         $output .= '<div class="glossary-detailed">';
         $text = null;
-        $detailed = db_query(db_rewrite_sql('SELECT tn.nid FROM {term_node} tn JOIN {node} n USING (nid) WHERE tn.tid=%d AND n.status=1'), $term->tid);
+        $detailed = db_query(db_rewrite_sql('SELECT t.nid FROM {term_node} t JOIN {node} n USING (nid) WHERE t.tid=%d AND n.status=1'), $term->tid);
         while ($row = db_fetch_array($detailed)) {
           $node = node_load($row['nid']);
           // Format as teaser view with links.

Comments

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Thanks, LUTi. I am assuming that i18n is doing this. I will make these changes.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Assigned: Unassigned » nancydru
Status: Active » Fixed

Fix committed to -dev. Please check it and let me know if it solves the problem.

luti’s picture

luti commented

As much as I see, you've applied the patch exactly as above - for that, I have already confirmed (with the code) that the changes helped me to get rid of error messages.

But, if i18n is doing that, it would probably be good to post an issue about that there, or? It was absolutely correct query, which something turned into something useless, and that behavior can probably have impact on other modules as well...

And, just to avoid any misunderstandings - I don't have Internalization / i18n module installed! So, it probably has to be a part of the i18n functionality moved to the core at Drupal version 6...

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I guessed i18n because of the db_rewrite_sql. There are two places where that is intercepted on a regular basis: security modules and i18n.

http://drupal.org/node/259907 was a change to make i18n work and includes an update to the documentation.

i18n issues:
http://drupal.org/node/256415
http://drupal.org/node/233817

erofadd’s picture

erofadd commented

Hello,

Please, I am ready to help you out but let me know whether your SQL database was created by you or your importing the database from an old server.

thanks

luti’s picture

luti commented

What does it have to do with the fact that something is screwing up a query just because td table alias is used instead of t???

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Hook_db_rewrite_sql (as in i18n) uses that alias to determine whether or not to alter the query.

luti’s picture

luti commented

NancyDru, thank you for details about hook_db_rewrite_sql (), but what a difference does it make if the database has been created by me or imported from the old server (it was something in between, by the way - I've transferred just the content of the site, more or less through many manually created queries, from the old to the new database...)?!

I can understand it happens (if the same alias is used...), but are those aliases stored in the database? Or, what else could be ported from the old server, that can cause that?

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I didn't ask about whether is was new or updated, erofadd did. What I care about is who is doing the hook_db_rewrite_sql and what rules they want me to follow.

In this context, "alias" refers to the database table. For example, if one codes SELECT * FROM {node} n ..., then "n" is the alias. Those are not stored, they are the developer's preference for shortening the query. However, some implementations of hook_db_rewrite_sql require the developer to use a specific alias. That's why I opened an issue against i18n to provide developer information.

It is apparent that you are using some module that implements hook_db_rewrite_sql, and it seems that they also have a requirement that the table alias be something specific.

luti’s picture

luti commented

NancyDru, sorry if it looked like I am asking you about what does it matter if the database has been ported from the old system. It was a general question, to which I expect erofadd will provide his explanation (as he asked about...).

Generally, I know the purpose of aliases, and probably they could also be stored into the database, as any other variable into the database. I don't know what the purpose or the benefit would be, though... ;-)

Maybe it would be good to suggest the rule, that any module, which rewrites queries or other modules code, ensures that the same names (of aliases in this case, but also global variables are probably candidates for similar problems...) are not used. This can be done probably by examining the code before it is actually changed, or simply by adding "something" to all of the names it uses (a module name, or some project ID - if some system could be established, which would ensure the uniqueness of it...).

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.