user warning: Unknown table 'n' ...

What modules do you have installed on your site?

These error messages come from category module, from its included replacement taxonomy module (in /wrappers), and from an access control module (either cac_lite included in category module or another access control module).

Category module deviates from standard Drupal in several ways. I would start my hunt by asking its developer, just in case he is around and he is not onto something else.

Oh, and I would try to keep to the point and be as technical as I can.

moving

Are you using the views module? The problem query that you posted looks to me like it could be views-generated. I don't think category is responsible for this - it doesn't do query generation, and there is no query in the module that looks like that.

What made me mention category's taxonomy wrapper was the "category_get_container()" error in #4.

I also notice that the query in the initial post mentions both cac_lite and og (og_public and og_all), possibly a problematic combination.

Seems I'm somehow involved in this (detected the link), though I'm not sure how excately. Well here are my two cents... Rather some mumbling and loud thinking than a concrete solution, though.

An error like this usually is caused by some module "rewriting" a query of some other module, each of them not aware of each other. This is a powerful feature, yet it also is highly error prone - as the original poster already figured out.

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:

• A module invokes db_rewrite_sql with wrong parameters
• 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.

That's also the reason why the list of modules installed is extremly important with issues like this.

Regarding the concrete sql query causing the error, two modules obviously are of different opinion, how the node table should be named. While the invoking module uses "node" as alias, the module appending "n.moderate != 0" assumes "n".

For me this looks like either one the following has happened, though this is only a - not even educated - guess.

• The invoking module uses "node", which is correct, following the documentation, but which seems unusual looking at the code (which says "n"). However, the module maybe invokes the rewriting omitting the table name parameter, so "n" gets filled in, leading to the modules doing the rewrite assuming "n" as alias.
• Or: The module rewriting the query simply ignores the table name passed

What I would do is to search all the modules installed for portions of the SQL string. This is to figure out the modules involved in producing this query. I did this for my system and the only module I found using node as table alias is the view module. I'm not sure, but most likely, this also is the source of the query that is causing you trouble.

The view module however invokes the function correctly and additionally some of the rewriting (regading the access check) seems to be done correctly ("INNER JOIN node_access" comes from node module, I think), so I would for now concentrate on possibilty 2, that the rewriter is doing wrong. Try to find "_db_rewrite_sql" in the sources to find all places where a query gets rewritten. These are to be examined if they add the "moderate" check. But note that searching is not possible using windows search, since Windows search will ignore the module files (as unknown file type).

Again: The list of modules installed is extremly important to fix this bug.

Btw: As far as I can see, none of the category modules is involved in this error.

Interesting observations. I would pass the ball to views, then back to core, and if all fails it will probably remain unresolved for lack of a complete module/version list.

I agree with gerd; this is neither a views issure nor a core issue. Views doesn't put items into a query in the form of'n.moderate = 1; it would instead be node.moderate = '1'.

Still, it might be worthwhile to get an export of whatever view is causing this, just to ensure that there isn't some funny filter somewhere.

The only way to track this down is to get a complete module list of every contributed module you have active in your system.

However, in doing a grep on the entire modules directory (and you can THANK me for that piece of work) I believe your problem is likely the 'og_moderate' module.

You can confirm this by disabling that module.

I have submitted a patch to the og_moderate module that should fix this condition.

http://drupal.org/node/83267

Well, allowing every module to change the sqls of any other module doesn't necessarily ensure a controlled environement. Welcome to support hell :-)

I tried to tell people where and what for to look (teach a man to fish...). And I continue the mission with some more details:

Code like this (a patch taken from here) illustrates the problem. It 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"? This is plain wrong, and will be causing an "unknown table n" error, if the table alias is "node" - as is the case with the problem discussed here.

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. This turns the code into a neat little timebomb.

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 hardcoded "n.[fieldname]" in it.

Oops, took to long to write my prior comment. It partly answers to #10.

I think merlinofchaos is right and his patch proposed in comment #12 will fix the problem.

gerd -- you deserve a real round of applause for that bit of sleuthing. Good job. You were absolutely correct in every detail.

The patch I linked has already been committed, so an update of og_moderate (once the new tarball is generated in the next 12 hours or so) will also fix the problem.