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


Back to the top