[jira] Closed: (OFBIZ-1571) Product keyword search SQL error (patch)

["Si Chen (JIRA)" <ji...@apache.org>]

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