Last post informat incorrect in forum listing

Apparently, my PHP/MySQL installation is a perfect ground for testing "grey area" cases! After launching the forum on my website, I have found that the latest post information was incorrect in forum listing. Drupal would simply pull out a wrong post (not the most recent one) and present it as the most recent.

The problem lies in advanced_forum.module, line 628:

  // Query the info about the latest topic for the given forum
  $query = "
    SELECT n.title AS topictitle, 
           r.title AS replytitle, 
           r.time timestamp, 
           r.type AS replytype, 
           n.type AS topictype, 
           n.nid AS topicid, 
           t.tid, 
           r.cid AS cid, 
           r.uid,
           u.name
    FROM {node} AS n 
    INNER JOIN 
      (
        (SELECT title, 
                created AS time,
                nid, 
                uid, 
                type, 
                'cid' AS cid 
         FROM {node}
         )
         UNION 
         (SELECT subject, 
                  timestamp, 
                  nid, 
                  uid, 
                  'comment', 
                  cid 
           FROM {comments}
           WHERE {comments}.status = %d
         ) 
         ORDER BY time DESC
      ) AS r ON n.nid=r.nid 
      INNER JOIN {term_node} AS t ON n.nid = t.nid 
      INNER JOIN {users} AS u ON r.uid = u.uid
      WHERE n.status = 1 
      GROUP BY tid;";

By ORDERing BY time on line 661, the author expects GROUP BY on line 666 to preserve the first occurrence of the row. However, the 666th line presents a devilish trick, since MySQL doesn't guarantee that GROUP BY will take this order into account. And in my case, it does not.

Instead, one should use one of the recipes mentioned here for pulling the latest post by forum:

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row....

The code below fixes that.

  // Query the info about the latest topic for the given forum
  $query = "
    SELECT n.title AS topictitle, 
           r.title AS replytitle, 
           r.time timestamp, 
           r.type AS replytype, 
           n.type AS topictype, 
           n.nid AS topicid, 
           t.tid, 
           r.cid AS cid, 
           r.uid,
           u.name
    FROM {node} AS n 
    INNER JOIN 
      (
        (SELECT title, 
                created AS time,
                nid, 
                uid, 
                type, 
                'cid' AS cid 
         FROM {node}
         )
         UNION 
         (SELECT subject, 
                  timestamp, 
                  nid, 
                  uid, 
                  'comment', 
                  cid 
           FROM {comments}
           WHERE {comments}.status = %d
         ) 
      ) AS r ON n.nid=r.nid 
      INNER JOIN {term_node} AS t ON n.nid = t.nid 
      INNER JOIN {users} AS u ON r.uid = u.uid
      
    INNER JOIN    
      (SELECT MAX(r.time) AS timestamp, t.tid AS termid
       FROM {node} AS n
       INNER JOIN 
         (
           (SELECT created AS time, nid
            FROM {node}
            )
            UNION 
            (SELECT timestamp AS time, nid
             FROM {comments}
             WHERE comments.status =0
             )
         ) AS r ON n.nid = r.nid
       INNER JOIN {term_node} AS t ON n.nid = t.nid
       WHERE n.status = 1
       GROUP BY tid
     ) AS latest
     ON t.tid = latest.termid AND r.time = latest.timestamp";