Hi. This is my first time here so I'm sorry if something is set wrong in this form :)
I got this beautiful error in log:
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "t.id" must appear in the GROUP BY clause or be used in an aggregate function: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM (SELECT t.*, SUM(t.completeness) AS percent FROM {fuzzysearch_wyszukane_przepisy_title} t WHERE (( (ngram = :db_condition_placeholder_0) AND (completeness >= :db_condition_placeholder_1) AND (completeness <= :db_condition_placeholder_2) )OR( (ngram = :db_condition_placeholder_3) AND (completeness >= :db_condition_placeholder_4) AND (completeness <= :db_condition_placeholder_5) )OR( (ngram = :db_condition_placeholder_6) AND (completeness >= :db_condition_placeholder_7) AND (completeness <= :db_condition_placeholder_8) )OR( (ngram = :db_condition_placeholder_9) AND (completeness >= :db_condition_placeholder_10) AND (completeness <= :db_condition_placeholder_11) )OR( (ngram = :db_condition_placeholder_12) AND (completeness >= :db_condition_placeholder_13) AND (completeness <= :db_condition_placeholder_14) )OR( (ngram = :db_condition_placeholder_15) AND (completeness >= :db_condition_placeholder_16) AND (completeness <= :db_condition_placeholder_17) )OR( (ngram = :db_condition_placeholder_18) AND (completeness >= :db_condition_placeholder_19) AND (completeness <= :db_condition_placeholder_20) )OR( (ngram = :db_condition_placeholder_21) AND (completeness >= :db_condition_placeholder_22) AND (completeness <= :db_condition_placeholder_23) )OR( (ngram = :db_condition_placeholder_24) AND (completeness >= :db_condition_placeholder_25) AND (completeness <= :db_condition_placeholder_26) )OR( (ngram = :db_condition_placeholder_27) AND (completeness >= :db_condition_placeholder_28) AND (completeness <= :db_condition_placeholder_29) )OR( (ngram = :db_condition_placeholder_30) AND (completeness >= :db_condition_placeholder_31) AND (completeness <= :db_condition_placeholder_32) )OR( (ngram = :db_condition_placeholder_33) AND (completeness >= :db_condition_placeholder_34) AND (completeness <= :db_condition_placeholder_35) )OR( (ngram = :db_condition_placeholder_36) AND (completeness >= :db_condition_placeholder_37) AND (completeness <= :db_condition_placeholder_38) )OR( (ngram = :db_condition_placeholder_39) AND (completeness >= :db_condition_placeholder_40) AND (completeness <= :db_condition_placeholder_41) )OR( (ngram = :db_condition_placeholder_42) AND (completeness >= :db_condition_placeholder_43) AND (completeness <= :db_condition_placeholder_44) )OR( (ngram = :db_condition_placeholder_45) AND (completeness >= :db_condition_placeholder_46) AND (completeness <= :db_condition_placeholder_47) )) GROUP BY t.word_id, t.item_id HAVING (SUM(t.completeness) >= :value1) UNION ALL SELECT t.*, SUM(t.completeness) AS percent FROM {fuzzysearch_wyszukane_przepisy_field_opis} t WHERE (( (ngram = :db_condition_placeholder_0) AND (completeness >= :db_condition_placeholder_1) AND (completeness <= :db_condition_placeholder_2) )OR( (ngram = :db_condition_placeholder_3) AND (completeness >= :db_condition_placeholder_4) AND (completeness <= :db_condition_placeholder_5) )OR( (ngram = :db_condition_placeholder_6) AND (completeness >= :db_condition_placeholder_7) AND (completeness <= :db_condition_placeholder_8) )OR( (ngram = :db_condition_placeholder_9) AND (completeness >= :db_condition_placeholder_10) AND (completeness <= :db_condition_placeholder_11) )OR( (ngram = :db_condition_placeholder_12) AND (completeness >= :db_condition_placeholder_13) AND (completeness <= :db_condition_placeholder_14) )OR( (ngram = :db_condition_placeholder_15) AND (completeness >= :db_condition_placeholder_16) AND (completeness <= :db_condition_placeholder_17) )OR( (ngram = :db_condition_placeholder_18) AND (completeness >= :db_condition_placeholder_19) AND (completeness <= :db_condition_placeholder_20) )OR( (ngram = :db_condition_placeholder_21) AND (completeness >= :db_condition_placeholder_22) AND (completeness <= :db_condition_placeholder_23) )OR( (ngram = :db_condition_placeholder_24) AND (completeness >= :db_condition_placeholder_25) AND (completeness <= :db_condition_placeholder_26) )OR( (ngram = :db_condition_placeholder_27) AND (completeness >= :db_condition_placeholder_28) AND (completeness <= :db_condition_placeholder_29) )OR( (ngram = :db_condition_placeholder_30) AND (completeness >= :db_condition_placeholder_31) AND (completeness <= :db_condition_placeholder_32) )OR( (ngram = :db_condition_placeholder_33) AND (completeness >= :db_condition_placeholder_34) AND (completeness <= :db_condition_placeholder_35) )OR( (ngram = :db_condition_placeholder_36) AND (completeness >= :db_condition_placeholder_37) AND (completeness <= :db_condition_placeholder_38) )OR( (ngram = :db_condition_placeholder_39) AND (completeness >= :db_condition_placeholder_40) AND (completeness <= :db_condition_placeholder_41) )OR( (ngram = :db_condition_placeholder_42) AND (completeness >= :db_condition_placeholder_43) AND (completeness <= :db_condition_placeholder_44) )OR( (ngram = :db_condition_placeholder_45) AND (completeness >= :db_condition_placeholder_46) AND (completeness <= :db_condition_placeholder_47) )) GROUP BY t.word_id, t.item_id HAVING (SUM(t.completeness) >= :value1) UNION ALL SELECT t.*, SUM(t.completeness) AS percent FROM {fuzzysearch_wyszukane_przepisy_field_przygotowanie_value} t WHERE (( (ngram = :db_condition_placeholder_0) AND (completeness >= :db_condition_placeholder_1) AND (completeness <= :db_condition_placeholder_2) )OR( (ngram = :db_condition_placeholder_3) AND (completeness >= :db_condition_placeholder_4) AND (completeness <= :db_condition_placeholder_5) )OR( (ngram = :db_condition_placeholder_6) AND (completeness >= :db_condition_placeholder_7) AND (completeness <= :db_condition_placeholder_8) )OR( (ngram = :db_condition_placeholder_9) AND (completeness >= :db_condition_placeholder_10) AND (completeness <= :db_condition_placeholder_11) )OR( (ngram = :db_condition_placeholder_12) AND (completeness >= :db_condition_placeholder_13) AND (completeness <= :db_condition_placeholder_14) )OR( (ngram = :db_condition_placeholder_15) AND (completeness >= :db_condition_placeholder_16) AND (completeness <= :db_condition_placeholder_17) )OR( (ngram = :db_condition_placeholder_18) AND (completeness >= :db_condition_placeholder_19) AND (completeness <= :db_condition_placeholder_20) )OR( (ngram = :db_condition_placeholder_21) AND (completeness >= :db_condition_placeholder_22) AND (completeness <= :db_condition_placeholder_23) )OR( (ngram = :db_condition_placeholder_24) AND (completeness >= :db_condition_placeholder_25) AND (completeness <= :db_condition_placeholder_26) )OR( (ngram = :db_condition_placeholder_27) AND (completeness >= :db_condition_placeholder_28) AND (completeness <= :db_condition_placeholder_29) )OR( (ngram = :db_condition_placeholder_30) AND (completeness >= :db_condition_placeholder_31) AND (completeness <= :db_condition_placeholder_32) )OR( (ngram = :db_condition_placeholder_33) AND (completeness >= :db_condition_placeholder_34) AND (completeness <= :db_condition_placeholder_35) )OR( (ngram = :db_condition_placeholder_36) AND (completeness >= :db_condition_placeholder_37) AND (completeness <= :db_condition_placeholder_38) )OR( (ngram = :db_condition_placeholder_39) AND (completeness >= :db_condition_placeholder_40) AND (completeness <= :db_condition_placeholder_41) )OR( (ngram = :db_condition_placeholder_42) AND (completeness >= :db_condition_placeholder_43) AND (completeness <= :db_condition_placeholder_44) )OR( (ngram = :db_condition_placeholder_45) AND (completeness >= :db_condition_placeholder_46) AND (completeness <= :db_condition_placeholder_47) )) GROUP BY t.word_id, t.item_id HAVING (SUM(t.completeness) >= :value1) UNION ALL SELECT t.*, SUM(t.completeness) AS percent FROM {fuzzysearch_wyszukane_przepisy_field_skladniki_value} t WHERE (( (ngram = :db_condition_placeholder_0) AND (completeness >= :db_condition_placeholder_1) AND (completeness <= :db_condition_placeholder_2) )OR( (ngram = :db_condition_placeholder_3) AND (completeness >= :db_condition_placeholder_4) AND (completeness <= :db_condition_placeholder_5) )OR( (ngram = :db_condition_placeholder_6) AND (completeness >= :db_condition_placeholder_7) AND (completeness <= :db_condition_placeholder_8) )OR( (ngram = :db_condition_placeholder_9) AND (completeness >= :db_condition_placeholder_10) AND (completeness <= :db_condition_placeholder_11) )OR( (ngram = :db_condition_placeholder_12) AND (completeness >= :db_condition_placeholder_13) AND (completeness <= :db_condition_placeholder_14) )OR( (ngram = :db_condition_placeholder_15) AND (completeness >= :db_condition_placeholder_16) AND (completeness <= :db_condition_placeholder_17) )OR( (ngram = :db_condition_placeholder_18) AND (completeness >= :db_condition_placeholder_19) AND (completeness <= :db_condition_placeholder_20) )OR( (ngram = :db_condition_placeholder_21) AND (completeness >= :db_condition_placeholder_22) AND (completeness <= :db_condition_placeholder_23) )OR( (ngram = :db_condition_placeholder_24) AND (completeness >= :db_condition_placeholder_25) AND (completeness <= :db_condition_placeholder_26) )OR( (ngram = :db_condition_placeholder_27) AND (completeness >= :db_condition_placeholder_28) AND (completeness <= :db_condition_placeholder_29) )OR( (ngram = :db_condition_placeholder_30) AND (completeness >= :db_condition_placeholder_31) AND (completeness <= :db_condition_placeholder_32) )OR( (ngram = :db_condition_placeholder_33) AND (completeness >= :db_condition_placeholder_34) AND (completeness <= :db_condition_placeholder_35) )OR( (ngram = :db_condition_placeholder_36) AND (completeness >= :db_condition_placeholder_37) AND (completeness <= :db_condition_placeholder_38) )OR( (ngram = :db_condition_placeholder_39) AND (completeness >= :db_condition_placeholder_40) AND (completeness <= :db_condition_placeholder_41) )OR( (ngram = :db_condition_placeholder_42) AND (completeness >= :db_condition_placeholder_43) AND (completeness <= :db_condition_placeholder_44) )OR( (ngram = :db_condition_placeholder_45) AND (completeness >= :db_condition_placeholder_46) AND (completeness <= :db_condition_placeholder_47) )) GROUP BY t.word_id, t.item_id HAVING (SUM(t.completeness) >= :value1) ) t GROUP BY t.item_id) subquery; Array ( [:db_condition_placeholder_0] => kre [:db_condition_placeholder_1] => 14.286 [:db_condition_placeholder_2] => 100.001 [:db_condition_placeholder_3] => rew [:db_condition_placeholder_4] => 14.286 [:db_condition_placeholder_5] => 100.001 [:db_condition_placeholder_6] => ewe [:db_condition_placeholder_7] => 14.286 [:db_condition_placeholder_8] => 100.001 [:db_condition_placeholder_9] => wet [:db_condition_placeholder_10] => 14.286 [:db_condition_placeholder_11] => 100.001 [:db_condition_placeholder_12] => kre [:db_condition_placeholder_13] => 14.286 [:db_condition_placeholder_14] => 100.001 [:db_condition_placeholder_15] => rew [:db_condition_placeholder_16] => 14.286 [:db_condition_placeholder_17] => 100.001 [:db_condition_placeholder_18] => ewe [:db_condition_placeholder_19] => 14.286 [:db_condition_placeholder_20] => 100.001 [:db_condition_placeholder_21] => wet [:db_condition_placeholder_22] => 14.286 [:db_condition_placeholder_23] => 100.001 [:db_condition_placeholder_24] => kre [:db_condition_placeholder_25] => 14.286 [:db_condition_placeholder_26] => 100.001 [:db_condition_placeholder_27] => rew [:db_condition_placeholder_28] => 14.286 [:db_condition_placeholder_29] => 100.001 [:db_condition_placeholder_30] => ewe [:db_condition_placeholder_31] => 14.286 [:db_condition_placeholder_32] => 100.001 [:db_condition_placeholder_33] => wet [:db_condition_placeholder_34] => 14.286 [:db_condition_placeholder_35] => 100.001 [:db_condition_placeholder_36] => kre [:db_condition_placeholder_37] => 14.286 [:db_condition_placeholder_38] => 100.001 [:db_condition_placeholder_39] => rew [:db_condition_placeholder_40] => 14.286 [:db_condition_placeholder_41] => 100.001 [:db_condition_placeholder_42] => ewe [:db_condition_placeholder_43] => 14.286 [:db_condition_placeholder_44] => 100.001 [:db_condition_placeholder_45] => wet [:db_condition_placeholder_46] => 14.286 [:db_condition_placeholder_47] => 100.001 [:value1] => 40 ) w FuzzySearchService->search() (linia 594 z /home/..../public_html/..../sites/all/modules/fuzzysearch/includes/service.inc).
Installation was made exactly with giude you gived here, all modules are up2date. Database is postgresql in mabe not so fresh version but it works.

Comments

awolfey’s picture

awolfey commented

Very nice! Is this postgresql?

awolfey’s picture

awolfey commented

Oh, I just saw that buried at the bottom. This was a problem we had in D6 also and I was hoping it would go away in D7. Can you tell me your postgresql version?

If you really want to use this module in D7 you may want to switch to mysql if you can. I can't say for sure when I'll have a chance to debug in postgresql. The D6 version does work with postgres.

ziomber’s picture

ziomber commented

This is version 8.3.5 but i think this is not releted to version of pgsql.
I haven't read php files yet but checked structure of fuzzysearch_ tables. In my opinion the problem is in different structure of tables. You use in query t.* but in GROUP BY you use only t.word_id and t.item_id. Postgresql needs all columns to be stated in GROUP BY or to be used in aggregate function.
Is fuzzysearch effective in larger services? My is not started yet and already has few thousands of rows in fuzzysearch_ tables...

awolfey’s picture

awolfey commented

Yes, that is the problem with postgres. I will try to find a solution for you, but keep in mind that the D7 version is development only at this point, and that most Drupal sites use mysql.

Fuzzy search will create large indexes. If you're using an ngram length of 3, then each word will create around 5 db rows (typical English words), compared to 1 for core search. Of course other languages have much longer average word length.

There will be a stopwords option soon, like we have in D6, to keep small, common words out of the db. But it's not ready yet for D7.

Depending on the scale and traffic of your site, fuzzysearch may not be right for you. Solr is a popular search engine for larger sites.

awolfey’s picture

awolfey commented
Title: Groupping error » Postgres groupping error
Status: Active » Postponed

Marking as postponed because postgres is a pretty low priority for me. That said, I welcome any patches or suggestions for a fix.