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 DESC

However, 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

damien tournoud’s picture

Drupal 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:

    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)

This doesn't make sense without a GROUP BY t.item_id.

drunken monkey’s picture

Status: Active » Fixed

> 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.

Status: Fixed » Closed (fixed)

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

conejo’s picture

I'm still having this issue, even after update to drupal 7.8, with search_api module

What did I do wrong in the update?