Incorrect new posts count when topics are moved with revisions on

I investigated this issue http://drupal.org/node/284392 and this issue http://drupal.org/node/113611 and while it seems likely this is related to db_rewrite_sql issues, the issues addressed in 284392 are fixed in Drupal 6.16 and the fix in 56 of 113611 did not fix this problem. Since the problem does not occur with Advanced Forum disabled and I have disabled all access control modules and the problem persists, it seems likely that the issue fixed in core with 113611, post 56, exists in Advanced Forum, but no patch has been prepared. Any thoughts? Am I even on the right track?

Thanks for communicating. It's not urgent for me as this is a simple sports group site, but I'll look at it again in a day or two and see if I get any further. I've identified the section of code that does the count, but haven't messed with modifying it or tracking down how it interacts with everything else yet. Right now I'm looking at this issue http://drupal.org/node/349675#comment-2350532 to see if it can point me in the right direction to fix this. It's not the same issue (root user sees incorrect posts as well) and it doesn't happen every time, but I'll check it out.

The number of miscounted posts is a bit mysterious. Right now it seems that new comments are pretty consistently counted twice, regardless of the number of roles a user is assigned (thought it might be related to roles, but removing additional roles didn't help). But using an account that hasn't read any threads for a while and is only an authenticated user, the number of new posts is not twice the actual number of new posts. Once inside a particular forum, the count of the number of new posts in each individual thread is always right. It's just the count at the base /forum page summarizing the number of new posts to the threads in each of the forums that is wrong. I am not using node comment and the 1st post, the node, seems to never be counted twice (the new topics column is always correct and if a new topic is created with no comments, the new posts count will be correct, i.e. 1).

You are right, of course, there is no new posts count without AF so it works. I have sent you login information through your contact page at shellmultimedia for an account where you can see the problem. Thanks for looking at this so quickly.

Here are the corrected links from above, for anyone else who may find this later or be following along (left out the subdirectory):
Problem
Inside the last forum
Without AF

Forgot to update status

mysql Ver 14.14 Distrib 5.1.45, for unknown-linux-gnu (x86_64) using readline 5.1

I had identified the section and wanted to play with the code, but my drupal/PHP ignorance was making the %d a mystery. I have now educated myself on PHP placeholders and messed with with the query further in SQL. Simply changing this:
$sql = "SELECT COUNT(c.cid) AS count, f.tid
to this:
$sql = "SELECT COUNT(DISTINCT(c.cid)) AS count, f.tid
fixes it, but I haven't tested for any other problems.

Well, I messed around with it for a while now and I think I finally found it. It appears the forum table has multiple entries for some of the nids which have the same tid, but different vids. One thread has 8 different vid numbers, so a comment posted there gets counted 8 times. Most only have one or two vid numbers. Why would a forum topic get multiple vids?

I have to run right now and I haven't tested this yet, but something along these lines may be viable to avoid using DISTINCT:

SELECT c1.cid, c1.tid
FROM (
SELECT c.cid, f.tid, c.nid, c.timestamp, c.status 
FROM comments c, forum f 
WHERE c.nid = f.nid 
GROUP BY c.cid
) c1 
LEFT JOIN history h ON c1.nid = h.nid AND h.uid = 4
WHERE c1.status =0
AND c1.timestamp > UNIX_TIMESTAMP( ) -2592000
AND (
c1.timestamp > h.timestamp
OR h.timestamp IS NULL
)

This code:

SELECT COUNT(c.cid) as count, f1.tid
FROM comments c
INNER JOIN (
SELECT f.nid, f.tid
FROM forum f 
GROUP BY f.nid
) f1 ON c.nid = f1.nid
LEFT JOIN history h ON c.nid = h.nid AND h.uid =4
WHERE c.status =0
AND c.timestamp > UNIX_TIMESTAMP( ) -2592000
AND (
c.timestamp > h.timestamp
OR h.timestamp IS NULL
)
GROUP BY f1.tid 
+-------+-----+
| count | tid |
+-------+-----+
|    41 |   1 |
|    24 |   2 |
|    11 |   3 |
|    35 |   4 |
|    65 |   5 |
|    56 |   6 |
|     1 |   9 |
+-------+-----+
7 rows in set (0.00 sec)

And this code:

SELECT COUNT(DISTINCT(c.cid)) AS count, f.tid
FROM comments c
INNER JOIN forum f ON c.nid = f.nid
LEFT JOIN history h ON c.nid = h.nid AND h.uid = 4
WHERE c.status = 0 AND c.timestamp > UNIX_TIMESTAMP( ) -2592000 
AND (
c.timestamp > h.timestamp 
OR h.timestamp IS NULL
)
GROUP BY f.tid
+-------+-----+
| count | tid |
+-------+-----+
|    41 |   1 |
|    24 |   2 |
|    11 |   3 |
|    35 |   4 |
|    65 |   5 |
|    56 |   6 |
|     3 |   8 |
|     1 |   9 |
+-------+-----+
8 rows in set (0.01 sec)

both seem to match the actual new comments in the topics, EXCEPT they assume that the first value of f.tid for a given f.nid is correct, which it generally won't be. At least one reason for multiple vids being generated is if a topic is moved from one forum to another. With the DISTINCT route, this topic appears to be counted twice, once in the original forum and again in the new forum. With the GROUP BY subquery the topic appears to be counted in it's original forum, not the new one. I haven't solved that yet.

Okay, had another minute to look at it (and this has turned into a bit of a fun puzzle) and this seems to get the right results, but could probably be simplified.

SELECT COUNT(c.cid) AS count, f1.tid
FROM comments c
INNER JOIN (
SELECT f.nid, f.tid
FROM forum f
LEFT JOIN forum ff ON f.nid = ff.nid AND f.vid < ff.vid
WHERE ff.nid IS NULL
) f1 ON c.nid = f1.nid
LEFT JOIN history h ON c.nid = h.nid AND h.uid =4
WHERE c.status =0
AND c.timestamp > UNIX_TIMESTAMP( ) -2592000
AND (
c.timestamp > h.timestamp
OR h.timestamp IS NULL
)
GROUP BY f1.tid 
+-------+-----+
| count | tid |
+-------+-----+
|    41 |   1 |
|    23 |   2 |
|    11 |   3 |
|    32 |   4 |
|    65 |   5 |
|    56 |   6 |
|     3 |   8 |
|     1 |   9 |
+-------+-----+

It seems to me the php version should be:

      $sql = "SELECT COUNT(c.cid) AS count, f1.tid
              FROM {comments} c
              INNER JOIN (SELECT f.nid, f.tid FROM {forum} f 
                         LEFT JOIN {forum} ff ON f.nid = ff.nid AND f.vid < ff.vid
                         WHERE ff.nid IS NULL
              ) f1 ON c.nid = f1.nid
              LEFT JOIN {history} h ON c.nid = h.nid AND h.uid = %d
              WHERE c.status = 0 AND c.timestamp > %d AND (c.timestamp > h.timestamp OR h.timestamp IS NULL)
              GROUP BY f1.tid";

But in practice, the count gets bumped +1 for every thread in which the initial post has not been read. Does that make sense/look right to you Michelle?

Edit: The +1 bump doesn't always happen with new topics. For user 4, the one I sent you, it happens in tid 4 and 1, but not in 9. The number returned is correct when I do the query in mySQL, but the result returned by my php above is not so perfect.