For a rather complex dynamic query that my module generates, the above exception is always thrown: "PDOException: SQLSTATE[HY000]: General error: 1111 Invalid use of group function".
The produced SQL code is the following:
SELECT t.item_id AS item_id, SUM(t.score) AS score
FROM (
SELECT t.item_id AS item_id, SUM(t.score) AS score, :word1 AS word
FROM (
SELECT t.item_id AS item_id, SUM(t.score) AS score, :word1 AS word
FROM (
SELECT t.*
FROM {search_api_db_2_1_title} t
WHERE ( (word = :db_condition_placeholder_0) OR (word = :db_condition_placeholder_1) )
UNION ALL
SELECT t.*
FROM {search_api_db_2_1_body_value} t
WHERE ( (word = :db_condition_placeholder_0) OR (word = :db_condition_placeholder_1) )
) t
GROUP BY t.item_id
HAVING (COUNT(DISTINCT t.word) >= :subs2)
UNION ALL
SELECT t.item_id AS item_id, SUM(t.score) AS score, :word2 AS word
FROM (
SELECT t.*
FROM {search_api_db_2_1_title} t
WHERE ( (word = :db_condition_placeholder_2) OR (word = :db_condition_placeholder_3) )
UNION ALL
SELECT t.*
FROM {search_api_db_2_1_body_value} t
WHERE ( (word = :db_condition_placeholder_2) OR (word = :db_condition_placeholder_3) )
) t
GROUP BY t.item_id
HAVING (COUNT(DISTINCT t.word) >= :subs2)
) t
GROUP BY t.item_id
) t
WHERE (t.item_id NOT IN (
SELECT t.item_id AS item_id
FROM (
SELECT t.item_id AS item_id
FROM {search_api_db_2_1_title} t
WHERE (word = :db_condition_placeholder_0)
UNION ALL
SELECT t.item_id AS item_id
FROM {search_api_db_2_1_body_value} t
WHERE (word = :db_condition_placeholder_0)
) t
HAVING (COUNT(DISTINCT t.word) >= :subs1)
))
GROUP BY t.item_id
HAVING (COUNT(DISTINCT t.word) >= :subs1)
ORDER BY score DESCHowever, when I enter the exact same code (just with placeholders replaced) into phpMyAdmin, the database has no problems with it. Therefore I assume that this is a restriction imposed directly by PDO – is that reasonable?
In any case, I'm at a complete loss as to why this exception is thrown. As far as I know, the query looks completely correct (and, as said, the MySQL server seems to agree). Following some related solutions on the internet (although they didn't exactly apply to this problem) I even tried to move all COUNT(DISTINCT word)s from the HAVING clauses directly to the SELECT (as a selected expression), and then filter on HAVING (num >= :subs2), but the problem remained.
Comments
Comment #1
damien tournoud commentedDrupal 7 runs in a stricter mode of MySQL (
SET sql_mode='ANSI,TRADITIONAL') then you are probably used to. In this mode, MySQL is less lignent with the queries that are incomplete or do not really make sense.For example, this bit from your query:
This doesn't make sense without a
GROUP BY t.item_id.Comment #2
drunken monkey> This doesn't make sense without a
GROUP BY t.item_id.Thanks a million! That really was it, I seem to have always overlooked that. >__<
And also thanks for the explanation regarding the stricter mode, now really everything makes sense! (At least regarding this issue, quantum chromodynamics still appear rather fuzzy to me…)
Problem solved, issue fixed.
Comment #4
conejo commentedI'm still having this issue, even after update to drupal 7.8, with search_api module
What did I do wrong in the update?