I am getting the following error:

user warning: Invalid use of group function query: SELECT DISTINCT n.title, n.nid FROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', ' Person Professional Past', ' Person Political Past') GROUP BY t.tid ORDER BY COUNT(n.nid) DESC LIMIT 5 in /pathtosite/sites/all/modules/popular_terms/popular_terms.module on line 216.

I remove these terms from the Calais black list and it still persists. If I disable the popular terms the error goes away.

I am running:
Apache version 1.3.41 (Unix)
PHP version 5.2.5
MySQL version 4.1.22-standard

I have installed it twice and get the error each time.

Comments

myersca’s picture

myersca commented

When I create a new topic hub, I get a major sql error. I think there is a bug between the Topic Hub and Popular terms module.

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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC ) count_alias in /pathtosite/sites/all/modules/views/includes/view.inc on line 705.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC LIMIT 0, 10 in /pathtosite/sites/all/modules/views/includes/view.inc on line 731.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC ) count_alias in /pathtosite/sites/all/modules/views/includes/view.inc on line 705.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC LIMIT 0, 10 in /pathtosite/sites/all/modules/views/includes/view.inc on line 731.
user warning: Invalid use of group function query: SELECT DISTINCT n.title, n.nid FROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', ' Person Professional Past', ' Person Political Past') GROUP BY t.tid ORDER BY COUNT(n.nid) DESC LIMIT 5 in /pathtosite/sites/all/modules/popular_terms/popular_terms.module on line 216.

===
If I disable the Popular terms module, I still get the following error with views and topic hubs.

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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC ) count_alias in /pathtosite/sites/all/modules/views/includes/view.inc on line 705.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC LIMIT 0, 10 in /pathtosite/sites/all/modules/views/includes/view.inc on line 731.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC ) count_alias in /pathtosite/sites/all/modules/views/includes/view.inc on line 705.
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 'in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comme' at line 10 query: SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video')) AND (((node_comments__term_node.tid = 28))) ORDER BY comments_timestamp DESC LIMIT 0, 10 in /pathtosite/sites/all/modules/views/includes/view.inc on line 731.

myersca’s picture

myersca commented

I install topic hubs on another site and I am still getting sql errors. Has anyone got this to work?

===

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(node.nid), node.title AS node_title, node_revisions.teaser AS nod' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format, node.type AS node_type, node.created AS node_created FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_created DESC ) count_alias in /pathtosite2/sites/all/modules/views/includes/view.inc on line 705.
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(node.nid), node.title AS node_title, node_revisions.teaser AS nod' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format, node.type AS node_type, node.created AS node_created FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_created DESC LIMIT 0, 10 in /pathtosite2/sites/all/modules/views/includes/view.inc on line 731.
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(node.nid), node.title AS node_title, node.type AS node_type, n' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node.type AS node_type, node_counter.totalcount AS node_counter_totalcount, node.created AS node_created FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_counter_totalcount DESC, node_created DESC ) count_alias in /pathtosite2/sites/all/modules/views/includes/view.inc on line 705.
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(node.nid), node.title AS node_title, node.type AS node_type, n' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node.type AS node_type, node_counter.totalcount AS node_counter_totalcount, node.created AS node_created FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_counter_totalcount DESC, node_created DESC LIMIT 0, 10 in /pathtosite2/sites/all/modules/views/includes/view.inc on line 731.
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 'in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topi' at line 11 query: SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid LEFT JOIN term_node node_comments__term_node2 ON node_comments.vid = node_comments__term_node2.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((node_comments__term_node.tid = 3) OR (node_comments__term_node2.tid = 1809))) ORDER BY comments_timestamp DESC ) count_alias in /pathtosite2/sites/all/modules/views/includes/view.inc on line 705.
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 'in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topi' at line 11 query: SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, node_comments.title AS node_comments_title, node_comments.nid AS node_comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid LEFT JOIN term_node node_comments__term_node ON node_comments.vid = node_comments__term_node.vid LEFT JOIN term_node node_comments__term_node2 ON node_comments.vid = node_comments__term_node2.vid WHERE (node_comments.status <> 0 OR node_comments.uid = 1 or 1 = 1) AND (.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((node_comments__term_node.tid = 3) OR (node_comments__term_node2.tid = 1809))) ORDER BY comments_timestamp DESC LIMIT 0, 10 in /pathtosite2/sites/all/modules/views/includes/view.inc on line 731.
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(node.nid), node.title AS node_title, node.type AS node_type, n' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node.type AS node_type, node_comment_statistics.comment_count AS node_comment_statistics_comment_count FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node_comment_statistics.comment_count >= 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_comment_statistics_comment_count DESC ) count_alias in /pathtosite2/sites/all/modules/views/includes/view.inc on line 705.
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(node.nid), node.title AS node_title, node.type AS node_type, n' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node.type AS node_type, node_comment_statistics.comment_count AS node_comment_statistics_comment_count FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_node term_node2 ON node.vid = term_node2.vid WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (node_comment_statistics.comment_count >= 1) AND (node.type in ('blog', 'poll', 'image', 'bubble', 'event', 'groups', 'page', 'story', 'topichub')) AND (((term_node.tid = 3) OR (term_node2.tid = 1809))) ORDER BY node_comment_statistics_comment_count DESC LIMIT 0, 10 in /pathtosite2/sites/all/modules/views/includes/view.inc on line 731.

febbraro’s picture

febbraro commented

I have not tested any of this on MySQL 4.1, that could be an issue, but unlikely. It also appears to have nothing to do with popular_node. It looks like the SQL problem is when constructing the content type filter...

AND (.type in ('blog', 'article', 'audio', 'event', 'op_image', 'video'))

Notice the missing alias. more investigation is needed though.

myersca’s picture

myersca commented

I am willing to help with the investigation. Let me know what I need to do.

I will keep looking and tweaking. The first error shows up everywhere but the home page and the admin pages. It shows up in content, user pages and even access denied pages.

myersca’s picture

myersca commented

Turning dev on gave some more info:

user warning: Invalid use of group function query: _get_topic_hubs_by_node_count /* webmaster : _get_topic_hubs_by_node_count */ SELECT DISTINCT n.title, n.nid FROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', ' Person Professional Past', ' Person Political Past') GROUP BY t.tid ORDER BY COUNT(n.nid) DESC LIMIT 5 in /pathtosite/sites/all/modules/popular_terms/popular_terms.module on line 216.

febbraro’s picture

febbraro commented

@myersca For your initial SQL bug, no word on that yet.

For the Topic Hubs bug, that is an issue with Views version 6.x 2.5, we will be pushing a version of OpenPublish soon with a revert to Views 6.x 2.3 that does not contain this bug. You can feel free to revert directly.

Will try to get more info on the first issue.

brenk28’s picture

brenk28 commented

@myersca, could you try replacing line 212 in popular_term.module from:

$sql .= 'GROUP BY t.tid ';

to

$sql .= 'GROUP BY n.title ';

and see if that fixes the error.

mwoodwar’s picture

mwoodwar commented

I'm not the original poster, but saw this and tried the change. I was breathless...but it gave me the following:

user warning: Invalid use of group function query: SELECT DISTINCT n.title, n.nid FROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', '') GROUP BY n.title ORDER BY COUNT(n.nid) DESC LIMIT 5 in /home/workmail/public_html/publish/sites/all/modules/popular_terms/popular_terms.module on line 216.

myersca’s picture

myersca commented

Here is what I get:

user warning: Invalid use of group function query: SELECT DISTINCT n.title, n.nid FROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', ' Person Professional Past', ' Person Political Past') GROUP BY n.title ORDER BY COUNT(n.nid) DESC LIMIT 5 in /pathtosite/sites/all/modules/popular_terms/popular_terms.module on line 216.

Sorry for the delay, I will be checking in more frequently. Thanks for your help.

brenk28’s picture

brenk28 commented

All right, I think this should fix it. Change line 194 from

$sql = 'SELECT DISTINCT n.title, n.nid ';

to

$sql = 'SELECT DISTINCT n.title, n.nid, COUNT(n.nid) as count ';

and lines 212-213 from

$sql .= 'GROUP BY t.tid ';
$sql .= 'ORDER BY COUNT(n.nid) DESC ';

to

$sql .= 'GROUP BY n.title ';
$sql .= 'ORDER BY count DESC ';

The fix will be in the next release.

mwoodwar’s picture

mwoodwar commented

Well, I made the changes, and this is what I got"

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 'term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node' at line 1 query: SELECT DISTINCT n.title, n.nid, COUNT(n.nid) as countFROM term_node t INNER JOIN topichub_condition thc ON thc.tid = t.tid INNER JOIN node n ON n.nid = thc.nid INNER JOIN term_data td ON t.tid = td.tid INNER JOIN vocabulary v ON v.vid = td.vid WHERE n.status = 1 and t.tid AND td.name NOT IN ('Other', ' Person Professional', ' Quotation', ' Person Political', ' Person Travel', '') GROUP BY n.title ORDER BY COUNT DESC LIMIT 5 in /home/workmail/public_html/publish/sites/all/modules/popular_terms/popular_terms.module on line 216.

brenk28’s picture

brenk28 commented

mwoodwar, make sure there is a space after count in line 194, if you look at the query posted, there is no space between count and FROM.

mwoodwar’s picture

mwoodwar commented

EUREKA...that was it, thanks. I know where I'll be spending my time today :>)

febbraro’s picture

febbraro commented
Status: Active » Fixed

Great, thanks Bill, and thanks for verifying @mwoodwar.

The fix is specified in http://drupal.org/node/438516#comment-1554066 and will we include it in the next release with a build number > 268.

myersca’s picture

myersca commented

I will confirm the fix worked for me. Thanks!

Status: Fixed » Closed (fixed)

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