[ https://issues.apache.org/jira/browse/OFBIZ-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Scott Gray closed OFBIZ-5907. ----------------------------- > 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)