[
https://issues.apache.org/jira/browse/OFBIZ-3789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12875154#action_12875154
]
Adam Heath commented on OFBIZ-3789:
-----------------------------------
This bug is not due to GROUP BY issues. There is no GROUP BY in the given
query. There is a bug in derby in that it has a wrong error message.
This is due to ORDER BY *always* using the aliased column name for a view;
instead, it should only do so when DatasourceInfo.aliasViews allows it to.
Looking at EntityConditionBase, getColName(), it checks whether the entity is a
view, and if aliasViews is true, then it uses the colAlias. But since
aliasViews is turned off for derby, getColName() ends up calling
ModelField.getColName(), and that is the aliased field for views.
> No results when searching with keywords in catalog
> --------------------------------------------------
>
> Key: OFBIZ-3789
> URL: https://issues.apache.org/jira/browse/OFBIZ-3789
> Project: OFBiz
> Issue Type: Bug
> Components: product
> Affects Versions: SVN trunk
> Reporter: Erwan de FERRIERES
> Assignee: Adam Heath
> Fix For: SVN trunk
>
>
> Steps to reproduce :
> go to : https://localhost:8443/catalog/control/keywordsearch
> then in the Keywords textbox, type GZ, then hit enter key.
> No results are shown.
> Log error in the console :
> 2010-05-28 16:03:07,780 (http-0.0.0.0-8443-1) [
> ProductSearch.java:271:INFO ] Finished initial setup of keywords,
> doingBothAndOr=false, andKeywordFixedSet={%gz%}
> keywordFixedOrSetAndList={}
> 2010-05-28 16:03:07,849 (http-0.0.0.0-8443-1) [
> ProductSearch.java:654:ERROR]
> ---- exception report
> ----------------------------------------------------------
> Error in product search
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:SELECT DISTINCT
> PK1.RELEVANCY_WEIGHT, PROD.PRODUCT_ID FROM (OFBIZ.PRODUCT PROD LEFT OUTER
> JOIN OFBIZ.PRODUCT_CALCULATED_INFO PRODCI ON PROD.PRODUCT_ID =
> PRODCI.PRODUCT_ID) INNER JOIN OFBIZ.PRODUCT_KEYWORD PK1 ON PROD.PRODUCT_ID =
> PK1.PRODUCT_ID WHERE (PK1.KEYWORD LIKE ?) ORDER BY TOTAL_RELEVANCY DESC
> (Column 'TOTAL_RELEVANCY' is either not in any table in the FROM list or
> appears within a join specification and is outside the scope of the join
> specification or appears in a HAVING clause and is not in the GROUP BY list.
> If this is a CREATE or ALTER TABLE statement then 'TOTAL_RELEVANCY' is not a
> column in the target table.)
> ---- cause
> ---------------------------------------------------------------------
> Exception: java.sql.SQLSyntaxErrorException
> Message: Column 'TOTAL_RELEVANCY' is either not in any table in the FROM list
> or appears within a join specification and is outside the scope of the join
> specification or appears in a HAVING clause and is not in the GROUP BY list.
> If this is a CREATE or ALTER TABLE statement then 'TOTAL_RELEVANCY' is not a
> column in the target table.
> ---- cause
> ---------------------------------------------------------------------
> Exception: org.apache.derby.impl.jdbc.EmbedSQLException
> Message: Column 'TOTAL_RELEVANCY' is either not in any table in the FROM list
> or appears within a join specification and is outside the scope of the join
> specification or appears in a HAVING clause and is not in the GROUP BY list.
> If this is a CREATE or ALTER TABLE statement then 'TOTAL_RELEVANCY' is not a
> column in the target table.
> ---- cause
> ---------------------------------------------------------------------
> Exception: org.apache.derby.iapi.error.StandardException
> Message: Column 'TOTAL_RELEVANCY' is either not in any table in the FROM list
> or appears within a join specification and is outside the scope of the join
> specification or appears in a HAVING clause and is not in the GROUP BY list.
> If this is a CREATE or ALTER TABLE statement then 'TOTAL_RELEVANCY' is not a
> column in the target table.
> ---- stack trace
> ---------------------------------------------------------------
> ERROR 42X04: Column 'TOTAL_RELEVANCY' is either not in any table in the FROM
> list or appears within a join specification and is outside the scope of the
> join specification or appears in a HAVING clause and is not in the GROUP BY
> list. If this is a CREATE or ALTER TABLE statement then 'TOTAL_RELEVANCY' is
> not a column in the target table.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
