[
https://issues.apache.org/jira/browse/OFBIZ-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sharan Foga updated OFBIZ-5907:
-------------------------------
Sprint: Bug Crush Event - 21/2/2015
> 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)
