Odd SQL errors: relation "n" does not exist

ON (na.nid = 0 OR na.nid = n.nid)

This syntax is uncommon if not wrong. I may be wrong, but it would seem that the na.nid = 0 part should go in the WHERE clause and this should instead be ON na.nid = n.nid.

I think you mean the other sitemap.module and not site_map.module. I developed and support site_map.module and cannot recall who supports the other. As it stands, the author of sitemap.module probably does not know about your bug report yet. If you have the module around, look for the author's name at the top. Perhaps you could use that to contact him/her.

The reported error is not related to site_map.module, and I cannot find a project page for the sitemenu.module. I think that's what you need, though.

Ok, dont know if it is the solution but when queriing the database returns some values, that are, I think just what we need. Im using postgresql, dont know if this issue is for mysql too.

This problem appears everywhere you need to count number of items in a term. The same error I get when enabling to show number of items in site_map and in sitemenu modules, or when counting number of items in image galleries.

For counting items, funcion in taxonomy.module is used:
/**
* Given a term id, count the number of published nodes in it.
*/

function taxonomy_term_count_nodes($tid, $type = 0) {
  static $count;
  if (!isset($count[$type])) {
    // $type == 0 always evaluates true is $type is a string
    if (is_numeric($type)) {
        echo "yup here is the error for site_map";
      $result = db_query(db_rewrite_sql('SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 GROUP BY t.tid'));
    }
    else {
        echo "yup here is the error for image galleries";
      $result = db_query(db_rewrite_sql("SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t, {node} n WHERE t.nid = n.nid AND n.status = 1 AND n.type = '%s' GROUP BY t.tid"), $type);
    }
    while ($term = db_fetch_object($result)) {
      $count[$type][$term->tid] = $term->c;
    }
  }
....

we know that sql error that comes up is something like:

query: SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t INNER JOIN node_access na ON na.nid = n.nid , node n WHERE  (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND  t.nid = n.nid AND n.status = 1 AND n.type = 'image' GROUP BY t.tid

if we compare that query with one in upper taxonomy funcion

 $result = db_query(db_rewrite_sql("SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t, {node} n WHERE t.nid = n.nid AND n.status = 1 AND n.type = '%s' GROUP BY t.tid"), $type);

we see that {node} part in function is really INNER JOIN node_access na ON na.nid = n.nid , node n in sql part (or something like that). I guess that is achieved by db_rewrite_sql function in include/database.inc file.

If we just change na.nid = n.nid to na.nid = t.nid query is working, because we're INNER JOINING node_acces (na) and term_node (t), not node (n).

Now, I still dont know where is db_rewrite_sql called with required parameters...I'll try to figure it out

If someone knows anything write it!!!
Tnx

Nope, its because of taxnomy_access module. When I disable it everything works OK. Dont know where but taxnomy module add the second line in query:

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t 
INNER JOIN node_access na ON na.nid = t.nid 
INNER JOIN node n ON t.nid = n.nid WHERE  
(na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND  
n.status = 1 GROUP BY t.tid

Without taxnomy module it just:

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t 
INNER JOIN node n ON t.nid = n.nid WHERE  
n.status = 1 GROUP BY t.tid

i know that 4th row of query is from node.module, maybe by _node_access_where_sql function

$sql = $node_access_alias .'.grant_'. $op .' = 1 AND CONCAT('. $node_access_alias .'.realm, '.  
  $node_access_ali as .'.gid) IN (';
    $grants = array();
    foreach (node_access_grants($op, $uid) as $realm => $gids) {
                 foreach ($gids as $gid) {
                $grants[] = "'". $realm . $gid ."'";
                }
  }
  $sql .= implode(',', $grants) .')';
...

but dont know where is 2th row coming from...have to find out, because the query is wrong because of it.

 SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t
 INNER JOIN node_access na ON na.nid = n.nid , node n
 WHERE  
(na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND
 t.nid = n.nid AND n.status = 1 AND n.type = 'image' GROUP BY t.tid 

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM {term_node} t, {node} n 
WHERE
 t.nid = n.nid AND n.status = 1 AND n.type = 'image' GROUP BY t.tid

Query error when trying to see image galleries with taxonomy_access is on. Again, the fourth row is added by node.module and for second dont know from. But query is wrong because node n comes after INNER JOIN, it should come after term_node t so querry would be:

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t , node n
INNER JOIN node_access na ON na.nid = n.nid removed from here
WHERE
(na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND
t.nid = n.nid AND n.status = 1 AND n.type = 'image' GROUP BY t.tid

So, second line should come after node n, and then query works.

In both cases second line is wrong, so i guess its something with taxonomy or taxonomy_access module.

Has anyone been able to fix this problem? I seem to have narrowed it down to the node_db_rewrite_sql() function in node.module. That seems to be the function adding the incorrect inner join.

I see that the CVS of taxonomy_access has a new _db_rewrite_function - perhaps this will make a difference?

Hello All,

I got the same error using C# with NHibernate and PostgreSQL. I dont how to fix it in NHibernate, because I dont have access to generated queries, but if you have access to queries, the solution is quite simple. The object names must be enclosed by quotes and must be case sensitive. For example:

Right way:
select * from "Customer"

Wrong ways:
select * from "customer"
select * from "CUSTOMER"
select * from customer
etc.

If you have any tips about NHibernate, or if you have any other solution about "relation
does not exist" contribute on forums, because thousand people have the same issue.

Tks.

I had the same sort of problem but in a slightly different place:

pg_query(): Query failed: ERROR: relation "n" does not exist in /var/www/questarus/includes/database.pgsql.inc on line 45.
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 WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND n.status = 1 AND ((e.event_start > 1133424000 AND e.event_start < 1136102399) OR (e.event_end > 1133424000 AND e.event_end < 1136102399) OR (e.event_start < 1133424000 AND e.event_end > 1136102399)) ORDER BY event_start in /var/www/questarus/includes/database.pgsql.inc on line 62.

Now as to the solution... The problem exists becuase the joins are being put back together improperly by the db_rewrite_sql() function. Take the above example. We're trying to get to n.nid, but n.nid is in the second join. This join is implemented as a sub query and as such the alias n is not available to the main query. Reversing the order of the joins resolves this problem because the access join is really only needed to be run as a sub query anyway.

Attached is a patch that achieves this, it ensures that the inserted join is always added only immediately prior to the WHERE clause. This should resolve this problem in all cases.

Gah, I reversed the order of my diff call again. Here's the proper patch.

Okay the patch I provided is not so good. It fixes the problem with the event module but creates new problems for the forum module. I've found a way to fix the specific issue over there. Guess I'll open a new issue with that patch.