JPA auto query generation using method name not working as expected

Question

In the below findAll and findByUserNameAndPlantId is working where as findAllByPlantId and findAllByAccessPlantsSet_AccessPlantKey_Plant_PlantIdIn is not working. Both generating wrong query(compared to spring boot 1.5.3-release) and throwing same exception.

It was working in spring boot 1.5.3-release but in 2.7.16 not working. Any issue in the code?

public interface UserPagingAndSortingRepository extends PagingAndSortingRepository<User, Long> {

    @Override
    Page<User> findAll(Pageable page);

    @Query("SELECT  DISTINCT  u FROM User u LEFT OUTER JOIN  u.accessPlantsSet a where (UPPER(u.userName) = UPPER(?1)) and a.accessPlantKey.plant.plantId in (?2)")
    Page<User> findByUserNameAndPlantId(String userName, List<Long> plantIds, Pageable page);

    @Query("SELECT  u FROM User u LEFT OUTER JOIN  u.accessPlantsSet ap where ap.accessPlantKey.plant.plantId in (?1)")
    Page<User> findAllByPlantId(List<Long> plantIds, Pageable pageParam);

    Page<User> findAllByAccessPlantsSet_AccessPlantKey_Plant_PlantIdIn(List<Long> plantIds, Pageable pageParam);
}

more details here https://github.com/spring-projects/spring-data-jpa/issues/3226

The exception is

Unable to find com.gtl.industryapp.auth.middleware.domain.Plant with id 12; nested exception is javax.persistence.EntityNotFoundException: Unable to find com.gtl.industryapp.auth.middleware.domain.Plant with id 12

The same code runs properly without this exception in spring boot 1.5.3-RELEASE

I made a change in query and method name as below

@Query("SELECT u FROM User u LEFT OUTER JOIN  u.accessPlantsSet a WHERE u.userName like :name and a.accessPlantKey.plant.plantId in (:plantIds)")
Page<User> findAllByNameAndPlantId(@Param("name") String name, @Param("plantIds") List<Long>  plantIds,Pageable page); 

I am calling the above method in another class like this below

 Page<user> pagedUsers = userPagingAndSortingRepository.findAllByNameAndPlantId("test@test.lt",plantIds, pageParam);

I am getting the result for this. But when I call with below parameter, it is throwing same exception. There are multiple users with user name having test.lt. But it fails. I think if there are multiple users in the result this is failing ? Is it like that ?

 Page<user> pagedUsers = userPagingAndSortingRepository.findAllByNameAndPlantId("%test.lt%",plantIds, pageParam);

Answer 1

After going through all the generated queries and corresponding tables I found that issue is due to the data. In the RM_AccessPlants table corresponding to userId 19 there was a plantId 12 and that plantid 12 was not present in the Mstr_Plant.

Old JPA dont check whether the plantid foreign key with value 12 present in Mstr_plant table where as new JPA does.