OFBiz › OFBiz - Dev

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

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 5 messages Options
Nicolas Malin (Jira)
Reply | Threaded
Open this post in threaded view
♦
♦
|

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

Nicolas Malin (Jira)
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.

Nicolas Malin (Jira)
Reply | Threaded
Open this post in threaded view
♦
♦
|

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

Nicolas Malin (Jira)

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

Nicolas Malin (Jira)
Reply | Threaded
Open this post in threaded view
♦
♦
|

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

Nicolas Malin (Jira)
In reply to this post by Nicolas Malin (Jira)

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

Nicolas Malin (Jira)
Reply | Threaded
Open this post in threaded view
♦
♦
|

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

Nicolas Malin (Jira)
In reply to this post by Nicolas Malin (Jira)

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

Nicolas Malin (Jira)
Reply | Threaded
Open this post in threaded view
♦
♦
|

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

Nicolas Malin (Jira)
In reply to this post by Nicolas Malin (Jira)

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

Free forum by Nabble Edit this page