[ https://issues.apache.org/jira/browse/OFBIZ-1571?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Si Chen closed OFBIZ-1571. -------------------------- Resolution: Fixed thanks. > Product keyword search SQL error (patch) > ---------------------------------------- > > Key: OFBIZ-1571 > URL: https://issues.apache.org/jira/browse/OFBIZ-1571 > Project: OFBiz > Issue Type: Bug > Components: product > Affects Versions: SVN trunk > Environment: postgres SQL 8.2 > Reporter: Wickersheimer Jeremy > Assignee: Si Chen > Fix For: SVN trunk > > Attachments: 1571.patch > > > It seems the way the SQL query is built is incorrect when there are both And > and Or sets for keywords. > For this you need a Thesaurus rule that expand one keyword into 2 (ex "foo" > => "bar1 bar2") > Then if you search for "foo foobar", the query will be like this: > SELECT DISTINCT > (PK2.RELEVANCY_WEIGHT + SUM(PK3.RELEVANCY_WEIGHT)), > PROD.PRODUCT_ID > FROM ((((public.PRODUCT PROD > LEFT OUTER JOIN public.PRODUCT_CALCULATED_INFO PRODCI ON > PROD.PRODUCT_ID = PRODCI.PRODUCT_ID) > INNER JOIN public.PRODUCT_CATEGORY_MEMBER PCM1 ON > PROD.PRODUCT_ID = PCM1.PRODUCT_ID) > INNER JOIN public.PRODUCT_PRICE PSPP2 ON > PROD.PRODUCT_ID = PSPP2.PRODUCT_ID) > INNER JOIN public.PRODUCT_KEYWORD PK2 ON > PROD.PRODUCT_ID = PK2.PRODUCT_ID) > INNER JOIN public.PRODUCT_KEYWORD PK3 ON > PROD.PRODUCT_ID = PK3.PRODUCT_ID > WHERE (PCM1.PRODUCT_CATEGORY_ID IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) > AND (PCM1.THRU_DATE IS NULL OR PCM1.THRU_DATE > ?) > AND PCM1.FROM_DATE < ? > AND PSPP2.PRODUCT_PRICE_TYPE_ID = ? > AND PSPP2.PRODUCT_PRICE_PURPOSE_ID = ? > AND PSPP2.CURRENCY_UOM_ID = ? > AND PSPP2.PRODUCT_STORE_GROUP_ID = ? > AND (PSPP2.THRU_DATE IS NULL OR PSPP2.THRU_DATE > ?) > AND PSPP2.FROM_DATE < ? > AND PROD.IS_VARIANT <> ? > AND (PROD.INTRODUCTION_DATE IS NULL OR PROD.INTRODUCTION_DATE <= ?) > AND (PROD.SALES_DISCONTINUATION_DATE IS NULL OR > PROD.SALES_DISCONTINUATION_DATE > ?) > AND PK2.KEYWORD LIKE ? > AND (PK3.KEYWORD LIKE ? OR PK3.KEYWORD LIKE ?)) > GROUP BY PROD.PRODUCT_ID > ORDER BY (PK2.RELEVANCY_WEIGHT + SUM(PK3.RELEVANCY_WEIGHT)) DESC > And the error is: > (ERROR: column "pk2.relevancy_weight" must appear in the GROUP BY clause or > be used in an aggregate function) -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.