[drupal-devel] Trouble with tracker SQL queries

[K B]

I have a troubling issue that I did partially solve, but need feedback
from other developers before I determine if this is a general issue
and open an issue for it, or just specific to my sites.

Background:

I have several sites that were upgraded from 4.5 to 4.6 a few days
ago. These sites were originally started as 4.4 (or whatever the
current version on Jan 2004 was), then moved to 4.5 then 4.6.

I have never enabled comments on those sites, since I did not need to.

Now that I have converted to 4.6, I found out that tracker does not
work (displays headers and an empty list of nodes).

The query in tracker that does that is:

SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name,
l.last_comment_timestamp AS last_post, l.comment_count FROM be_node n
INNER JOIN be_node_comment_statistics l ON n.nid = l.nid INNER JOIN
be_users u ON n.uid = u.uid LEFT JOIN be_comments c ON n.nid = c.nid
AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid =
1 OR c.uid = 1) ORDER BY last_post DESC

Since node_comment_statistics table is empty in my case, no rows were
returned. Changing the first INNER to LEFT took care of this part.

Then I found that the nodes were not sorted correctly. This was
because last_post returned NULL. I changed the ORDER BY to be
"n.changed" instead of last_post and that took care of the sorting.

So, the SQL now becomes:
SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name,
l.last_comment_timestamp AS last_post, l.comment_count FROM be_node n
LEFT JOIN be_node_comment_statistics l ON n.nid = l.nid INNER JOIN
be_users u ON n.uid = u.uid LEFT JOIN be_comments c ON n.nid = c.nid
AND (c.status = 0 OR c.status IS NULL) WHERE n.status = 1 AND (n.uid =
1 OR c.uid = 1) ORDER BY n.changed DESC

However, the last post column now has "35 years 20 weeks ago" because
the tracker.module tries to do "time() - last_posted", and since
last_posted is NULL, it gives the number of years since the epoch. I
overcame this by checking of last_post is null, and printing a -
instead of an interval.

I tried enabling comments, and did enter a few test comments. Still
the node_comment_statistics is empty, and hence the replies column
still shows 0 replies.

So, my question is:

- Did anyone else see a similar issue (node_comment_statistics table
is empty and tracker gives no results)?

- Is it normal to have an empty table, and not having anything
inserted in it even after enabling comments? As far as I can tell, the
inserts are done via the nodeapi of the comment.module, and are in
real time (not via cron for example)?

- Is this an exception case that we did not program for (someone has
comments disabled on their web site)?

Based on your replies, I will create a patch for the issue of having
correct tracker output.

I am still puzzled as to why no inserts are happening for
node_comment_statistics.