Numeric value out of range error

We are seeing this error too, but upon inspection the score column has numbers much bigger than the apparent error value. Weird!

Okay, so no idea where the number in the error message is coming from, but we're trying to insert some massive score values, that are larger than a unsigned 32bit integer.

I suppose that SearchAPI DB should min the desired score with the upper limit on the DB column.

CRAZY!

Got to the bottom of this for our site.

We are using the 'HTML filter' and the 'Ignore characters' processor on an HTML field.

'Ignore characters' runs first, and removes all the '\' from HTML closing tags.

'HTML filter' runs next and finds more and more tags with boosts, so the boosts get higher and higher as the recursion into the nested HTML tags gets more and more!

The solution for us was to change the weight of the 'Ignore characters' processor to be higher than the 'HTML Filter' so that the HTML characters we not changed.

Thanks a lot for reporting this issue and already providing a detailed analysis of the problem.
Seems we should do two things here:
- Change the default processor weights so that the HTML filter will run before the "Ignore characters" processors does.
- In the database backend, make sure the score can never exceed the maximum database column size (apparently 4 bytes in all three supported DBMSs).

Would be great if you could take a look at the attached patch and tell me if you think it makes sense like this!

I should point out that in the UI the weights of the processors only go from -10 to 10, so I think a separate issue needs to resolve that too, so that the ordering doesn't get wiped out if I load and save.

That being said, just from a visual review, the patch looks good to me!

I haven't tested the patch but it looks like it makes sense. I'm going to be bold and set this to RTBC.

OK, thanks you two!
Committed.

also check for score values below zero, happens for me:

$score = min((int) $score, 4294967295);
$score = max((int) $score, 0);

I'm also seeing negative values that need to be constrained to 0.

This issue still occurs due to code in fieldsUpdated() in search_api_db/src/Plugin/search_api/backend/Database.php that tries to avoid a reindex by running an UPDATE query on the score.

I temporarily worked around it by forcing $reindex=true in the code below (line 943) and re-indexing, but not sure what the ramifications of doing this actually are. We should either always reindex in this case, or we should apply the same logic as the above patch (although it might be tricky due to differences in DBMS).

          //$reindex = true; //ADD ME to get around the issue

          if (!$reindex) {
            // If there was a non-zero boost set previously, we can just update
            // all scores with a single UPDATE query. Otherwise, no way around
            // re-indexing.
            if ($field['boost']) {
              $multiplier = $new_fields[$field_id]->getBoost() / $field['boost'];
              // Postgres doesn't allow multiplying an integer column with a
              // float literal, so we have to work around that.
              $expression = 'score * :mult';
              $args = [
                ':mult' => $multiplier,
              ];
              if (is_float($multiplier) && $pos = strpos("$multiplier", '.')) {
                $expression .= ' / :div';
                $after_point_digits = strlen("$multiplier") - $pos - 1;
                $args[':div'] = pow(10, min(3, $after_point_digits));
                $args[':mult'] = (int) round($args[':mult'] * $args[':div']);
              }
              $this->database->update($text_table)
                ->expression('score', $expression, $args)
                ->condition('field_name', static::getTextFieldName($field_id))
                ->execute();
            }
            else {
              $reindex = TRUE;
            }

+++ b/modules/search_api_db/src/Plugin/search_api/backend/Database.php
@@ -1261,11 +1261,15 @@ protected function indexItem(IndexInterface $index, ItemInterface $item) {
+              $score = min((int) $score, 4294967295);

As noted in #11 when something causes the score to go negative, this min() solution doesn't work since an out-of-range score in the negative will be smaller than the max int size.

Following up on #4, the boost values can exceed PHP_FLOAT_MAX so I opened #3209167: Because of multiplying inside recursion, HTML Filter's tag boost can produce astronomical boost values, which besides being nonsensical, breaks the Solr boost_term in Search API Solr by exceeding PHP_FLOAT_MAX but that issue may belong in this project.

add patch with #11 to solve score field have negative number.

Re-roll