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

Still no luck reproducing the reported issue.  I’ve tried some variations of the query, if I put “DISTINCT” straight in the query itself I can force an ORA-00932 (still testing with Oracle DB to verify the test’s fidelity at reproducing the original problem) -- but it occurs on both ECL 2.5.0 and 2.5.1 (which has the fix for bug #295556) so that is not likely the correct scenario.  Findings are provided below in the test’s commentary.


    /*
     * Bug #295556
     * 
     * Steps to Reproduce:
     * 1. Create a class A with a @Lob Field
     * 2. Create a second class B that has a many-to-many reference to class A
     * 3. Batchload all Bs (including all As)
     * 4. SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB Error Code: 932
     * 
     * Notes: ECL 2.5.0 does not have fix, 2.5.1 does.
     */   
    @Test
    public void test002() {
        EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("testpu");

        

        populateDB(emf);

        

        EntityManager em = emf.createEntityManager();

       

//        ReadAllQuery raQuery = new ReadAllQuery(ECL295EntB.class);
//        raQuery.setJPQLString("SELECT OBJECT(b) FROM ECL295EntB b");
//        List resultSet = (List) ((org.eclipse.persistence.internal.jpa.EntityManagerImpl) em).getSession().executeQuery(raQuery);

               

        String qString = "SELECT OBJECT(b) from ECL295EntB b LEFT JOIN FETCH b.entACollection"; // Passes 2.5.0+
//        String qString = "SELECT DISTINCT OBJECT(b) from ECL295EntB b LEFT JOIN FETCH b.entACollection"; // Fails on 2.5.0 and 2.5.1 with ORA-00932, probably not target scenario
//        String qString = "SELECT OBJECT(b) from ECL295EntB b JOIN b.entACollection entA"; // Passes 2.5.0+
//        String qString = "SELECT DISTINCT OBJECT(b) from ECL295EntB b JOIN b.entACollection entA"; // Fails on 2.5.0 and 2.5.1 with ORA-00932
//        String qString = "SELECT OBJECT(b) from ECL295EntB b"; // Passes 2.5.0+
//        String qString = "SELECT DISTINCT OBJECT(b) from ECL295EntB b"; // Fails on 2.5.0 and 2.5.1 with ORA-00932

        

        Query q = em.createQuery(qString);
        q.setHint(QueryHints.BATCH , "b.entACollection"); 
//        q.setHint(QueryHints.QUERY_TYPE, QueryType.ReadObject);
        List<ECL295EntB> resultSet = q.getResultList();      

        

        assertNotNull(resultSet);

        

        ECL295EntB entB_find = (ECL295EntB) resultSet.get(0); //  em.find(ECL295EntB.class, 1);
        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());      
    }


On Sep 4, 2014, at 10:07 AM, J Grassel <fyrewyld@xxxxxxxxx> wrote:

I’ve switched to using Oracle for the database, and still cannot reproduce with ECL 2.5.0.  Switching to use a ReadAllQuery did not change the outcome, either (attempt left commented out.)

[EL Info]: 2014-09-04 10:01:31.001--ServerSession(544214961)--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
...
[EL Fine]: sql: 2014-09-04 10:01:33.078--ServerSession(544214961)--Connection(1725418407)--CREATE TABLE ECL295ENTA (ID NUMBER(10) NOT NULL, STRDATA VARCHAR2(255) NULL, STRINGVALUE CLOB NULL, PRIMARY KEY (ID))
[EL Fine]: sql: 2014-09-04 10:01:33.148--ServerSession(544214961)--Connection(1725418407)--CREATE TABLE ECL295ENTB (ID NUMBER(10) NOT NULL, STRDATA VARCHAR2(255) NULL, PRIMARY KEY (ID))
[EL Fine]: sql: 2014-09-04 10:01:33.213--ServerSession(544214961)--Connection(1725418407)--CREATE TABLE ECLENTA_ENTB (ECL295EntB_ID NUMBER(10) NOT NULL, entACollection_ID NUMBER(10) NOT NULL, PRIMARY KEY (ECL295EntB_ID, entACollection_ID))
[EL Fine]: sql: 2014-09-04 10:01:33.28--ServerSession(544214961)--Connection(1725418407)--ALTER TABLE ECLENTA_ENTB ADD CONSTRAINT ECLENTA_ENTB_entACollection_ID FOREIGN KEY (entACollection_ID) REFERENCES ECL295ENTA (ID)
[EL Fine]: sql: 2014-09-04 10:01:33.348--ServerSession(544214961)--Connection(1725418407)--ALTER TABLE ECLENTA_ENTB ADD CONSTRAINT FK_ECLENTA_ENTB_ECL295EntB_ID FOREIGN KEY (ECL295EntB_ID) REFERENCES ECL295ENTB (ID)
[EL Fine]: sql: 2014-09-04 10:01:40.598--ServerSession(544214961)--Connection(1725418407)--SELECT ID, STRDATA FROM ECL295ENTB
[EL Fine]: sql: 2014-09-04 10:01:40.708--ServerSession(544214961)--Connection(1725418407)--SELECT t1.ID, t1.STRDATA, t1.STRINGVALUE, t0.ECL295EntB_ID FROM ECLENTA_ENTB t0, ECL295ENTB t2, ECL295ENTA t1 WHERE ((t0.ECL295EntB_ID = t2.ID) AND (t1.ID = t0.entACollection_ID))


Entity Definitions are still:

@Entity
public class ECL295EntA {
    @Id
    private int id;
    
    @Basic
    private String strData;
    
    @Lob String stringValue;
}

@Entity
public class ECL295EntB {
    @Id
    private int id;
    
    @Basic
    private String strData;
    
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE})
    @JoinTable(name="ECLENTA_ENTB")
    @MapKey(name="strData")
    private Map<String, ECL295EntA> entACollection;
}

With the executing test:

public class TestD295556 {
    @Test
    public void test002() {
        EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("testpu");
        
        populateDB(emf);
        
        EntityManager em = emf.createEntityManager();
       
//        ReadAllQuery raQuery = new ReadAllQuery(ECL295EntB.class);
//        raQuery.setJPQLString("SELECT OBJECT(b) FROM ECL295EntB b");
//        List resultSet = (List) ((org.eclipse.persistence.internal.jpa.EntityManagerImpl) em).getSession().executeQuery(raQuery);
               
        Query q = em.createQuery("SELECT OBJECT(b) from ECL295EntB b");
        q.setHint(QueryHints.BATCH , "b.entACollection"); 
        q.setHint(QueryHints.QUERY_TYPE, QueryType.ReadObject);
        List<ECL295EntB> resultSet = q.getResultList();      
        
        assertNotNull(resultSet);
        
        ECL295EntB entB_find = (ECL295EntB) resultSet.get(0); //  em.find(ECL295EntB.class, 1);
        assertNotNull(entB_find);
       
        boolean containsTarget = entB_find.getEntACollection().containsKey("EntA-1");
        assertTrue(containsTarget);
        
        assertEquals(10, entB_find.getEntACollection().size());      
    }
    
    private void populateDB(EntityManagerFactory emf) {
        System.out.println("Populating DB...");
        EntityManager em = emf.createEntityManager();
        
        em.getTransaction().begin();
        em.createNativeQuery("DELETE FROM ECLENTA_ENTB").executeUpdate();
        em.createNativeQuery("DELETE FROM ECLENTA_ENTB2").executeUpdate();
        em.createNativeQuery("DELETE FROM ECL295EntA").executeUpdate();
        em.createNativeQuery("DELETE FROM ECL295EntB").executeUpdate();
        
        em.getTransaction().commit();
        em.close();
        
        em = emf.createEntityManager();
        em.getTransaction().begin();
        
        final int entBCount = 10;
        ECL295EntB[] entBArr = new ECL295EntB[entBCount];
        
        SecureRandom sr = new SecureRandom();
        
        for (int i = 0; i < entBCount; i++) {
            ECL295EntB entB = new ECL295EntB();
            entB.setId(i+1);
            entB.setStrData("Entity B-" + i);
            em.persist(entB);
            
            entBArr[i] = entB;
        }
        
        
        
        for (int i = 0; i < 10; i++) {
            ECL295EntA entA = new ECL295EntA();
            entA.setId(i+1);
            entA.setStrData("EntA-" + i);
            
            StringBuffer sb = new StringBuffer();
            for (int j = 0; j < 200; j++) {
                sb.append(sr.nextLong());sb.append(' ');
            }
            entA.setStringValue(sb.toString());
            
                       
            for (int j = 0; j < entBCount; j++) {
                entBArr[j].getEntACollection().put(entA.getStrData(), entA);
            }
            
            em.persist(entA);
        }
        
        em.getTransaction().commit();
        em.close();
        System.out.println("DB population complete.");
    }
}

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

I'll try to get some DB2 and help you with reproduction scenario at the beginning of next week. But you guys know about DB2 much more then me to we'll see. :)

Dne 9/4/14, 10:51 AM, Tomas Kraus napsal(a):
Try it with master branch where all changes are present.

Dne 9/3/14, 7:32 PM, J Grassel napsal(a):
Curious, the bug describes the steps to reproduce as:
Steps to Reproduce:
1. Create a class A with a @Lob Field
2. Create a second class B that has a many-to-many reference to class A
3. Batchload all Bs (including all As)
4. SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB 
Error Code: 932
I’ve adjusted my entities so that B has the @Lob String stringValue instead of A, but the problem still does not surface — the DISTINCT keyword refuses to show up:

[EL Info]: 2014-09-03 12:25:36.921--ServerSession(1715144603)--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
[EL Fine]: sql: 2014-09-03 12:29:54.18--ServerSession(1786057406)--Connection(1216669372)--SELECT ID, STRDATA, STRINGVALUE FROM ECL295ENTB
[EL Fine]: sql: 2014-09-03 12:29:54.24--ServerSession(1786057406)--Connection(1216669372)--SELECT t1.ID, t1.STRDATA, t0.ECL295EntB_ID FROM ECLENTA_ENTB t0, ECL295ENTB t2, ECL295ENTA t1 WHERE ((t0.ECL295EntB_ID = t2.ID) AND (t1.ID = t0.entACollection_ID))



On Sep 3, 2014, at 11:56 AM, andrei ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:

To reproduce this lob should be defined on the source of ManyToMany (on EntB, not EntA).
On 9/3/2014 12:40 PM, J Grassel wrote:
I realize that it was fixed for Oracle only, I saw that in both the bug report and through looking at the 80947a6 change set (where only OraclePlatform.isLobCompatibleWithDistinct() returns false else default true per DatabasePlatform.)  I tested a sample query on the db2 command console with a query that includes DISTINCT and DB2 rejects it as expected:

db2 => describe table ECL295EntA

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No    
STRDATA                         SYSIBM    VARCHAR                    255     0 Yes   
STRINGVALUE                     SYSIBM    CLOB                     64000     0 Yes   

db2 => select distinct stringvalue from ECL295EntA where id = 1
SQL0134N  Improper use of a string column, host variable, constant, or 
function "STRINGVALUE".  SQLSTATE=42907

However, with ECL 2.5.0 (which if I’m not mistaken does not include the fix for #295556) generates SQL without DISTINCT:

[EL Info]: 2014-09-03 10:26:51.364--ServerSession(917250329)--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
...
[EL Fine]: sql: 2014-09-03 10:26:51.818--ServerSession(917250329)--Connection(966907808)--SELECT ID, STRDATA FROM ECL295ENTB
[EL Fine]: sql: 2014-09-03 10:26:51.821--ServerSession(917250329)--Connection(966907808)--SELECT t1.ID, t1.STRDATA, t1.STRINGVALUE, t0.ECL295EntB_ID FROM ECLENTA_ENTB t0, ECL295ENTB t2, ECL295ENTA t1 WHERE ((t0.ECL295EntB_ID = t2.ID) AND (t1.ID = t0.entACollection_ID))

Before I can verify (or update #295556 for DB2 support) that the problem is resolved, I need to first reproduce the problem using an affected ECL build in order to have confidence in the unit test.  That is where I’m getting blocked on — so there must be something that I am overlooking in my unit test.


On Sep 3, 2014, at 10:49 AM, andrei ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:

I believe it's Oracle-only problem, it won't allow:
  select distinct my_id, my_lob from my_table
Try that on DB2, I believe it will work.
On 9/3/2014 11:45 AM, Tomas Kraus wrote:
Here is what's written in our internal bug:
Bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=295556 was fixed for Oracle only. It should have been fixed for Db2 also.
The isLobCompatibleWithDistinct method is overridden in OraclePlatform, but not in Db2Platform.

TESTCASE DETAILS (REQUIRED):
----------------------------
Create a descriptor for a clob column, and try to select it in Db2.

I checked master branch and it's true, OraclePlatform overrides this to false but DB2Platform does not. I don't know if this is source of any problems in DB2. Adding Jay Turner (who reported this) to cc, maybe he will tell you more.

Tomas

Dne 9/3/14, 5:29 PM, J Grassel napsal(a):
Hey there, I’m having a bit of trouble reproducing this issue.  I wrote the following unit test and ran it against ECL 2.5.0 but unfortunately the test refuses to fail.  Is there something important from the problem reproduction description that I am overlooking?

Here’s my attempt at reproducing the problem:

@Entity
public class ECL295EntA {
    @Id
    private int id;
    
    @Basic
    private String strData;

    @Lob String stringValue;
}

@Entity
public class ECL295EntB {
    @Id
    private int id;
    
    @Basic
    private String strData;
    
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE})
    @JoinTable(name="ECLENTA_ENTB")
    @MapKey(name="strData")
    private Map<String, ECL295EntA> entACollection;
}

public class TestD295556 {
...
    @Test
    public void test002() {
        EntityManagerFactory emf = null;
        EntityManager em = null;
        
        emf = javax.persistence.Persistence.createEntityManagerFactory("testpu");
        em = emf.createEntityManager();
        
        em.getTransaction().begin();
        em.createNativeQuery("DELETE FROM ECLENTA_ENTB").executeUpdate();
        em.createNativeQuery("DELETE FROM ECL295EntA").executeUpdate();
        em.createNativeQuery("DELETE FROM ECL295EntB").executeUpdate();
        
        em.getTransaction().commit();
        em.close();
        
        em = emf.createEntityManager();
        em.getTransaction().begin();
        
        final int entBCount = 10;
        ECL295EntB[] entBArr = new ECL295EntB[entBCount];
        
        for (int i = 0; i < entBCount; i++) {
            ECL295EntB entB = new ECL295EntB();
            entB.setId(i+1);
            entB.setStrData("Entity B-" + i);
            em.persist(entB);
            
            entBArr[i] = entB;
        }
        
        SecureRandom sr = new SecureRandom();
        
        for (int i = 0; i < 10; i++) {
            ECL295EntA entA = new ECL295EntA();
            entA.setId(i+1);
            entA.setStrData("EntA-" + i);
            
            StringBuffer sb = new StringBuffer();
            for (int j = 0; j < 200; j++) {
                sb.append(sr.nextLong());sb.append(' ');
            }
            entA.setStringValue(sb.toString());
            
                       
            for (int j = 0; j < entBCount; j++) {
                entBArr[j].getEntACollection().put(entA.getStrData(), entA);
            }
            
            em.persist(entA);
        }
        
        em.getTransaction().commit();
        em.close();
        emf.close();
        
        emf = javax.persistence.Persistence.createEntityManagerFactory("testpu");
        em = emf.createEntityManager();
       
        Query q = em.createQuery("SELECT b from ECL295EntB b");
        q.setHint(QueryHints.BATCH , "b.entACollection");
        
        List<ECL295EntB> resultSet = q.getResultList();
        assertNotNull(resultSet);
        
        ECL295EntB entB_find = resultSet.get(0); //  em.find(ECL295EntB.class, 1);
        assertNotNull(entB_find);
       
        boolean containsTarget = entB_find.getEntACollection().containsKey("EntA-1");
        assertTrue(containsTarget);
        
        assertEquals(10, entB_find.getEntACollection().size());      
    }
...


On Aug 26, 2014, at 4:53 AM, Tomas Kraus <tomas.kraus@xxxxxxxxxx> wrote:

Hi all,

I would like to ask guys from IBM to have a look at https://bugs.eclipse.org/bugs/show_bug.cgi?id=295556 and port this fix into DB2 code too. I wanted to do it myself but we currently do not have DB2 available here. Please let me know if someone has time available to fix it in next 2-3 weeks. We have to finalize 2.6 soon and this issue is one of those we would like to fix in 2.6.

Tomas

_______________________________________________
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

_______________________________________________
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



_______________________________________________
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