postgresql 7.4.5 / drupal 4.5.1
forum module version information:
// $Id: forum.module,v 1.217 2004/11/24 22:56:21 dries Exp $
in the forum module, the IF statement in the SQL queries is not postgresql compliant:
IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name
needs to be a case statement (or an IF function in postgresql could be written...):
CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name
case statements are supported by both mySQL and postgresql...
--------------------------
IF results in errors:
--------------------------
warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "13" in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 45.
user error:
query: SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n , node_comment_statistics l, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 354 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 62.
warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "2" in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 45.
user error:
query: SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n , node_comment_statistics l, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 353 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 62.
--------------------------
suggested patch:
--------------------------
function forum_get_forums($tid = 0) { // ~at around line 380:
// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics} l /*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC', $forum->tid, 0, 1));
// add:
++ $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics} l, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC', $forum->tid, 0, 1));
function _forum_topics_read($term, $uid) { // ~at around line 420:
// remove:
-- $sql = "SELECT DISTINCT(n.nid), f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM {node} n ". node_access_join_sql() .", {node_comment_statistics} l, {users} cu, {term_node} r, {users} u, {forum} f WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = $check_tid AND n.uid = u.uid AND n.nid = f.nid AND ". node_access_where_sql();
$sql .= tablesort_sql($forum_topic_list_header, 'n.sticky DESC,');
// add:
++ $sql = "SELECT DISTINCT(n.nid), f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM {node} n ". node_access_join_sql() .", {node_comment_statistics} l, {users} cu, {term_node} r, {users} u, {forum} f WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = $check_tid AND n.uid = u.uid AND n.nid = f.nid AND ". node_access_where_sql();
$sql .= tablesort_sql($forum_topic_list_header, 'n.sticky DESC,');
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | forum_3.patch | 3.17 KB | Zed Pobre |
Comments
Comment #1
(not verified) commentedjust wanted to chime in... this patch works perfectly.
thanks,
aaron
Comment #2
Zed Pobre commentedI'll chime in noting that it works for me as well. I've attached the above solution as an actual patch file to 4.5.2.
Comment #3
chx commentedhttp://drupal.org/node/11903