query specified join fetching, but the owner of the fetched association was not present in the select list

Question

I'm selecting two id columns but get error specified:

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=r,role=null,tableName=REVISIONS,tableAlias=revision1_,origin=ENTITY_CHANGED_IN_REVISION entitychan0_,columns={entitychan0_.REV_ID ,className=ru.csbi.registry.domain.envers.Revision}}] [ select ec.id as entityChangeId, r.id as revisionId from ru.csbi.registry.domain.envers.EntityChange as ec  inner join fetch ec.revision as r  where ec.groupEntityId = :groupEntityId and ec.groupName = :groupName  and r.timestamp < :entityDateFrom  and r.timestamp > :entityDateTo  and (        ec.revisionType in (0, 5, 1, 4, 2 )       and not ( ec.otherGroupEntityModified = false and ec.thisGroupEntityModified = true and ec.rowDataModified = false and ec.collectionOfNotGroupEntityModified = false   )      )  group by ec.id, r.id  having count(*) > :start order by r.id desc]

Some code:

String hql = " select ec.id as entityChangeId, r.id as revisionId from EntityChange as ec " +
            " inner join fetch ec.revision as r " +
            " where ec.groupEntityId = :groupEntityId" +
            " and ec.groupName = :groupName " +
            " and r.timestamp < :entityDateFrom " +
            " and r.timestamp > :entityDateTo " +
            " and ( " +
            "       ec.revisionType in (" + 
                        RevisionType.ADD.getRepresentation() + ", " + 
                        RevisionType.ONLY_DATA_PROPERTY_MOD.getRepresentation() + ", " +
                        RevisionType.BOTH_COLLECTION_AND_PROPERTY_MOD.getRepresentation() + ", " +
                        RevisionType.ONLY_COLLECTION_PROPERTY_MOD.getRepresentation() + ", " +
                        RevisionType.DEL.getRepresentation() +
                    " ) " +
            "     and not ( "+
                    "ec.otherGroupEntityModified = false and " +
                    "ec.thisGroupEntityModified = true and " +
                    "ec.rowDataModified = false and " +
                    "ec.collectionOfNotGroupEntityModified = false " +
                "  ) " +
            "     ) " +
            " group by ec.id, r.id " +
            " having count(*) > :start" +
            " order by r.id desc";

How to fix the error and what am I doing wrong?

for future searchers of this question, in my situation, i was joining a non lazy attribute. When i remove joining clause it was solved. — merveotesi, Feb 18 '13 at 14:14
I think in your case the problem is that you do not select the whole entity (EntityChange) but only a few columns. Fetch clause makes only sense, if the root entity is selected and you want a mapped collection / entitiy to be filled by joining it. — andy, May 31 '19 at 12:24

score 128 · Accepted Answer · answered Sep 17 '12 at 13:09

128

Use regular join instead of join fetch (by the way, it's inner by default):

String hql = " select ec.id as entityChangeId, r.id as revisionId from EntityChange as ec " + 
        " join ec.revision as r " + ...

As error message tells you, join fetch doesn't make sense here, because it's a performance hint that forces eager loading of collection.

answered Sep 17 '12 at 13:09

axtavt

239,438
41
511
482

12

Just to clarify, `join fetch` can also be used to force the eager loading of an association such as a field annotated with @ManyToOne and not just collections. – Robert Hunt Feb 16 '15 at 11:40
82

Hm.. I'm facing a similar issue, but I do need to do the join fetch to avoid n+1 problem – Zhenya Mar 17 '16 at 09:58
5

@levgen, I don't know details of your query but, keep in mind that count query should not have "fetch" at all. https://codingexplained.com/coding/java/spring-framework/fetch-query-not-working-spring-data-jpa-pageable#comment-293535 – smile Aug 26 '17 at 22:08
4

@levgen It's hard to help you without seeing a code, but if it happens that you use Spring Data with `@Query` annotation, then you can specify separate queries for fetching and count: see [this question (Spring-Data FETCH JOIN with Paging is not working)](https://stackoverflow.com/q/21549480/1429387) for details. – naXa stands with Ukraine Oct 09 '17 at 13:36
Should I use EntityChange entity as result object or some other POJO with 2 columns? – Ismail Yavuz Dec 20 '19 at 08:31
20

Removing "fetch" led me to N+1 problem and caused everything in my application to hang. Would not recommend this solution to anyone. – Alkis Mavridis Jan 21 '20 at 17:44
4

This is not a solution for the problem as the author wanted a join fetching to probably avoid n+1 issue. – Hollerweger Jul 22 '20 at 12:33
2

Use the answer below(add count query).This solution leads to an n+1 issue. – Dmitry Avgustis Aug 09 '21 at 09:28
In contrast to what others commented, this didn't lead to N+1 additional queries for my solution. The join worked and only one query was executed. Not sure, though, if there is something else in my code that helped with that. – havryliuk Mar 02 '23 at 14:49

score 72 · Answer 2 · edited Dec 24 '21 at 08:34

72

As you need the join fetch, removing the fetch won't meet your needs.

What you should do instead is specify a count query together with it.

Assuming you are paginating the result, below is a JPA query that takes id as a param and will cause the problem you specified and the second query solves this by adding count query to it.

Note: fk_field is the attribute in tableA that has the one-to-many rln. The count query does not use join fetch.

@Query(value = "from TableA a LEFT JOIN FETCH a.fk_field where a.id = :id") 

@Query(value = "from TableA a LEFT JOIN FETCH a.fk_field where a.id = :id", 
  countQuery = " select  count(a) from TableA a left join a.fk_field where a.id = :id")

edited Dec 24 '21 at 08:34

lealceldeiro

14,342
6
49
80

answered Feb 07 '19 at 22:57

mykey

1,943
19
13

2

Yes, this is the right answer if you want to keep FETCH and avoid n+1. Thanks! – alonso_50 May 14 '21 at 10:16
1

Yes, this is the correct answer, thank you. – Ousama May 31 '21 at 13:23
1

Thanks! Saved me. Don't know why hibernate cannot do this automatically. – Jan Cizmar Nov 28 '22 at 12:43
The other answer from axtavt leads to N+1 problem. This is the right answer! – Nelio Alves Apr 02 '23 at 11:14

score 11 · Answer 3 · answered Jun 01 '22 at 01:39

Not relevant to OP's specific query, but for me same query specified join fetching, but the owner of the fetched association was not present in the select list error was thrown due to incorrect JOIN FETCH syntax of a nested relation, it must be referenced via an alias whereas I intuitively tried to refer to it via full path.

Bad: (causes not present in the select list error)

SELECT * FROM TableA a
JOIN FETCH a.relationB
JOIN FETCH a.relationB.relationC

Good: (works!)

SELECT * FROM TableA a
JOIN FETCH a.relationB AS b
JOIN FETCH b.relationC

Hope that helps someone

score 2 · Answer 4 · answered Mar 15 '22 at 22:09

2

This issue has relation with the N+1 problem on Spring Data, if you would reach to use JpaSpecificationExecutor, you can solve it you could change Page to Slice, review this input, please: Link

answered Mar 15 '22 at 22:09

J. Abel

890
3
17
38

score 1 · Answer 5 · edited Nov 03 '21 at 06:47

1

You have to put your related item column into selection clause

select ec.id as entityChangeId, r.id as revisionId, **r.revision** 
from EntityChange as ec " +
        " inner join fetch ec.revision as r " +

edited Nov 03 '21 at 06:47

Jin Kwon

20,295
14
115
184

answered Feb 02 '21 at 22:58

Dmitry

812
8
13

score 0 · Answer 6 · answered May 05 '23 at 11:03

if you are using Spring JPA, then this query should fix it for you

@Query( value = “FROM TableName t LEFT JOIN FETCH t.columnName WHERE t.columnName = value”, countQuery = “select count(t) from TableName t left join t.columnName WHERE t.columnName = value”)

Note the where clause can be removed to you don’t have what you are filtering by

query specified join fetching, but the owner of the fetched association was not present in the select list

6 Answers6

Linked