JPQL JOIN FETCH Error

Hello

I get an error when executing the following JPQL query:

select doi
from Doi doi
left join fetch doi.metadata metadata
left join fetch metadata.titles titles
left join fetch metadata.creators creators
left join fetch metadata.descriptions descriptions
left join fetch metadata.publishers publishers
left join fetch metadata.contributors contributors
left join fetch metadata.dates dates
left join fetch metadata.types types
left join fetch metadata.formats formats
left join fetch metadata.identifiers identifiers
left join fetch metadata.sources sources
left join fetch metadata.languages languages
left join fetch metadata.relations relations
left join fetch metadata.subjects subjects
left join fetch metadata.coverages coverages
left join fetch metadata.rights rights
where LOWER( doi.doi ) LIKE LOWER( :doi )

The error is: Unexpected query token 'metadata'

This query works on Hibernate with Oracle / MySQL / Postgres, so I'm assuming the JPQL is valid.

Any ideas?

Thanks

John

The query is not a valid JPQL query since JOIN FETCH should be followed by a path with no variable (and maybe it is supported by Hibernate / HQL as an extension to JPQL).

Try:

SELECT doi
FROM Doi doi
LEFT JOIN doi.metadata metadata
LEFT JOIN FETCH metadata.titles
LEFT JOIN FETCH metadata.creators
LEFT JOIN FETCH metadata.descriptions
LEFT JOIN FETCH metadata.publishers
LEFT JOIN FETCH metadata.contributors
LEFT JOIN FETCH metadata.dates
LEFT JOIN FETCH metadata.types
LEFT JOIN FETCH metadata.formats
LEFT JOIN FETCH metadata.identifiers
LEFT JOIN FETCH metadata.sources
LEFT JOIN FETCH metadata.languages
LEFT JOIN FETCH metadata.relations
LEFT JOIN FETCH metadata.subjects
LEFT JOIN FETCH metadata.coverages
LEFT JOIN FETCH metadata.rights
WHERE LOWER( doi.doi ) LIKE LOWER( :doi )

Thanks - the query is now being excepted by objectdb, however now I am getting a...

java.lang.OutOfMemoryError: Java heap space

This is after the db crunches for about 20s or so. Note that there is only one single object (doi) in the db. Ideas?

Also: (strangely) if try the following query:

SELECT doi
FROM Doi doi
LEFT JOIN doi.metadata metadata
LEFT JOIN FETCH metadata.titles
WHERE LOWER( doi.doi ) LIKE LOWER( "10.000/test" )

...I get the error:

Invalid fetch path: .titles for type ch.ethz.id.wai.doi.bo.Metadata

John

More details are required in order to understand the problem.

Is there a sample database that you can upload in order to show the OutOfMemoryError and the query compilation error in action?

The OutOfMemoryError may be related to this issue (result of using many variables in the query, since every JOIN, including  JOIN FETCH defines a variable). Until this issue is solved, try using no more than 10-15 variables per query.

The query compilation error may be related to missing schema (still no Metadata instances in the database and the class has not been listed as a managed type in the persistence.xml file), but this only a guess. As indicated above, more details are required.

The class "Metadata" does exist in the database, as well as the field "titles" and its type "MetadataString" (see attachments).

But actually, there is no point in my continuing the evaluation if a dozen or so joins breaks your product - this is an intrinsic part of our architecture and we have structures that are considerably more complex those above. I thought that handling complex object trees is something that OODBs are supposed to excel at... ?

If you think of anything that would meet our requirements, do let me know...

Object databases are indeed very good in handling complex object trees, but not through massive JOINs. Anyway, this specific issue will be solved soon, and if you provide a sample database (possibly in a support ticket) it may be possible to provide an immediate fix.

OK - I have attached a copy of the offending DB which you are more than welcome to take a shot at. The select statement from post #2 above causes the OutOfMemoryError. I might add that this can surely only be due to your query planing algorithm as there is currently only 1 Doi and no actual Metadata.

Do let me know if you are able to get it working. Thanks  - John

Build 2.3.7_03 fixes these issues. Please try it.