[
https://issues.apache.org/jira/browse/OFBIZ-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15065349#comment-15065349
]
Rahul Bhooteshwar commented on OFBIZ-5907:
------------------------------------------
This is neither PostgreSQL JDBC Driver nor OfBiz Entity Engine which is causing
the error mentioned.
Query Generated:
"SELECT SG.GROUP_ID, COUNT(DISTINCT ULSG.USER_LOGIN_ID) FROM (SECURITY_GROUP SG
LEFT OUTER JOIN USER_LOGIN_SECURITY_GROUP ULSG ON SG.GROUP_ID = ULSG.GROUP_ID)
LEFT OUTER JOIN USER_LOGIN UL ON ULSG.USER_LOGIN_ID = UL.USER_LOGIN_ID WHERE
(((ULSG.THRU_DATE IS NULL))) GROUP BY SG.GROUP_ID ORDER BY SG.GROUP_ID ASC,
COUNT(DISTINCT ULSG.USER_LOGIN_ID) ASC, ULSG.USER_LOGIN_ID ASC;"
It can be seen by running the above query resulted for your view-entity
directly on different databases i.e. MySQL, PostgresSQL & Derby. Only MySQL
will treat such queries as "correct". Postgres & Derby expects any column name
mentioned in SELECT fields to be included in GroupBy or to be used with select
"directly" inside the aggregate functions.
So, in your case, in order to display "cntUser" you should use group-by="true"
with "ULSG" i.e. <alias entity-alias="ULSG" name="userLoginId"
field="userLoginId" group-by="true">
> Postgresql jdbc driver is causing exception. Mysql driver is working ok.
> -------------------------------------------------------------------------
>
> Key: OFBIZ-5907
> URL: https://issues.apache.org/jira/browse/OFBIZ-5907
> Project: OFBiz
> Issue Type: Bug
> Components: framework
> Affects Versions: Trunk
> Environment: OS:
> Ubuntu 14.04
> Java:
> java version "1.7.0_71"
> Java(TM) SE Runtime Environment (build 1.7.0_71-b14)
> Java HotSpot(TM) 64-Bit Server VM (build 24.71-b01, mixed mode)
> Reporter: Wai
>
> I've discovered an issue and I do not know if it lies in the entity engine or
> the jdbc driver.
> I have the following entity view.
> <view-entity entity-name="TestView" package-name="test.entityviews"
> title="View entity">
> <member-entity entity-alias="SG" entity-name="SecurityGroup" />
> <member-entity entity-alias="ULSG"
> entity-name="UserLoginSecurityGroup" />
> <member-entity entity-alias="UL" entity-name="UserLogin" />
> <alias entity-alias="SG" name="groupId" field="groupId"
> group-by="true" />
> <alias entity-alias="ULSG" name="userLoginId" field="userLoginId" />
> <alias entity-alias="ULSG" name="cntUser" field="userLoginId"
> function="count-distinct" />
> <view-link entity-alias="SG" rel-entity-alias="ULSG"
> rel-optional="true">
> <key-map field-name="groupId" rel-field-name="groupId" />
> </view-link>
> <view-link entity-alias="ULSG" rel-entity-alias="UL"
> rel-optional="true">
> <key-map field-name="userLoginId" rel-field-name="userLoginId" />
> </view-link>
> <entity-condition>
> <condition-expr entity-alias="ULSG" field-name="thruDate"
> operator="equals" />
> </entity-condition>
> </view-entity>
> When ofbiz is run using the latest mysql jdbc driver (v5.1.34), the proper
> sql statement is generated and all runs well. But when the Postgresql jdbc
> driver is used, it causes an exception. I have tried with the following
> latest Postgresql drivers and they have all failed.
> JDBC3 Postgresql Driver, Version 9.3-1102
> JDBC4 Postgresql Driver, Version 9.3-1102
> JDBC41 Postgresql Driver, Version 9.3-1102
> Mysql jdbc driver would generate the following sql statement:
> SELECT SG.GROUP_ID, ULSG.USER_LOGIN_ID, COUNT(DISTINCT ULSG.USER_LOGIN_ID)
> FROM SECURITY_GROUP SG LEFT OUTER JOIN USER_LOGIN_SECURITY_GROUP ULSG ON
> SG.GROUP_ID = ULSG.GROUP_ID LEFT OUTER JOIN USER_LOGIN UL ON
> ULSG.USER_LOGIN_ID = UL.USER_LOGIN_ID WHERE ((ULSG.THRU_DATE IS NULL)) GROUP
> BY SG.GROUP_ID
> Postgresql jdbc driver would give the following exception (Notice the
> resulting sql statement is corrupted with 'public.' ???):
> Failure in operation, rolling back transaction
> org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing
> the following:SELECT SG.GROUP_ID, ULSG.USER_LOGIN_ID, COUNT(DISTINCT
> ULSG.USER_LOGIN_ID) FROM (public.SECURITY_GROUP SG LEFT OUTER JOIN
> public.USER_LOGIN_SECURITY_GROUP ULSG ON SG.GROUP_ID = ULSG.GROUP_ID) LEFT
> OUTER JOIN public.USER_LOGIN UL ON ULSG.USER_LOGIN_ID = UL.USER_LOGIN_ID
> WHERE ((ULSG.THRU_DATE IS NULL)) GROUP BY SG.GROUP_ID (ERROR: column
> "ulsg.user_login_id" must appear in the GROUP BY clause or be used in an
> aggregate function
> Position: 21)
> at
> org.ofbiz.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:409)
> ~[ofbiz-entity.jar:?]
> at
> org.ofbiz.entity.datasource.GenericDAO.selectListIteratorByCondition(GenericDAO.java:785)
> ~[ofbiz-entity.jar:?]
> at
> org.ofbiz.entity.datasource.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:140)
> ~[ofbiz-entity.jar:?]
> at org.ofbiz.entity.GenericDelegator.find(GenericDelegator.java:1774)
> ~[ofbiz-entity.jar:?]
> at
> org.ofbiz.entity.util.EntityQuery.queryIterator(EntityQuery.java:392)
> ~[ofbiz-entity.jar:?]
> at org.ofbiz.entity.util.EntityQuery$queryIterator$1.call(Unknown
> Source) ~[?:?]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
