You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Sharan Foga (JIRA)" <ji...@apache.org> on 2015/02/21 08:41:06 UTC
[jira] [Updated] (OFBIZ-5907) Postgresql jdbc driver is causing
exception. Mysql driver is working ok.
[ 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)