MySQL Match Relevance Score causes "DOUBLE value is out of range" error

user_14465422 0
Viewed 375

I created a MySQL (MariaDB) fulltext search in my Windows XAMPP environment. Everything works well. But when putting it on the live server (also using MariaDB), I get a 1690 Double value is out of range error from it with the exactly same data.

I assume that the problem is somewhere in the MariaDB settings. I just cannot figure out, which setting this could be.

Here is the query simplified:

SELECT id, (MATCH (name) AGAINST ('"exactSearchPhrase"' IN BOOLEAN MODE)*3 + MATCH (name) AGAINST ('single*, Words*, Search*, Phrase*' IN BOOLEAN MODE)) as relevance, name FROM tableName WHERE MATCH(name) AGAINST('search Phrase' IN BOOLEAN MODE) ORDER BY relevance DESC

And this is the error I get

Numeric value out of range: 1690 DOUBLE value is out of range in '(match name against ('"exactSearchPhrase"' in boolean mode)) * 3

Does anyone have an idea which setting could cause this behaviour that might be different from the one in my standard Xampp environment?

Edit after some Try and Error: Any kind of operation with the match result causes this error. This confuses me even more:

No error: SELECT id, (MATCH (name) AGAINST ('"exactSearchPhrase"' IN BOOLEAN MODE)) as relevance, name FROM tableName WHERE MATCH(name) AGAINST('search Phrase' IN BOOLEAN MODE) ORDER BY relevance DESC

Error: SELECT id, (MATCH (name) AGAINST ('"exactSearchPhrase"' IN BOOLEAN MODE)+0) as relevance, name FROM tableName WHERE MATCH(name) AGAINST('search Phrase' IN BOOLEAN MODE) ORDER BY relevance DESC

Edit: I ended up building the whole table again. It seemed to be a problem with the fulltext index itself.

mariadb mysql
1 Answers

Try ANALYZE TABLE table_name. It worked for me.

I recently encountered the same issue with a DigitalOcean Managed Database, after a MySQL upgrade, which I believe involved switching over to a replica. I got the same error message "DOUBLE value is out of range" when I multiplied the relevance score by any number, including * 1. And the query succeeded when I ran it without the multiplication.

The relevance score seemed incorrect to me, judging by comparing the example in the MySQL docs to a similar search in my data. So I assumed that something was wrong with the FULLTEXT index, and ran ANALYZE TABLE, and then it worked correctly again.

I don't know the exact cause of the problem, but I do note the similarity of switching servers in my case and in the OP's case.

user_183330
Related