I was able to get a similar double LEFT JOIN to work with ManyToOnes, so, it
seems it's valid at least in that case. Not sure if that would make a
difference. Here it works fine if I'm using the javaagent.
Feb 27, 2007 11:59:09 AM edu.uchicago.at.chalk.ChalkService findEvents
FINEST: SELECT e FROM Event e LEFT JOIN FETCH e.course LEFT JOIN FETCH
e.user ORDER BY e.startTime
Feb 27, 2007 11:59:09 AM
oracle.toplink.essentials.session.file:/J:/Chalk/ChalkLibrary/build/classes/-Chalk.sql
FINE: SELECT t0.PK1, t0.END_DATE, t0.START_DATE, t0.TEXT_FORMAT_TYPE,
t0.MESSAGE, t0.DTMODIFIED, t0.EVENT_TYPE, t0.SUBJECT, t0.USERS_PK1,
t0.CRSMAIN_PK1, t1.PK1, t1.END_DATE, t1.COURSE_ID, t1.START_DATE,
t1.COURSE_DESC, t1.COURSE_NAME, t2.PK1, t2.FIRSTNAME, t2.LASTNAME, t2.EMAIL,
t2.MIDDLENAME, t2.USER_ID FROM BB_BB60.CALENDAR t0, BB_BB60.USERS t2,
BB_BB60.COURSE_MAIN t1 WHERE ((t1.PK1 (+) = t0.CRSMAIN_PK1) AND (t2.PK1 (+)
= t0.USERS_PK1)) ORDER BY t0.START_DATE ASC
However, if I'm not using the javaagent, I receive the following NPE. I went
back and checked the original query (the one on Items) that I was having
problems with and whether the javaagent is enabled or not in that case
doesn't make a difference.
Feb 27, 2007 11:54:48 AM edu.uchicago.at.chalk.ChalkService findEvents
FINEST: SELECT e FROM Event e LEFT JOIN FETCH e.course LEFT JOIN FETCH
e.user ORDER BY e.startTime
Feb 27, 2007 11:54:48 AM
oracle.toplink.essentials.session.file:/J:/Chalk/ChalkLibrary/build/classes/-Chalk.sql
FINE: SELECT t0.PK1, t0.START_DATE, t0.END_DATE, t0.DTMODIFIED, t0.MESSAGE,
t0.TEXT_FORMAT_TYPE, t0.EVENT_TYPE, t0.SUBJECT, t0.USERS_PK1,
t0.CRSMAIN_PK1, t1.PK1, t1.COURSE_NAME, t1.COURSE_ID, t1.START_DATE,
t1.COURSE_DESC, t1.END_DATE, t2.PK1, t2.MIDDLENAME, t2.LASTNAME,
t2.FIRSTNAME, t2.EMAIL, t2.USER_ID FROM BB_BB60.CALENDAR t0, BB_BB60.USERS
t2, BB_BB60.COURSE_MAIN t1 WHERE ((t1.PK1 (+) = t0.CRSMAIN_PK1) AND (t2.PK1
(+) = t0.USERS_PK1)) ORDER BY t0.START_DATE ASC
Feb 27, 2007 11:54:51 AM
oracle.toplink.essentials.session.file:/J:/Chalk/ChalkLibrary/build/classes/-Chalk
WARNING:
java.lang.NullPointerException
at
oracle.toplink.essentials.mappings.ForeignReferenceMapping.buildClone(ForeignReferenceMapping.java:122)
at
oracle.toplink.essentials.internal.descriptors.ObjectBuilder.populateAttributesForClone(ObjectBuilder.java:2136)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.populateAndRegisterObject(UnitOfWorkImpl.java:2836)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.cloneAndRegisterObject(UnitOfWorkImpl.java:673)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkIdentityMapAccessor.getAndCloneCacheKeyFromParent(UnitOfWorkIdentityMapAccessor.java:152)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkIdentityMapAccessor.getFromIdentityMap(UnitOfWorkIdentityMapAccessor.java:90)
at
oracle.toplink.essentials.internal.sessions.IdentityMapAccessor.getFromIdentityMap(IdentityMapAccessor.java:295)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.registerExistingObject(UnitOfWorkImpl.java:3075)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.registerExistingObject(UnitOfWorkImpl.java:3024)
at
oracle.toplink.essentials.queryframework.ObjectBuildingQuery.registerIndividualResult(ObjectBuildingQuery.java:319)
at
oracle.toplink.essentials.internal.descriptors.ObjectBuilder.buildWorkingCopyCloneNormally(ObjectBuilder.java:441)
at
oracle.toplink.essentials.internal.descriptors.ObjectBuilder.buildObjectInUnitOfWork(ObjectBuilder.java:406)
at
oracle.toplink.essentials.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:372)
at
oracle.toplink.essentials.queryframework.ReportQueryResult.processItem(ReportQueryResult.java:205)
at
oracle.toplink.essentials.queryframework.ReportQueryResult.buildResult(ReportQueryResult.java:167)
at
oracle.toplink.essentials.queryframework.ReportQueryResult.<init>(ReportQueryResult.java:83)
at
oracle.toplink.essentials.queryframework.ReportQuery.buildObject(ReportQuery.java:579)
at
oracle.toplink.essentials.queryframework.ReportQuery.buildObjects(ReportQuery.java:628)
at
oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:776)
at
oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
at
oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)
at
oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)
at
oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2219)
at
oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
at
oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:346)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:453)
at
edu.uchicago.at.chalk.ChalkService.findEvents(ChalkService.java:121)
at
edu.uchicago.at.chalk.ChalkServiceTest.testFindEvents(ChalkServiceTest.java:136)
Jon
----- Original Message -----
From: "Tom Ware" <tom.ware_at_Oracle.com>
To: <persistence_at_glassfish.dev.java.net>
Sent: Monday, February 19, 2007 3:27 PM
Subject: Re: Is it legal to have a JPQL query that has multiple LEFT JOIN
FETCHes?
> Hi Jon,
>
> I am a bit surprised you are seeing an issue with the query you list
> below.
>
> Our test framework has some similar queries that work. For instance:
>
> "SELECT e from Employee e JOIN FETCH e.projects"
>
> Is the exception trace for your current query the same as for the
> previous query?
>
> BTW: I took a quick look at the BNF for JPQL and it seems your double
> joining query is not legal. JPQL requires a range_variable_declaration
> for each fetch join. That means:
> Item i LEFT JOIN FETCH i.reservations
>
> Is Legal
>
> And the second part:
>
> LEFT JOIN FETCH i.subItems
> Is missing something. (you can't join to "Item i" from the previous
> clause)
>
> -Tom
>
>
> Jon Miller wrote:
>
>>I just tried the following which throws a NPE too. So, it looks like it's
>>probably the fact that I'm mapping to the same class rather than the fact
>>that I had two LEFT JOINs which is what I thought might have been the
>>original problem. Is this a bug?
>>
>>SELECT i FROM Item i LEFT JOIN FETCH i.subItems ORDER BY i.name
>>
>>Jon
>>
>
> --
> Tom Ware
> Principal Software Engineer
> Oracle Canada Inc.
>
> Direct: (613) 783-4598
> Email: tom.ware_at_oracle.com
>