Instead of:
ExpressionBuilder exprBuilder =
raQuery.getExpressionBuilder();
_expression_ batchReadProduct =
exprBuilder.get("entACollection");
_expression_ batchReadProductLob =
batchReadProduct.get("stringValue");
raQuery.addBatchReadAttribute(batchReadProduct);
raQuery.addBatchReadAttribute(batchReadProductLob);
let's try:
raQuery.addBatchReadAttribute("entACollection");
On 9/5/2014 2:19 PM, J Grassel wrote:
Hmm, I built a new test case and refactored the JPA query out of
it, and using 2.5.0 it still does not produce the offending query,
as the following test passes:
/*
* 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 test003() {
EntityManagerFactory emf =
javax.persistence.Persistence.createEntityManagerFactory("testpu");
//
populateDB(emf);
// To save time, comment out after the db has been prepped
EntityManager em =
emf.createEntityManager();
Session session =
((org.eclipse.persistence.internal.jpa.EntityManagerImpl)
em).getSession();
ReadAllQuery raQuery = new
ReadAllQuery(ECL295EntB.class);
ExpressionBuilder
exprBuilder = raQuery.getExpressionBuilder();
_expression_
batchReadProduct = exprBuilder.get("entACollection");
_expression_
batchReadProductLob = batchReadProduct.get("stringValue");
raQuery.addBatchReadAttribute(batchReadProduct);
raQuery.addBatchReadAttribute(batchReadProductLob);
assertEquals(0,
raQuery.getDistinctState()); // Make sure this
is 0 so ECL needs to determine if DISTINCT should be used
List resultSet
= (List)
session.executeQuery(raQuery);
assertNotNull(resultSet);
assertNotEquals(0,
resultSet.size());
ECL295EntB entB_find =
(ECL295EntB) resultSet.get(0);
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());
}
[EL Info]: 2014-09-05
13:18:06.779--ServerSession(1103082466)--EclipseLink,
version: Eclipse Persistence Services -
2.5.0.v20130507-3faac2b
[EL Info]: connection: 2014-09-05
13:18:08.828--ServerSession(1103082466)--testpu login
successful
[EL Fine]: sql: 2014-09-05
13:18:08.845--ServerSession(1103082466)--Connection(1294192564)--SELECT
ID FROM ECL295ENTA WHERE ID <> ID
[EL Fine]: sql: 2014-09-05
13:18:09.051--ServerSession(1103082466)--Connection(1294192564)--SELECT
ID FROM ECL295ENTB WHERE ID <> ID
[EL Fine]: sql: 2014-09-05
13:18:09.103--ServerSession(1103082466)--Connection(1294192564)--SELECT
entACollection_ID FROM ECLENTA_ENTB WHERE entACollection_ID
<> entACollection_ID
[EL Fine]: sql: 2014-09-05
13:18:09.203--ServerSession(1103082466)--Connection(1294192564)--SELECT
ID, STRDATA FROM ECL295ENTB
[EL Fine]: sql: 2014-09-05
13:18:09.323--ServerSession(1103082466)--Connection(1294192564)--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))
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
_______________________________________________
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
|