You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Si Chen (JIRA)" <ji...@apache.org> on 2008/01/12 02:50:34 UTC
[jira] Commented: (OFBIZ-1571) Product keyword search SQL error
(patch)
[ https://issues.apache.org/jira/browse/OFBIZ-1571?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12558184#action_12558184 ]
Si Chen commented on OFBIZ-1571:
--------------------------------
If there are no objections I will be committing this.
> 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.