Using the search api database with postgresql 9.1 resulted in the following error:
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "t_2.value" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT t.item_id AS item_id, t_2.value AS t_2value, SUM(t.sc... ^: SELECT t.item_id AS item_id, t_2.value AS t_2value, SUM(t.score) AS score FROM (SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_1} t WHERE (word = :db_condition_placeholder_0) UNION ALL SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_2} t WHERE (word = :db_condition_placeholder_1) ) t LEFT OUTER JOIN {search_api_db_product_display_field_product_commerce_price_a_1} t_2 ON t.item_id = t_2.item_id WHERE ( (t.item_id IN (SELECT t.item_id AS item_id FROM {search_api_db_product_display_field_product_commerce_price_a_1} t)) ) GROUP BY t.item_id ORDER BY t_2.value ASC LIMIT 1 OFFSET 0; Array ( [:db_condition_placeholder_0] => hao [:db_condition_placeholder_1] => hao ) in SearchApiDbService->search() (regel 773 van /home/stunthandel.nl/public/profiles/commerce_kickstart/modules/contrib/search_api_db/service.inc).
For the moment I just needed a quick fix:
... somewhere around line 754 in search_api_db/service.inc
...
throw new SearchApiException(t('Cannot sort on fulltext field @field.', array('@field' => $field_name)));
}
$alias = $this->getTableAlias($field, $db_query);
++ $db_query->groupBy($alias . '.value');
$db_query->orderBy($alias . '.value', $order);
}
}
else {
But this results in another error:
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "t.item_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT DISTINCT t.item_id AS item_id, t_2.value AS t_2value,... ^: SELECT DISTINCT t.item_id AS item_id, t_2.value AS t_2value, :score AS score FROM {search_api_db_product_display_search_api_language} t LEFT OUTER JOIN {search_api_db_product_display_field_product_commerce_price_a_1} t_2 ON t.item_id = t_2.item_id WHERE ( (t.item_id IN (SELECT t.item_id AS item_id FROM {search_api_db_product_display_field_product_commerce_price_a_1} t)) ) GROUP BY t_2.value ORDER BY t_2.value ASC LIMIT 1 OFFSET 0; Array ( [:score] => 1 ) in SearchApiDbService->search() (regel 765 van /home/stunthandel.nl/public/profiles/commerce_kickstart/modules/contrib/search_api_db/service.inc).
And by adding another fix after digging in another method:
protected function createDbQuery(SearchApiQueryInterface $query, array $fields) {
.... around line 850 ....
if (!isset($db_query)) {
$db_query = db_select($fields['search_api_language']['table'], 't', $this->query_options);
$db_query->addField('t', 'item_id', 'item_id');
+ $db_query->groupBy('t.item_id');
$db_query->addExpression(':score', 'score', array(':score' => 1));
$db_query->distinct();
}
This seems to work ok without errors.
| Comment | File | Size | Author |
|---|---|---|---|
| #11 | search_api_db_exception_on_pgsql-after_patch.jpg | 192.83 KB | gilsbert |
| #10 | search_api_db_exception_on_pgsql.jpg | 107.18 KB | gilsbert |
| #9 | 2140321-9--psql_sort_groupby.patch | 942 bytes | drunken monkey |
| #7 | 2140321-7--psql_sort_groupby.patch | 459 bytes | drunken monkey |
Comments
Comment #1
denegen commentedComment #2
denegen commentedComment #3
drunken monkeyThanks for reporting this issue! Always good to learn about bugs that only occur in other DBMSs – I really hate MySQL's lax handling of SQL errors.
However, in this case I can neither reproduce nor explain the error. (I'm not using Postgres, but I can't get the module to produce an SQL query like the one you posted.)
As far as I can say, such a query should never be produced by this module. There is only a single situation in which a
valuecolumn could be added to aSELECTquery, and that's when using a temporary table for computing facets – where the query would look completely different.Please make sure to try the latest dev version of this module (and the Search API). What other Search API modules are you using? Are you using facets, and does the error still occur after disabling them temporarily?
If the error still occurs after updating, please post all related SQL queries that are executed on the page request in question here.
Comment #4
gilsbert commentedHi Drunken.
I have the same issue reported here.
I'm working in a new project where the searchapi view's result needs a fixed sort by a date field.
In another words: I'm not using the relevance (when using relevance everything works fine).
How to reproduce the issue: when we use a term as a filter in the fulltext search we get that kind of message (field must be in group by). The field reported in the message is always the field choosen as the sort criteria.
List of SQL generated/executed by "SearchApiDbService::searchP" (the last one - after facets).
a) SQL when not using a full text search (it works)
SELECT DISTINCT t.item_id AS item_id, t.field_date AS tfield_date, '1' AS score FROM search_api_db_idx_noticias t ORDER BY t.field_date DESC LIMIT 20 OFFSET 0
b) SQL with error when using a full text search (and not the relevance as the sort criteria):
Devel's module is not showing this SQL (probrably because it is not being executed!) so I got it from postgresql log.
SELECT t.item_id AS item_id, t_2.field_date AS t_2field_date, SUM(t.score) AS score
FROM ( SELECT t.* FROM search_api_db_idx_noticias_title t
WHERE (word = 'brasil')
UNION ALL
SELECT t.* FROM search_api_db_idx_noticias_body_value t
WHERE (word = 'brasil') ) t
LEFT OUTER JOIN search_api_db_idx_noticias t_2 ON t.item_id = t_2.item_id
GROUP BY t.item_id
ORDER BY t_2.field_date DESC
LIMIT 20 OFFSET 0
c) Same as the previous SQL but now using the relevance as the sort criteria (it works)
SELECT t.item_id AS item_id, SUM(t.score) AS score
FROM ( SELECT t.* FROM search_api_db_idx_noticias_title t
WHERE (word = 'brasil')
UNION ALL
SELECT t.* FROM search_api_db_idx_noticias_body_value t
WHERE (word = 'brasil') ) t
GROUP BY t.item_id
ORDER BY score DESC LIMIT 20 OFFSET 0
As you might note in statement (b) there is a field: "t_2.field_date". When we have an agregation function just like the SUM used for the score all "free" fields must be in the group by clause (this is mandatory in postgresql and I don't know how this works in different databases).
So the correct statement for (b) would be (portion in bold shows the necessary correction):
SELECT t.item_id AS item_id, t_2.field_date AS t_2field_date, SUM(t.score) AS score
FROM ( SELECT t.* FROM search_api_db_idx_noticias_title t
WHERE (word = 'brasil')
UNION ALL
SELECT t.* FROM search_api_db_idx_noticias_body_value t
WHERE (word = 'brasil') ) t
LEFT OUTER JOIN search_api_db_idx_noticias t_2 ON t.item_id = t_2.item_id
GROUP BY t.item_id, t_2.field_date
ORDER BY t_2.field_date DESC
LIMIT 20 OFFSET 0
Another possible approach is to avoid the SUM when we are NOT using the relevance to sort the result!
Perhaps that would be better since the statement will be faster but I don't know about others impacts.
How can I help you further to produce a patch for this issue?
Regards,
Gilsberty
Comment #5
gilsbert commentedI forgot to report the version of the modules:
searchapi - 7.x-1.11
searchapi_db = 7.x-1.2
Comment #6
gilsbert commentedDrunken, if the original post's solution is good I might be able to write a patch.
If you think that it is possible to avoid the SUM in the sql statement when we are not using the "score" then I dont have an idea on how to write this patch.
Please, guide me.
Regards,
Gilsberty
Comment #7
drunken monkeyThanks for investigating and reporting this so thoroughly! Like this, it was easy to spot what's wrong: Postgres seems to need a field present in the fields list in order to be able to sort on it! Therefore, even though this module doesn't add the field itself, we would need to add the
GROUP BYfor Postgres.That's rather bad DX, altogether, and I wonder whether this isn't more of a bug in the Postgres integration of Drupal itself. However, MySQL seems fine enough with the
GROUP BYbeing added even if the field isn't present, so the attached patch does just that. Could you please test whether it fixes your problem?Also, did you ever try to run this module's test case under Postgres? I would be very much interested in the results. This issue here should result in a few fails, and if we can reduce them with this patch (so please test both with and without it) then all the better!
In theory, this is mandatory in SQL in general, because having an aggregation without all "normal" fields being grouped makes absolutely no sense. However, MySQL has the bad habit of just ignoring such errors and ploughing on regardless, resulting mostly in complete garbage being returned as the result.
However, while I had initially some such errors in this module, we already fixed them a while ago. This one here is just due to a field being only added with the Postgres backend, something I couldn't see or know.
(Actually, I always wondered why the SQL standard doesn't just say that all non-aggregated fields are just assumed to be grouped if an aggregation is present. Would make much more sense, in my opinion, and reduce both verbosity and sources of error. But well, wishing doesn't help anything, of course. ;))
Comment #8
gilsbert commentedHi Drunken.
Thank you for posting the patch.
I want to help you with everything you ask but I'm not sure if I understood what you are expecting from me.
When you asked me to test "this module's test case under Postgresql" were you talking about my reported issue or about prepared test cases inside the module?
I can test anything you wish but I don't know how to use test cases that might be inside the modules search_api_db / search_api. If that is the case do you mind to point me a link where I can read how to proceed with the tests?
If you were talking about my issue then no worries: I'm using postgresql so the results are validated on it!
Results:
1) without the patch
I get what is reported at #4 (same behavior for last stable and dev versions).
2) with the patch
I tested the patch only over the last dev because I believe it is not written for the last stable version.
It fixed the problem with the statement (b) - "SQL with error when using a full text search (and not the relevance as the sort criteria)" related at #4.
Unfortunately the patch created a new issue with the statement (a) - "SQL when not using a full text search" related at #4. This statement after the patch is receiving a group by clausule and ending like this:
SELECT DISTINCT t.item_id AS item_id, t.field_date AS tfield_date, '1' AS score
FROM search_api_db_idx_noticias t
GROUP BY t.field_date
ORDER BY t.field_date DESC
LIMIT 20 OFFSET 0
This is wrong and is generating another postgresql error and the explanation is: when there is a group by all fields must be listed on it or must be used by an agregation function!
The explanation already give us two ways of solving it: I - adding all fields to the group by or II - adding an agregation funcition! Personally I would not try to fix it because this new issue can be avoided at all!
How to avoid the new issue
The group by should be used only when there is a sql agregation function (min, max, sum, etc.) and should not be included otherwise.
In another words: the group by should be added only when there is a full text value in the statement because in that case there will be a sum for the relevance field.
Perhaps there are another cases where search_api's modules will use a sql agregation function but I dont know the module so deeper to help with this analyze.
Let me know if I can help any further and apologize me if I'm understanding something wrong.
Regards,
Gilsberty
Comment #9
drunken monkeyAh, of course, makes sense … I think the easiest way to do this is by just looking whether there is already something in a
GROUP BY– if yes, add the sort field as well, otherwise don't.Patch attached, please see whether that solves the problem!
Ah, sorry for phrasing this so poorly. I was talking about the automatic tests deployed along with this module. (These are also the things that get tested when posting patches here, that make the patch field go either green or red.)
To execute them, you need to enable the "Testing" module, which is packaged in Drupal Core. Then, go to
admin/config/development/testingon your site, check the checkbox next to "Search API Database Search" and then click "Run tests" at the bottom of the page. The tests will be executed and after a minute or so you will see a page with the test results.On MySQL, everything passes and is OK, but I assume that this won't be the case for PostgreSQL. It would be good to know how many tests fail and, if not too many, which. Please try it both with and without the patch, in any case, to see if it helps the matter.
Thanks a lot for your help in this! I hope we can make this module work on Postgres just as well as on MySQL at some point.
Comment #10
gilsbert commentedHi Drunken.
I had to deal with the php directive's open_basedir!
The testing module requires it to be off and for a security reason my server uses it.
Well today I found a good workaround and I did run the tests.
search_api_db - last dev (2014-Mar-11)
--> by the way I believe there is an error in the datetime value inside the "info" file because the module is updated but drupal says it is february's version!
First try (without the patch)
I got one exception listed in the image below (the text is in portuguese, let me know if you need help to understand it).
I'm not sure if the test stopped at this message but I feel like it did.
Second try (with the patch)
The patch FIXED the issue.
It works!
The test module result got a new exception listed in the image below.
Once again I believe the test stopped when the message was generated.
It appears to me that there is a sql statement using an union and trying to combine fields of different types (this is not possible in postgresql). Perhaps this will require a new patch...
Regards,
Gilsberty
Comment #11
gilsbert commentedComment #12
drunken monkeyThanks a lot for your help! Good to hear that this patch already solved some of the problems.
Agreed, though, we should continue solving this in a separate issue. I created one here: #2219525: Fix tests in PostgreSQL. It would be great if you could continue to help me there!
Regarding the original issue here, I committed the patch now. Thanks a lot for helping me with it!