[jira] [Created] (OFBIZ-5907) Postgresql jdbc driver is causing exception. Mysql driver is working ok.

["Wai (JIRA)" <ji...@apache.org>]

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)