I'm getting Postgres SQL errors when inserting into the index tables. I believe it's because the "score" column is a bigint type, but the value being inserted is a decimal.

Example from postgres log:
UTC ERROR: invalid input syntax for integer: "1976.8508287293" at character 1934
UTC STATEMENT: INSERT INTO search_api_db_user_index_text (item_id, field_name, word, score) VALUES ... ('64', 'field_brief_bio:value', 'state', '1976.8508287293') ...

This may be related to a recent change to convert score to integer (see related issues).

The attached patch allows the indexing not to throw the error. Is there a better way to approach this?

CommentFileSizeAuthor
#1 search_api_db-Force_score_to_be_int-2231829.patch524 byteskrisahil
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

krisahil’s picture

krisahil CreditAttribution: krisahil commented
Issue summary: View changes
FileSize
search_api_db-Force_score_to_be_int-2231829.patch524 bytes

  • Commit 5c9124d on 7.x-1.x authored by krisahil, committed by drunken monkey: 
    Issue #2231829 by krisahil, drunken monkey: Fixed indexed scores to be...
drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
CreditAttribution: drunken monkey commented

Thanks a lot for reporting this issue and providing a patch!
This is of course nonsense and should be fixed, and your patch looks like the right way to do it. I just adapted it slightly and then committed it.
Thanks a lot again!

However, now I also wonder/worry about the following code when fields are updated (line 467 pp.):

$multiplier = $new_fields[$name]['boost'] / $field['boost'];
$this->connection->update($text_table)
  ->expression('score', 'score * :mult', array(':mult' => $multiplier))
  ->condition('field_name', self::getTextFieldName($name))
  ->execute();

If the multiplier isn't an integer, does that lead to an error? Could you maybe test this for me? Just set any fulltext field's boost (for an index with indexed items) to a new value that isn't a whole multiple of the old value (using one that is smaller, e.g.). If this also leads to an error, please re-open and we'll think about how to fix it.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
CreditAttribution: drunken monkey commented
Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

ralf57’s picture

ralf57 CreditAttribution: ralf57 commented

Hi,
reopening the issue since I needed to also type cast in the "createKeysQuery" method of the "service.inc" file.
More specifically, to get the multiple-word search work, I needed to replace

$alias = $db_query->addExpression("t.word LIKE '%" . $this->connection->escapeLike($word) . "%'", $alias);

with

$alias = $db_query->addExpression("(t.word LIKE '%" . $this->connection->escapeLike($word) . "%')::int", $alias);

Dunno if the syntax is also MySql friendly. If so, I will be glad to provide a patch.

ralf57’s picture

ralf57 CreditAttribution: ralf57 commented
Status: Closed (fixed) » Needs review

Status: Needs review » Needs work

The last submitted patch, 1: search_api_db-Force_score_to_be_int-2231829.patch, failed testing.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
CreditAttribution: drunken monkey commented
Status: Needs work » Closed (fixed)

This is only slightly related to the original issue – please open a new one. (Also, "Active" would have been the correct status, not "Needs review".)