table 'comments' not 'comment'

I don't know anything about commentRSS, but I think the SQL error is caused by a typo. db_rewrite_sql is being passed 'comment' when it should be passed 'comments'.

But I think there's a more difficult problem. cac_lite code assumes a primary field of 'cid' refers to a category id. But in this case it seems to refer to a comment id. So that's going to cause problems.

Try this: add code near the top of cac_lite_db_rewrite_sql that says:

if (strpos($primary_table, 'comment') === 0) {
    return;
}

If that solves the problem I'll see what I can do to permanently address this in cac_lite.

I'm having similar issues when cac_lite is enabled and I add nodes (event, image, etc.). I get the following error msg:

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND cac_lite_na.gid IN (0)) AND cnt.type = 'event' AND n.status = 1 AND n.m' at line 1 query: category_form_alter SELECT cn.*, n.title FROM category_cont cn INNER JOIN category_cont_node_types cnt ON cn.cid = cnt.cid INNER JOIN category c ON cn.cid = c.cid INNER JOIN node n ON cn.cid = n.nid LEFT JOIN category ca_c ON cn.cid = ca_c.cid LEFT JOIN node_access cac_lite_na ON cn.cid = cac_lite_na.nid WHERE ((cn.cid IN (0) OR ca_c.cnid NOT IN ()) AND cac_lite_na.gid IN (0)) AND cnt.type = 'event' AND n.status = 1 AND n.moderate = 0 ORDER BY c.weight, n.title in D:\www\public_html\domain\includes\database.mysql.inc on line 120.

And this error only shows up when I'm logged in as a user other than administrator (1).

Thanks.
Lsabug

Dude WTF? You totally hijacked a bug. If you want your issue resolved, submit it as a new bug against cac_lite.

cac_lite will not work properly unless you tell it which categories should affect privacy. That will make your problem go away.

See, this is the query in commentrss module:

SELECT c.cid, n.nid, c.subject, c.comment, c.timestamp, c.uid, c.name FROM {comments} c INNER JOIN {node} n ON c.nid = n.nid WHERE n.status = 1 AND c.status = %d $nodeselector ORDER BY c.timestamp DESC

This is the query you have an issue with:

SELECT c.cid, n.nid, c.subject, c.comment, c.timestamp, c.uid, c.name FROM comments c INNER JOIN node n ON c.nid = n.nid LEFT JOIN category ca_c ON comment.cid = ca_c.cid LEFT JOIN node_access cac_lite_na ON comment.cid = cac_lite_na.nid WHERE ((comment.cid IN (12, 13, 14, 39, 63, 50, 19, 46, 45, 20, 48, 17, 52, 34, 54, 37, 16, 18, 0, 104, 106, 95, 7, 9, 8, 0, 0, 27, 99, 98, 96, 97, 24, 0) OR ca_c.cnid NOT IN (11,4,107,1,6,5,3,2)) AND cac_lite_na.gid IN (12,13,14,39,63,50,19,46,45,20,48,17,52,34,54,37,16,18,0,104,106,95,7,9,8,0,0,27,99,98,96,97,24,0)) AND n.status = 1 AND c.status = 0 ORDER BY c.timestamp DESC LIMIT 0, 15

You see, that the commentrss version does not have any problems. cac_lite however rewrites the query, and adds these comment.cid checks. The problem is not in commentrss.

This is the query in commentrss.module:

$sql = db_rewrite_sql("SELECT c.cid, n.nid, c.subject, c.comment, c.timestamp, c.uid, c.name FROM {comments} c INNER JOIN {node} n ON c.nid = n.nid WHERE n.status = 1 AND c.status = %d $nodeselector ORDER BY c.timestamp DESC", 'comment', 'cid');

This is the signature of db_rewrite_sql:
db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array())

The query uses a table named 'comments'. The $primary_table passed to db_rewrite_sql is 'comment', it should be 'comments'.

Ups, excuse me for being shortsighted. I have fixed the code in HEAD and 4.7. Thanks for the report.

Erm, the advice from yogadex was not actually right. Since the $primary_table parameter is used directly, to facilitate table prefixing, we need to pass on the table name alias (in this case c). Fixed in 4.7 and HEAD.

http://drupal.org/cvs?commit=38365
http://drupal.org/cvs?commit=38364

Ah, I see that now.

This is going to be a problem for cac_lite. It has no reliable way to determine whether the primary_field 'cid' is a category id or a comment id.

Hmmm.....

A heads up to yogadex, and an update to this bug:

I have updated the module in 4.7 and HEAD to use one SQL query to fetch relevant comments. It now calls the SQL rewrite with default parameters. Only this can ensure that node access restrictions are taken into account...

function node_db_rewrite_sql($query, $primary_table, $primary_field) {
  if ($primary_field == 'nid' && !node_access_view_all_nodes()) {
    $return['join'] = _node_access_join_sql($primary_table);
    $return['where'] = _node_access_where_sql();
    $return['distinct'] = 1;
    return $return;
  }
}

This means we have the compatibility problem resolved with commentrss.

Sweet, I think. If you mean that commentRSS restructured its query to use 'nid' as the primary key.

Thanks.

ju, could you please not change descriptive issue titles?