Issue #3225675: Full-text search: buggy aggregation of scores in the DB backend

modules/search_api_db/src/Plugin/search_api/backend/Database.php

@@ -1992,20 +1992,20 @@ class Database extends BackendPluginBase implements PluginFormInterface {
       $db_query = $this->database->select($field['table'], 't');
       $mul_words = ($word_count > 1);
       if ($neg_nested) {
-        $db_query->fields('t', ['item_id', 'word']);
+        $db_query->fields('t', ['item_id', 'field_name', 'word']);
       }
       elseif ($neg) {
-        $db_query->fields('t', ['item_id']);
+        $db_query->fields('t', ['item_id', 'field_name']);
       }
       elseif ($not_nested && $match_parts) {
-        $db_query->fields('t', ['item_id']);
+        $db_query->fields('t', ['item_id', 'field_name']);
         $db_query->addExpression('SUM(t.score)', 'score');
       }
       elseif ($not_nested || $match_parts) {
-        $db_query->fields('t', ['item_id', 'score']);
+        $db_query->fields('t', ['item_id', 'field_name', 'score']);
       }
       else {
-        $db_query->fields('t', ['item_id', 'score', 'word']);
+        $db_query->fields('t', ['item_id', 'field_name', 'score', 'word']);
       }
 
       if (!$match_parts) {
@@ -2015,6 +2015,15 @@ class Database extends BackendPluginBase implements PluginFormInterface {
         $db_or = $db_query->orConditionGroup();
         // GROUP BY all existing non-aggregated columns.
         foreach ($db_query->getFields() as $column) {
+          if ($column['field'] === 'score') {
+            // Score will be aggregated to get the sum of scores per item,
+            // so we do not want to GROUP BY score.
+            continue;
+          }
+
+          // At most: "item_id", "field_name" and "word".
+          // "field_name" is needed to sum the scores of a match
+          // across multiple fields for the same item.
           $db_query->groupBy("{$column['table']}.{$column['field']}");
         }