MYSQL - MATCH AGAINST score value = 0 but is >0 in WHERE clause

Question

I have 2 similar DB, on 2 servers.
If i try to execute this request one each one :

SELECT u.lastname, MATCH (u.lastname) AGAINST ('Rod*' IN BOOLEAN MODE)
FROM user u
WHERE MATCH (u.lastname) AGAINST ('Rod*' IN BOOLEAN MODE) > 0 ;

On the first server, it give me results with a numeric value as score of the match against.

One the second one, I got the same results, so the where clause is really applied, but the score is always = to 0.

Do you have any idea ? I guess it could be a specific conf of mysql... but I don't know which one to check.

Thanks very much

Answer 1

Select from a derived table:

SELECT lastname, score FROM (
SELECT u.lastname, MATCH (u.lastname) AGAINST ('Rod*' IN BOOLEAN MODE) AS score 
FROM user u 
) as derivedTable WHERE score <> 0 ORDER BY score DESC;