After updating from Drupal 9.5 to 10.2, searching on a view with a fulltext exposed filter and a facet using the AND operator results in the error: "A database exception occurred while searching.".
Drupal 10.2, Facets 2.0.6, Search API 1.3.
Everything works fine in Drupal 9.5. Deleting the facet solves the issue (ie fulltext search returns expected results). Changing the facet's operator to OR solves the issue. Note that the existence of the facet seems to cause the issue — it doesn't need to be active.
3 errors in the log each time it happens, each with the same error, except with a different "db_temporary_65d129de74afd":
Drupal\Core\Database\DatabaseExceptionWrapper while trying to create a temporary table: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drupal10.t.word' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: CREATE TEMPORARY TABLE "db_temporary_65d129de74afd" Engine=MEMORY SELECT DISTINCT "t"."item_id" AS "item_id" FROM (SELECT "t"."item_id" AS "item_id", CASE WHEN "t"."word" LIKE :like_w1 THEN 1 ELSE 0 END AS "w1" FROM "search_api_db_products_text" "t" WHERE ("t"."word" LIKE :db_condition_placeholder_0 ESCAPE '\\') AND ("t"."field_name" IN (:db_condition_placeholder_1)) GROUP BY "t"."item_id") "t"; Array ( [:db_condition_placeholder_0] => gloria% [:db_condition_placeholder_1] => title [:like_w1] => %gloria% ) in Drupal\search_api_db\Plugin\search_api\backend\Database->getTemporaryResultsTable() (line 2902 of /app/web/modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).
Each of the three errors has a different backtrace. I can post those if it's helpful.
Grateful for any direction on this.