[drupal-devel] [bug] IF in forum SQL not supported by postgresql

[Zed Pobre]

 Project:      Drupal
-Version:      4.5.1
+Version:      4.5.2
 Component:    forum.module
 Category:     bug reports
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  michaelemeyers
 Updated by:   Zed Pobre
-Status:       active
+Status:       patch
 Attachment:   http://drupal.org/files/issues/forum_3.patch (3.17 KB)

I'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.


Zed Pobre



Previous comments:
------------------------------------------------------------------------

December 14, 2004 - 16:32 : michaelemeyers

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,');


------------------------------------------------------------------------

January 10, 2005 - 16:34 : Anonymous

just wanted to chime in... this patch works perfectly.

thanks,
aaron

-- 
View: http://drupal.org/node/14352
Edit: http://drupal.org/project/comments/add/14352