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()); }
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."); } }
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:
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))
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.
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;
…
}
@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());
}
...
_______________________________________________
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
|
