in produces malformed query when called with an empty collection

[szanyierik]

Example code:

interface EmployeeRepository : JpaRepository<Employee, String>, KotlinJdslJpqlExecutor {
}

employeeRepository.findAll {
    select(entity(Employee::class))
        .from(entity(Employee::class))
        .where(path(Employee::id).`in`(ids))
}

When ids is a non-empty collection, the query is rendered correctly:

SELECT Employee FROM Employee AS Employee WHERE Employee.id IN (:param1){param1=asd}

However, when ids is an empty collection, the produced query is malformed (empty WHERE clause):

SELECT Employee FROM Employee AS Employee WHERE {}

This is probably a result of how JpqlIn is serialized - when compareValues is empty, the serializer just returns without doing anything, resulting in a malformed query.

For reference, Hibernate deals with similar situations by replacing the IN predicate with 1=0. For example,

@Query(
    """
        SELECT e FROM Employee AS e
        WHERE e.id IN :ids
    """
)
fun findAllEmployeesById(ids: Collection<String>): List<Employee>

turns into

select
    e1_0.id,
    e1_0.name
from
    employee e1_0 
where
    1=0

when ids is an empty collection.

This bug has already been reported here, but I was requested to create a new issue.

[kshired]

There's some difference between Jpa Query Method and @Query annotation.

interface ExampleRepository: JpaRepository<ExampleEntity, Long> {
    fun findAllByIdIn(ids: List<Long>): List<ExampleEntity>
    fun findAllByIdNotIn(ids: List<Long>): List<ExampleEntity>

    @Query("select e from ExampleEntity e where e.id in :ids")
    fun findAllByIdInCustom(ids: List<Long>): List<ExampleEntity>

    @Query("select e from ExampleEntity e where e.id not in :ids")
    fun findAllByIdNotInCustom(ids: List<Long>): List<ExampleEntity>
}

exampleRepository.findAllByIdIn(emptyList())
exampleRepository.findAllByIdNotIn(emptyList())
exampleRepository.findAllByIdInCustom(emptyList())
exampleRepository.findAllByIdNotInCustom(emptyList())

As a result of the above code, the SQL is generated as below.

select e1_0.id, e1_0.name from example e1_0 where e1_0.id in (NULL); -- findAllByIdIn
select e1_0.id, e1_0.name from example e1_0 where e1_0.id not in (NULL); -- findAllByIdNotIn
select e1_0.id, e1_0.name from example e1_0 where 1=0; -- findAllByIdInCustom
select e1_0.id, e1_0.name from example e1_0 where 1=0; -- findAllByIdNotInCustom

But since either in (NULL) or not in (NULL) is false in the end, I think it doesn't matter much.

[shouwn]

I deployed the 3.4.1 hotfix, please reopen this issue if you have similar issues.