Hi,
I just upgraded to version 7.x-1.x-dev as 7.x-1.0-rc1 was failing with "missing uid". After upgrading I get the following error:
PDOException: SQLSTATE[HY000]: General error: 1111 Invalid use of group function: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM (SELECT t.* FROM {search_api_db_catalog_index_title} t WHERE (word = :db_condition_placeholder_18) UNION ALL SELECT t.* FROM {search_api_db_catalog_index_search_api_access_node} t WHERE (word = :db_condition_placeholder_19) ) t INNER JOIN {search_api_db_catalog_index_status} t_2 ON t.item_id = t_2.item_id INNER JOIN {search_api_db_catalog_index_author} t_3 ON t.item_id = t_3.item_id WHERE (( (t_2.value = :db_condition_placeholder_0) OR (t_3.value = :db_condition_placeholder_1) )AND( (t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_2) OR (word = :db_condition_placeholder_3) OR (word = :db_condition_placeholder_4) )) t GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs3) )) OR (t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_5) OR (word = :db_condition_placeholder_6) OR (word = :db_condition_placeholder_7) OR (word = :db_condition_placeholder_8) OR (word = :db_condition_placeholder_9) )) t GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs5) )) OR (t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_10) OR (word = :db_condition_placeholder_11) OR (word = :db_condition_placeholder_12) OR (word = :db_condition_placeholder_13) OR (word = :db_condition_placeholder_14) )) t GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs5) )) OR (t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_15) OR (word = :db_condition_placeholder_16) OR (word = :db_condition_placeholder_17) )) t GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs3) )) )) GROUP BY t.item_id) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 37 [:subs3] => 3 [:db_condition_placeholder_2] => node [:db_condition_placeholder_3] => access [:db_condition_placeholder_4] => all [:subs5] => 5 [:db_condition_placeholder_5] => node [:db_condition_placeholder_6] => access [:db_condition_placeholder_7] => content [:db_condition_placeholder_8] => author [:db_condition_placeholder_9] => 37 [:db_condition_placeholder_10] => node [:db_condition_placeholder_11] => access [:db_condition_placeholder_12] => content [:db_condition_placeholder_13] => rid [:db_condition_placeholder_14] => 2 [:db_condition_placeholder_15] => node [:db_condition_placeholder_16] => access [:db_condition_placeholder_17] => all [:db_condition_placeholder_18] => laptop [:db_condition_placeholder_19] => laptop ) in SearchApiDbService->search() (line 625 of /usr/share/drupaleam/sites/all/modules/search_api_db/service.inc).
Checking the SQL, I couldn't see any problem... Any ideas of what might be causing this problem?
Some information:
- problem doesn't happen with userid = 1 (Administrator)
- I am using the content_access module (tested both beta1 and dev versions)
- problem happens in drupal 7.8, 7.9 and 7.10 (in drupal 7.7, there was a different problem with mismatched number of parameters)
Comments
Comment #1
linacio commentedAfter some analysis, I think there is a problem with the SQL...
Instead of:
(t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_2) OR (word = :db_condition_placeholder_3) OR (word = :db_condition_placeholder_4) )) t GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs3) ))
it should be:
(t.item_id IN (SELECT t.item_id AS item_id FROM (SELECT t.item_id AS item_id FROM {search_api_db_catalog_index_search_api_access_node} t WHERE ( (word = :db_condition_placeholder_2) OR (word = :db_condition_placeholder_3) OR (word = :db_condition_placeholder_4) ) GROUP BY t.item_id HAVING (COUNT(DISTINCT t.word) >= :subs3) ) t ))
That means moving the GROUP BY and HAVING to the inner SELECT.
Can anybody check if this is really the problem?
Btw, the PDOException: SQLSTATE[HY000] error only happens when running the SQL in Drupal because of the sql_mode being used...
Regards,
Luiz
Comment #2
drunken monkeyShould be already fixed in dev, and thus also in the (very soon) upcoming 1.0 release.
Comment #3
linacio commentedI just tested the new 1.0 version from Dec 15 and the problem isn't solved...
I still think that the SQL generated by service.inc (in search_api_db module) for checking node_access is wrong.