You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Wickersheimer Jeremy (JIRA)" <ji...@apache.org> on 2008/01/10 16:16:34 UTC
[jira] Created: (OFBIZ-1571) Product keyword search SQL error
(patch)
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.
[jira] Updated: (OFBIZ-1571) Product keyword search SQL error
(patch)
Posted by "Wickersheimer Jeremy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-1571?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wickersheimer Jeremy updated OFBIZ-1571:
----------------------------------------
Attachment: 1571.patch
> 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
>
> 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.
[jira] Closed: (OFBIZ-1571) Product keyword search SQL error
(patch)
Posted by "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.
[jira] Assigned: (OFBIZ-1571) Product keyword search SQL error
(patch)
Posted by "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 reassigned OFBIZ-1571:
------------------------------
Assignee: Si Chen
> 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.
[jira] Commented: (OFBIZ-1571) Product keyword search SQL error
(patch)
Posted by "Si Chen (JIRA)" <ji...@apache.org>.
[ 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.