Random search results ordering with MySQL 5.0.13+

When searching nodes, I noticed that the score was null for every item in the results when some weight was given to the "Recently posted" factor.

Here is a sample query:

CREATE TEMPORARY TABLE temp_search_results SELECT i.type, i.sid, 5 * (6.98761587952 * i.relevance) + 2 * POW(2, (GREATEST(n.created, n.changed, c.last_comment_timestamp) - 1167851088) * 6.43e-8) AS score FROM temp_search_sids i INNER JOIN search_dataset d ON i.sid = d.sid AND i.type = d.type INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE (d.data LIKE '% montreal %') ORDER BY score DESC

The score part of that query is built by node_search().

The problem lies with the above use of GREATEST(), but it is a problem only for MySQL 5.0.13+ (as found here):

Incompatible change: Before MySQL 5.0.13, GREATEST(x,NULL) and LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.3, both functions return NULL if any argument is NULL, the same as Oracle. This change can cause problems for applications that rely on the old behavior.

In my tests, comments were disabled so c.last_comment_timestamp was always NULL, causing the whole score to become NULL. Removing c.last_comment_timestamp from the arguments to GREATEST() fixes the problem, but this is probably not the best solution. ;-)