Search ranking broken on pgsql

Search.module currently does not work properly on PGSQL.

As part of the ranking, the column search_index.score is divided by search_total.count, and summed over all matching words:

$query = "SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM {search_index} i $join INNER JOIN {search_total} t ON i.word = t.word WHERE $conditions GROUP BY i.type, i.sid ORDER BY score DESC";

These two columns are stored as integers, but the division is expected to result in a floating point number.

MySQL does this, PGSQL truncates the result to an integer. This will always result in 0 (as score < count), which means the ranking is always 0.

We can cast to float using CAST(i.score AS float) in PGSQL, but MySQL does not recognize 'float'. Instead, it seems you can't cast to float at all.

It seems we need some form of casting support in the db abstraction layer... where the mysql version would have to resort to "column + 0.0" or something like that.

The alternative is to have separate PGSQL/MYSQL versions of this query. This isn't such a bad idea as we've already had to replace a COUNT(DISTINCT ...) query by a db_num_rows() call for PGSQL compatibility, which might degrade performance a bit.

(I submitted a bug to mysql about this: http://bugs.mysql.com/bug.php?id=9440 , though it won't help us now.)