I am running an Apache 2.2 + PHP 5.2.10-2ubuntu6.4 + PGSQL 8.4 + D6.19 site. As a result of searches for 'foo', I have a number of errors in my site logs that look like this:
query: SELECT i.type, i.sid, 5 * (2.3449682843 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp) || 1) - 1283502131) * 6.43e-8) + 4 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.25)) + 5 * (2.0 - 2.0 / (1.0 + MAX(nc.totalcount) * 0.000954198473282)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE n.status = 1 AND (i.word = 'foo') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 10 OFFSET 0 in /usr/share/drupal6/modules/search/search.module on line 958.
Trying to execute the query in the psql command line complains about the " || 1" in the query. I can get a result on the psql command line if I replace the "|| 1" with a "GREATEST(..., 1)" construct like so:
SELECT i.type, i.sid, 5 * (2.3449682843 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), GREATEST(MAX(c.last_comment_timestamp), 1)) - 1283502131) * 6.43e-8) + 4 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.25)) + 5 * (2.0 - 2.0 / (1.0 + MAX(nc.totalcount) * 0.000954198473282)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE n.status = 1 AND (i.word = 'foo') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 10 OFFSET 0;
What I cannot determine is (1) does this new query produce a valid result, and (2) where the " || 1" is generated. I don't see it in the search.module file, which means it may be buried in the PostgreSQL query-making PHP code.
Comments
Comment #1
josh waihi commentedThis breaks PostgreSQL support in Drupal 6. Its crucial this is fixed fast. Here is a patch that works. IT simply uses GREATEST instead of || which is compliant and makes sense.
Comment #3
damien tournoud commentedThis is a duplicate of #488166: Search relevance calculation fails if last_comment_timestamp is NULL.
Comment #4
andrea.vivaldi commentedsubscribe.