Hibernate: Extra cross join for order by field in spring jpa repository

Question

I have a problem with a Spring JPARepository, when add sort of nested field object.

In controller it's called a method with param Pageable, contained sort == 'parent.shortDescription ASC and shortDescription ASC'. After Pageable go to mapObjectDao.findByType(...), hibernate construct wrong sql-query (with cross join).

//controller

public Page<MeteoInfoDto> list(MeteoInfoFilter filter, Pageable pageable) {
    mapObjectDao.findByType(MapObjectType.Meteo, pageable);
}

//repository

@Query(value = "select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

//HQL

select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type order by m.parent.shortDescription asc, m.shortDescription asc

//hibernate sql output

select
        mapobject0_.dbid as dbid1_27_0_,
        ....
        mapobject4_.parent_org_id as parent_15_27_4_ 
    from
        map_object mapobject0_ 
    left outer join
        map_object mapobject1_ 
            on mapobject0_.parent_id=mapobject1_.dbid 
    left outer join
        map_object mapobject2_ 
            on mapobject0_.road_id=mapobject2_.dbid 
    left outer join
        map_object mapobject3_ 
            on mapobject0_.parent_region_id=mapobject3_.dbid 
    left outer join
        map_object mapobject4_ 
            on mapobject0_.parent_org_id=mapobject4_.dbid 

    cross join map_object mapobject5_

    where
        mapobject0_.parent_id=mapobject5_.dbid 
        and mapobject0_.type=? 
    order by
        mapobject5_.short_description asc,
        mapobject0_.short_description asc limit ?

//model

public class MapObject extends DomainObject {

    private UUID id;
    private MapObjectType type;
    ...
    private MapObject parent;
    private MapObject parentRegion;
    private MapObject parentOrganization;
    private MapObject road;
    ...
    private Set<MapObject> children;
    private Set<MapObject> childrenRegion;
    private Set<MapObject> childrenOrganization;
}

Why it add cross join map_object mapobject5_ for sort parent.shortDescription?

Answer 1

to be honest, this is not solved but just worked-arround.

The question was why it creates a cross join instead of the expected left join internally - and this hasn't been answered unfortunatly.

Answer 2

Solved!

    @Query(value = "select m from MapObject as m left join m.parent as parent left join fetch m.parent left join fetch m.road left join fetch m.parentRegion left join fetch m.parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

Cross join removed, by adding left join with alias and no fetch.

 select
        mapobject0_.dbid as dbid1_27_0_,
        mapobject2_.dbid as dbid1_27_1_,
        mapobject3_.dbid as dbid1_27_2_,
        mapobject4_.dbid as dbid1_27_3_,
        mapobject5_.dbid as dbid1_27_4_,
        mapobject0_.id as id2_27_0_,

        mapobject0_.short_description as short_de3_27_0_,       
        ...
        mapobject0_.parent_org_id as parent_15_27_0_,
        mapobject2_.id as id2_27_1_,
        ...
        mapobject2_.parent_org_id as parent_15_27_1_,
        mapobject3_.id as id2_27_2_,
        ....
        mapobject3_.parent_region_id as parent_14_27_2_,
        mapobject4_.short_description as short_de3_27_3_,
     ...      
        mapobject4_.parent_org_id as parent_15_27_3_,
        mapobject5_.id as id2_27_4_,
        mapobject5_.short_description as short_de3_27_4_,
       ...
        mapobject5_.parent_org_id as parent_15_27_4_ 
    from
        map_object mapobject0_ 
    left outer join
        map_object mapobject1_ 
            on mapobject0_.parent_id=mapobject1_.dbid 
    left outer join
        map_object mapobject2_ 
            on mapobject0_.parent_id=mapobject2_.dbid 
    left outer join
        map_object mapobject3_ 
            on mapobject0_.road_id=mapobject3_.dbid 
    left outer join
        map_object mapobject4_ 
            on mapobject0_.parent_region_id=mapobject4_.dbid 
    left outer join
        map_object mapobject5_ 
            on mapobject0_.parent_org_id=mapobject5_.dbid 
    where
        mapobject0_.type=? 
    order by
        mapobject0_.short_description asc,
        mapobject1_.short_description asc limit ?`enter code here`