Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-dev] Fix for bug# 295556 - Do not add Distinct to Queries that including a Lob field - in DB2
OK, I understood. I will come back when there is working test case for Oracle for #295556 fix.
Thank you for all your help with solving this issue.

Tomas

Dne 9/9/14, 5:39 PM, J Grassel napsal(a):
I have not observed the problem as documented by the original reporter.  The last scenario Andrei suggested fails (using Oracle) on both 2.5.0 and 2.5.1 — where 2.5.1 is supposed to be fixed, so clearly the unit test does not reproduce the original problem yet.  It makes absolutely no sense to even try run with DB2 if I can’t reproduce the original problem using Oracle.

Once I have a test that:
1) Reproduces the problem (unit test fails)  using ECL 2.5.0 using Oracle AND
2) (unit test) Passes when using ECL 2.5.1 (which the fix was integrated into) using Oracle

Then I can give it a run against DB2.  Until those criteria are satisfied I am blocked.


On Sep 9, 2014, at 10:32 AM, Tomas Kraus <tomas.kraus@xxxxxxxxxx> wrote:

Hi Rick,

both me and Andrei do not have access to DB2 and we are quite busy at this moment. But looks like J Grassel already saw this problem on Oracle DB (as Andrei told me).
Think he can try the same with DB2 to see if it works fine or not. If he still won't be able to reproduce it, I'll push this back on our side and I'll ask to come back with working test-case.

Tomas

Dne 9/9/14, 5:01 PM, Rick Curtis napsal(a):
Andrei, Tomas -

By any chance can you guys come up with a proper EclipseLink test(or even standalone) that reproduces the failure? Lots of time has been spent on our end trying to reproduce this problem to no avail. Also, shouldn't we be adding a test with the bug fix in 295556?

Thanks,
RIck

On Mon, Sep 8, 2014 at 1:45 PM, andrei ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:
2.5.1 adds to org.eclipse.persistence.internal.expressions.SQLSelectStatement#normalize the following code:

        boolean isDistinctComputed = isDistinctComputed();

        // here goes normalization code, which is the same in both 2.5.0 and 2.5.1

        // distinct state has been set by normalization, see may be that should be reversed
        if (shouldDistinctBeUsed() && !isDistinctComputed && !session.getPlatform().isLobCompatibleWithDistinct()) {
            for (Object field : getFields()) {
                if (field instanceof DatabaseField) {
                    if (Helper.isLob((DatabaseField)field)) {
                        dontUseDistinct();
                        break;
                    }
                } else if (field instanceof _expression_) {                       
                    if (Helper.hasLob(((_expression_)field).getSelectionFields(this.query))) {
                        dontUseDistinct();
                        break;
                    }
                }
            }
        }

In case the original query doesn't explicitly specify neither use nor dontUse distinct, normalization code may choose to use distinct.
The new 2.5.1 code caches whether the distinct flag has been already set before normalization,
and in that case looks whether the useDistinct flag set by normalization should be reversed (to dontUseDistinct) -  and btw it would catch lob in both source or target.

My guess is that not catching lob in target is probably happens because it's a map (not a collection) and something is different in map case (getSelectionField(query) doesn't return all the fields of the target descriptor)
If that's true then both lob in the source should work as well as lob in target - provided the mapping is changed from map to collection - and there is a bug in map case.


On 9/8/2014 2:09 PM, J Grassel wrote:
Hmm, well, that works (produces Error Code 932 on Oracle) against both 2.5.0 and 2.5.1 (which should have the #295556 fix.)  Was the fix intended for this use case?

    @Test
    public void test003() {
        EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("testpu");       
//        populateDB(emf); // To save time, comment out after the db has been prepped
        EntityManager em = emf.createEntityManager();
        
        Session session = ((org.eclipse.persistence.internal.jpa.EntityManagerImpl) em).getSession();
       
        ReadAllQuery raQuery = new ReadAllQuery(ECL295EntB.class);
        raQuery.addJoinedAttribute(raQuery.getExpressionBuilder().anyOf("entACollection"));

        assertEquals(0, raQuery.getDistinctState());  // Make sure this is 0 so ECL needs to determine if DISTINCT should be used

        List resultSet = (List) session.executeQuery(raQuery);                       
        assertNotNull(resultSet);
        assertNotEquals(0, resultSet.size());
        
        ECL295EntB entB_find = (ECL295EntB) resultSet.get(0);
        assertNotNull(entB_find);
       
        // The next statement should demonstrate bug #295556
        boolean containsTarget = entB_find.getEntACollection().containsKey("EntA-1");
        assertTrue(containsTarget);
        
        assertEquals(10, entB_find.getEntACollection().size());      
    }

Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

Error Code: 932
Call: SELECT DISTINCT t1.ID, t1.STRDATA, t0.ID, t0.STRDATA, t0.STRINGVALUE FROM ECL295ENTA t0, ECLENTA_ENTB t2, ECL295ENTB t1 WHERE ((t2.ECL295EntB_ID = t1.ID) AND (t0.ID = t2.entACollection_ID))
Query: ReadAllQuery(referenceClass=ECL295EntB sql="SELECT DISTINCT t1.ID, t1.STRDATA, t0.ID, t0.STRDATA, t0.STRINGVALUE FROM ECL295ENTA t0, ECLENTA_ENTB t2, ECL295ENTB t1 WHERE ((t2.ECL295EntB_ID = t1.ID) AND (t0.ID = t2.entACollection_ID))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1995)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2714)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2667)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:477)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1155)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1114)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:402)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1202)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1730)
at ecl.d295556.test.TestD295556.test003(TestD295556.java:143)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1002)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:641)
... 45 more


[EL Info]: 2014-09-08 13:05:15.033--ServerSession(1433925045)--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
[EL Info]: connection: 2014-09-08 13:05:16.568--ServerSession(1433925045)--testpu login successful
[EL Fine]: sql: 2014-09-08 13:05:16.585--ServerSession(1433925045)--Connection(565238012)--SELECT ID FROM ECL295ENTA WHERE ID <> ID
[EL Fine]: sql: 2014-09-08 13:05:16.79--ServerSession(1433925045)--Connection(565238012)--SELECT ID FROM ECL295ENTB WHERE ID <> ID
[EL Fine]: sql: 2014-09-08 13:05:16.842--ServerSession(1433925045)--Connection(565238012)--SELECT entACollection_ID FROM ECLENTA_ENTB WHERE entACollection_ID <> entACollection_ID
[EL Fine]: sql: 2014-09-08 13:05:16.934--ServerSession(1433925045)--Connection(565238012)--SELECT DISTINCT t1.ID, t1.STRDATA, t0.ID, t0.STRDATA, t0.STRINGVALUE FROM ECL295ENTA t0, ECLENTA_ENTB t2, ECL295ENTB t1 WHERE ((t2.ECL295EntB_ID = t1.ID) AND (t0.ID = t2.entACollection_ID))
[EL Fine]: sql: 2014-09-08 13:05:17.041--ServerSession(1433925045)--SELECT 1 FROM DUAL
[EL Warning]: 2014-09-08 13:05:17.094--UnitOfWork(179265569)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

Error Code: 932
Call: SELECT DISTINCT t1.ID, t1.STRDATA, t0.ID, t0.STRDATA, t0.STRINGVALUE FROM ECL295ENTA t0, ECLENTA_ENTB t2, ECL295ENTB t1 WHERE ((t2.ECL295EntB_ID = t1.ID) AND (t0.ID = t2.entACollection_ID))
Query: ReadAllQuery(referenceClass=ECL295EntB sql="SELECT DISTINCT t1.ID, t1.STRDATA, t0.ID, t0.STRDATA, t0.STRINGVALUE FROM ECL295ENTA t0, ECLENTA_ENTB t2, ECL295ENTB t1 WHERE ((t2.ECL295EntB_ID = t1.ID) AND (t0.ID = t2.entACollection_ID))")


On Sep 8, 2014, at 12:49 PM, andrei ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:

raQuery.addJoinedAttribute(query.getExpressionBuilder().anyOf("entACollection"));



_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev


_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev



--
Rick Curtis


_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev

_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev



_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev

Back to the top