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
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

Back to the top