MariaDB “DOUBLE value is out of range” after a rebuild

If a search returns zero results in the first seconds after wp sss rebuild finishes, and your database log or the plugin’s db_errors field carries “DOUBLE value is out of range” alongside an SSS relevance expression, this article explains the cause and the fix.

The fix is to update Super Speedy Search to 5.60.1 or later. The rebuild refreshes each table’s row statistics before it swaps the freshly built table into place, which closes the window this error lives in.

Fixed in 5.60.1.

What you see

Searches performed in the seconds immediately after a rebuild return no results at all. In your MariaDB error log, or in the db_errors array returned by the sss/test-query ability:

ERROR 1690 (22003): DOUBLE value is out of range in '1 - octet_length(`yourdb`.`wp_superspeedysearch`.`post_title`) * 5 + (match `yourdb`.`wp_superspeedysearch`.`post_title`,`yourdb`.`wp_superspeedysearch`.`post_content`,`yourdb`.`wp_superspeedysearch`.`post_excerpt`,`yourdb`.`wp_superspeedysearch`.`taxonomydescriptions`,`yourdb`.`wp_superspeedysearch`.`metavalues`,`yourdb`.`wp_superspeedysearch`.`guid` against ('+iphone*' in boolean mode))'

Identifying features:

• It references wp_superspeedysearch (or your site’s prefix variant).
• It mentions an expression starting with 1 - octet_length(post_title) * 5 followed by one or more MATCH(...) AGAINST(... IN BOOLEAN MODE) calls.
• Error 1690, SQLSTATE 22003. The statement aborts, so the search returns zero rows rather than badly ordered rows.
• Repeating the same search a short time later returns the correct results with no error.

If the log line says anything else (syntax errors, “table doesn’t exist”, “Illegal mix of collations”), that is a different problem and the rest of this article does not apply.

Why it happens

The number in the expression is not merely large. It is infinite.

A DOUBLE holds any value between roughly -1.7976931348623157e308 and +1.7976931348623157e308, and an SSS relevance score for a typical product lands between about -400 and +20 — nowhere near the limit. MariaDB raises error 1690 when an arithmetic result is not finite, meaning infinity or NaN, rather than when it is simply big.

The infinity comes from InnoDB’s FULLTEXT ranking. Every MATCH() score is built from an inverse document frequency term:

idf = log10(total_docs / doc_count)

InnoDB reads total_docs from the table’s stored row statistics. A rebuild creates a new table, bulk-loads it, and renames it into place, and InnoDB recalculates those statistics on a background thread rather than at the end of the load. Until that thread runs, the stored row count reads zero, so the calculation becomes log10(0), which is negative infinity. Squaring it produces positive infinity, and every MATCH() against the table returns infinity until the statistics catch up.

The relevance expression adds that infinity to the title-length term, MariaDB refuses the arithmetic, and the query aborts.

The fix

Update Super Speedy Search to 5.60.1 or later, then run a rebuild:

wp plugin update super-speedy-search
wp sss rebuild

Each of the four tables the plugin builds with a FULLTEXT index has its statistics refreshed before the new table is swapped in: the main search table, the order-search index, the suggestions table and the taxonomy-combinations table. Refreshing before the swap rather than after it means the previous table stays live and serving searches throughout, so there is no moment at which a visitor’s search can reach a table whose statistics read zero.

The refresh samples a small number of index pages and completes in milliseconds, so it adds nothing measurable to rebuild time even on a multi-million-row catalogue.

If you cannot update yet

Run ANALYZE TABLE against the SSS tables straight after a rebuild. This does the same job by hand:

wp sss rebuild
wp db query "ANALYZE TABLE wp_superspeedysearch"

If your site builds the order-search, suggestions or taxonomy-combination tables too, include whichever of these exist:

wp db query "ANALYZE TABLE wp_superspeedysearch_orders"
wp db query "ANALYZE TABLE wp_superspeedysearch_suggestions"
wp db query "ANALYZE TABLE wp_sss_taxonomy_combinations"

Substitute your own table prefix if it is not wp_.

Verifying the cause on your own site

Two checks confirm you are looking at this and not something else. Both need database access.

First, compare the stored row count against the real one:

wp db query "SELECT n_rows FROM mysql.innodb_table_stats WHERE table_name = 'wp_superspeedysearch'"
wp db query "SELECT COUNT(*) FROM wp_superspeedysearch"

A stored n_rows of 0 against a real count in the thousands is the condition described here. Reading mysql.innodb_table_stats needs a database user with SELECT on the mysql schema, which a standard WordPress user does not have — run it as an administrative user.

Second, confirm the score is infinite rather than large:

wp db query "SELECT MATCH(post_title) AGAINST('+iphone*' IN BOOLEAN MODE) > 1e308 AS is_infinite FROM wp_superspeedysearch WHERE MATCH(post_title) AGAINST('+iphone*' IN BOOLEAN MODE) LIMIT 1"

A result of 1 means the score is infinite. ANALYZE TABLE wp_superspeedysearch followed by the same query returns 0 and a finite score.

What this is not

Sanity check the symptoms against this list before assuming this article applies:

• Not “syntax error in BOOLEAN MODE” – that is caused by FULLTEXT operator characters in user search input. See the separate article on dirty search input.
• Not “LIMIT -100” or similar negative-LIMIT errors – a separate defect, fixed in 5.53.12.
• Not “Table doesn’t exist” – the rebuild has not run, or failed midway. Run wp sss rebuild and read the output.
• Not “Illegal mix of collations” – a collation mismatch between wp_posts and wp_superspeedysearch. See the separate article on collation handling.

If your log line carries the exact phrase “DOUBLE value is out of range” alongside the SSS relevance expression and wp_superspeedysearch, you are in the right place.

Further reading

Related products

Super Speedy SearchSpeeds up front-end and wp-admin search