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
Fix For: SVN trunk
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.
