I believe that JPA parser always sets dontUseDistinct flag on every
query - if that's correct then the bug would never show up in a pure
JPA test.
To verify that print out useDistinct flag on the internal query:
((ObjectLevelReadQuery)((EJBQueryImpl)query).getDatabaseQuery()).getDistinctState();
As long as it's 2 (ObjectLevelReadQuery.DONT_USE_DISTINCT) distinct
would never be used.
If you explicitly require the internal query to use distinct:
((ObjectLevelReadQuery)((EJBQueryImpl)query).getDatabaseQuery()).useDistinct();
then the query should always fail (because distinct is incompatible
with lob).
Alternatively, if you reset the flag to its original undefined state
and let Eclipselink to decide whether to set it:
((ObjectLevelReadQuery)((EJBQueryImpl)query).getDatabaseQuery()).resetDistinct();
then the query should fail before the fix (2.5.0) and work with the
fix (2.5.1).
On 9/4/2014 3:26 PM, J Grassel wrote:
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
_______________________________________________
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
|
