Wai created OFBIZ-5907:
--------------------------
Summary: 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)
