General Comments on Drupal's Error "Unknown table 'n'"

Maybe you already know the famous Drupal error “Unknown table ‘n’”. It looks like this:

user warning: Unknown table 'n' in where clause query: SELECT DISTINCT(node.nid), node.type AS node_type, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node.created AS node_created, node_counter.daycount AS node_counter_daycount FROM node node LEFT JOIN users users ON node.uid = users.uid LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 0 AND na.realm = 'og_all') OR (na.gid = 3 AND na.realm = 'og_subscriber') OR (na.gid = 4 AND na.realm = 'og_subscriber') OR (na.gid = 0 AND na.realm = 'cac_lite'))) AND (n.moderate != 1) AND (node.status = '1') ORDER BY node_counter.daycount DESC LIMIT 0, 5 in /home/orbit42/public_html/includes/database.mysql.inc on line 120.

This error is caused by some module “rewriting” a query of some other module, each of them not aware of each other. This is a powerful Drupal feature, yet it also is highly error prone - as the lots of error alike show. Here’s how to deal with it.

The process of rewriting a query works like this: A module builds a SQL query and passes it to the function db_rewrite_sql, together with the table name and some other information.

In general there are two possible error sources:

  1. A module invokes db_rewrite_sql with wrong parameters
  2. A module wrongly implements the actual rewriting

Each of these errors will occur only if the query is rewritten, which actually may depend on a the module configuration, user state etc. That’s why there are so much different forms of the error.

An example: Module 1 issues a query rewrite, but does it wrong. This will only be discovered when another module really rewrites something. There aren’t to many modules doing this, so it may take a time. The same holds, when a module wrongly implements the query rewriting. In this case, the error will occur within some other module, which is especially nasty.

For this reasons, when dealing with “Unknown table ‘n’”, the list of modules installed is extremely important.

Now let’s dive a little deeper into db_rewrite_sql(). The function is declared like this:

function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array())

The first 3 parameters are of interest. First it takes the SQL query as a string, followed by the name of the primary table and the primary id field. Table and id field are supposed to default to ‘n’ and ‘nid’.

‘n’ - does that sound familiar?

Indeed, this ‘n’ causes all the trouble. See, it is assumed that the table queried is called ‘n’ normally, however, this is not required.

This can be an error source when invoking the function. This for example is wrong:

$sql = db_rewrite_sql('Select nid, title from {node} as squirrel');

The primary table name (which correctly should be called the primary alias name) in this case is ‘squirrel’. I like squirrels. Due to the default values, the function is expanded to:

$sql = db_rewrite_sql('Select nid, title from {node} as squirrel', 'n', 'nid');

OK, there is a field ‘nid’, but no ‘n’ anywhere. This is bad.

A correct way to invoke db_rewrite_sql() and keep the squirrel alias is to simply pass it:

$sql = db_rewrite_sql('Select nid, title from {node} as squirrel', 'squirrel', 'nid');

This code should issue no problems - well, unless of course some squirrel hating module handles it wrong.

If a module wants to rewrite queries of other modules, it has to provide a so-called hook. A hook is named [module-name]_[hook_name]. So if your module file is named squirrel_hater.module, the hook would be named squirrel_hater_db_rewrite_sql(). This function takes exactly the same arguments than the original db_rewrite_sql():

squirrel_hater_db_rewrite_sql($query, $primary_table, $primary_field, $args)

Let’s have a look at a real word example. Code like this (a patch taken from here) is a true troublemaker:

/**
 * Implementation of hook_db_rewrite_sql
 */
function content_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
  global $user;
  switch ($primary_field) {
    case 'nid':
      if ($user->uid != 1) {
        $return = array();
        $where = array("n.type <> ''");
        foreach (content_types() as $name => $type) {
          if (!user_access('view '. $name .' content')) {
            $where[] = "n.type <> '$name'";
          }
        }
        $return['where'] = join(' AND ', $where);
        return $return;
     }
     break;
  }
}

Note that this codes checks if the element queried is a node. Therefore it looks if the query deals with a node’s id (case “nid”). This is totally correct.

But then the code assumes the table alias is “n” - for exampel in this line:

$where = array("n.type <> ''");

Notice “n.type”? No squirrels! This is what will be causing an error “unknown table ‘n’” error.

Correct code would use the table alias $primary_table, like so:

 
$where = array($primary_table . ".type <> ''");

Here’s the whole snippet, corrected:

/**
 * Implementation of hook_db_rewrite_sql
 */
function content_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
  global $user;
  switch ($primary_field) {
    case 'nid':
      if ($user->uid != 1) {
        $return = array();
        $where = array($primary_table . ".type <> ''");
        foreach (content_types() as $name => $type) {
          if (!user_access('view '. $name .' content')) {
            $where[] = $primary_table . ".type <> '$name'";
          }
        }
        $return['where'] = join(' AND ', $where);
        return $return;
     }
     break;
  }
}

This little error in the implementation of the db_rewrite_sql-hook is not noticed, as long as the primary table is “n”, which it is in about 95% of all cases. There are, however, some modules, who name the table differently. Not “squirrel”, of course, but for “node”, for example. Additionally, the rewriting is not done for administrators. This turns the code into a neat little time bomb.

There are hundreds of modules, and any of them may contain a wrong implementation like the above one. Additionally there are hundreds of patches, which - like the example - may contain errors, too.

So, my advise in case of trouble is to look for code fragments like above. Look for functions named “[modulename]_db_rewrite_sql” and see if there’s a hard coded “n.[fieldname]” in it.

Note: This article is based on what I wrote on this bug report. I polished it a bit, tried to simplify it here and there, and added some squirrels.

Published: October 25 2006