Query to get nodes to index seems broken

Okay, this bug has been fixed through a re-factoring in Drupal-6...

However, it seems it is still there in Drupal-5.

Here is an apt description;
http://drupal.org/node/42277#comment-846833

There is a patch attached to that node. I suggest we rip that query from there and test and apply.

For the D6 branch, I'm thinking we might want to look into refactoring it to use the new method used in node.module... Although I haven't a clue how it works. I can commit to fixing the D5 branch, can someone else more knowledge about D6 tackle the other one?

If you're curious about the difference, here it is:

node.module in D5:

function node_update_index() {
  global $last_change, $last_nid;

  register_shutdown_function('node_update_shutdown');

  $last = variable_get('node_cron_last', 0);
  $last_nid = variable_get('node_cron_last_nid', 0);
  $limit = (int)variable_get('search_cron_limit', 100);

  // Store the maximum possible comments per thread (used for ranking by reply count)
  variable_set('node_cron_comments_scale', 1.0 / max(1, db_result(db_query('SELECT MAX(comment_count) FROM {node_comment_statistics}'))));
  variable_set('node_cron_views_scale', 1.0 / max(1, db_result(db_query('SELECT MAX(totalcount) FROM {node_counter}'))));

  $result = db_query_range('SELECT GREATEST(IF(c.last_comment_timestamp IS NULL, 0, c.last_comment_timestamp), n.changed) as last_change, n.nid FROM {node} n LEFT JOIN {node_comment_statistics} c ON n.nid = c.nid WHERE n.status = 1 AND ((GREATEST(n.changed, c.last_comment_timestamp) = %d AND n.nid > %d) OR (n.changed > %d OR c.last_comment_timestamp > %d)) ORDER BY GREATEST(n.changed, c.last_comment_timestamp) ASC, n.nid ASC', $last, $last_nid, $last, $last, $last, 0, $limit);

In particular see the last bit:

ORDER BY GREATEST(n.changed, c.last_comment_timestamp) ASC, n.nid ASC', $last, $last_nid, $last, $last, $last, 0, $limit);

node.module in D6:

function node_update_index() {
  $limit = (int)variable_get('search_cron_limit', 100);

  // Store the maximum possible comments per thread (used for ranking by reply count)
  variable_set('node_cron_comments_scale', 1.0 / max(1, db_result(db_query('SELECT MAX(comment_count) FROM {node_comment_statistics}'))));
  variable_set('node_cron_views_scale', 1.0 / max(1, db_result(db_query('SELECT MAX(totalcount) FROM {node_counter}'))));

  $result = db_query_range("SELECT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = 'node' AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC", 0, $limit);

Works for me. I tested against 6.x too and it works.

Can I get a witness? If it is good, I'll commit it.