How to fix "org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list"

Hibernate ORM
1

Following JPQL is executing and returning expected resultset :

SELECT 
    fprm 
FROM 
    FpRawMaterial fprm 
JOIN FETCH 
    fprm.finishedProduct 
JOIN FETCH 
    fprm.rawMaterial 
ORDER BY 
    fprm.finishedProduct.productName

But getting above QueryException while joining one more table “uom” to JPQL :

SELECT 
	fprm 
FROM 
	FpRawMaterial fprm 
JOIN FETCH 
	fprm.finishedProduct 
JOIN FETCH 
	fprm.rawMaterial 
JOIN FETCH 
	fprm.finishedProduct.uom 
ORDER BY 
	fprm.finishedProduct.productName

If I don’t join the “uom” table Hibernate will execute multiple sql queries to fetch uom for each product → N+1 issue.

How can I write the correct JPQL for following SQL query:

SELECT fprm.fp_id, fprm.rm_id, p1.fp_base_qty, u1.name 'Unit', p1.product_name AS 'FINISHED_PRODUCT', 
p.product_name AS 'Raw_Material', fprm.rm_qty, u.name 'Unit' FROM erp_mini.fp_raw_material fprm 
INNER JOIN erp_mini.product p ON fprm.rm_id = p.product_id 
INNER JOIN erp_mini.product p1 ON fprm.fp_id = p1.product_id
INNER JOIN erp_mini.uom u ON p.base_uom_id = u.uom_id
INNER JOIN erp_mini.uom u1 ON p1.base_uom_id = u1.uom_id
ORDER BY p1.product_name;

Error Details:

org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.itsys.erp.server.dal.entities.Product.uom,tableName=Uom,tableAlias=uom4_,origin=Product product3_,columns={product3_.base_uom_id ,className=com.itsys.erp.server.dal.entities.Uom}}] [SELECT fprm FROM com.itsys.erp.server.dal.entities.FpRawMaterial fprm JOIN FETCH fprm.rawMaterial JOIN FETCH fprm.finishedProduct JOIN FETCH fprm.finishedProduct.uom ORDER BY fprm.finishedProduct.productName DESC]

In some others posts I read that the solution to remove the JOIN FETCH and use just JOIN, but that certainly creates N+1 issue.
Any ideas how it could be resolved?
Thanks.

2

Try it like this:

SELECT 
	fprm 
FROM 
	FpRawMaterial fprm 
JOIN FETCH 
	fprm.finishedProduct fP
JOIN FETCH 
	fprm.rawMaterial 
JOIN FETCH 
	fP.uom 
ORDER BY 
	fP.productName

Notice the fP alias I’m using to reuse the JOIN declaration.

3 
SELECT fprm FROM FpRawMaterial fprm JOIN FETCH fprm.finishedProduct fp JOIN FETCH fprm.rawMaterial JOIN FETCH fp.uom ORDER BY fp.productName

Eclipse is showing compile time error for JOIN FETCH query with alias, as you can’t declare alias for JOIN FETCH :

JOIN FETCH expressions cannot be defined with an identification variable.

In one of the SO post I saw the following solution to add alias for JOIN FETCH JPQL as :

SELECT fprm FROM FpRawMaterial fprm JOIN FETCH fprm.finishedProduct JOIN fprm.finishedProduct fp JOIN FETCH fprm.rawMaterial JOIN FETCH fp.uom ORDER BY fp.productName

writing JOIN twice , one for JOIN FETCH and one to declare alias. Don’t know wether it is as per JPA specification or a workaround as am not able to see Hibernate generated SQL query yet.
But while executing I’m getting same error that of org.hibernate.QueryException : query specified join fetching, but the owner of the fetched association was not present in the select list
And what’s meaning of the error ? who is the owner of fetched association here?

4

You can declare an alias for the JOIN FETCH. That’s supported just fine by Hibernate.

Declaring the join twice is a mistake. Use aliases instead.

5

THanks Vlad, it worked. I was wondering why JPA allow alias for simple JOIN and not for JOIN FETCH. NOw I have to change JPA Error settings in Eclipse from show Error to show Warning to work the JPQL. Further is there any way to populate Entities by executing native SQL queries ?

6

The JPA spec does say that using an alias for JOIN FETCH is forbidden.

NOw I have to change JPA Error settings in Eclipse from show Error to show Warning to work the JPQL.

Last time I used Eclipse was in 2005, prior to trying out IntelliJ IDEA. I never looked back.

Further is there any way to populate Entities by executing native SQL queries ?

Of course, there is a way. Check out this article for some examples.

7

Hello, I am trying to do the same thing, except that I have to go one level deeper, for example

…
JOIN FETCH
fP.uom fpUom
JOIN FETCH
fpUom.someOtherCollection
…

Now I start getting NullPointerException at org.hibernate.engine.internal.StatefulPersistenceContext.getLoadedCollectionOwnerOrNull(StatefulPersistenceContext.java:825)

Is there any way to go one level deeper fetching collections? Thanks!

8

Hi,
I’ve the same problem with this query

@Query(value = "SELECT new org.apache.commons.lang3.tuple.ImmutableTriple(d,row,e) FROM Document d LEFT JOIN FETCH d.eyeExam e LEFT JOIN DocumentRow row ON row.document.id=d.id WHERE d.contact.id=:contactId AND d.type='SALES_ORDER' AND d.status='PROCESSED' AND d.assemble=true AND row.productType='FRAME'")

but I’m already using aliases. This is the Exception:

Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=e,role=it.test.server.model.accounting.documents.Document.eyeExam,tableName=`EyeExam`,tableAlias=eyeexam1_,origin=`Document` document0_,columns={document0_.`eyeExam_id`,className=it.test.server.model.medical.exams.EyeExam}}]

Do you have any hint?

Thanks

9

Looks like a bug. FWIW in Hibernate 6.0 this is not an issue anymore. In the meantime, you can avoid the constructor expression and create the ImmutableTriple instances in Java code.