spring boot - Empty in expression cause an exception in native query

Question

I use Hibernate and JpaRepository to handle my DB operations.

I have this query (to simplify because original query is quite big):

@Query(value="select * from history h where h.project_id in :projects", nativeQuery=true)
List<History> getHistoriesByProjectsIn(@Param("projects")List<Long> projects);

and it is working when I pass valid and not empty List<Long>. However when I pass empty list which may happen and it is not unusual in my scenario I get:

org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

Can anyone give me a hint on how to get rid of it?

Answer 1

The postgresql documentation states: in predicate can not hold empty list

expression IN (value [, ...])

and as hibernate passes the native queries as is, it's predictable that this leads to a problem.

But as workaround you can follow this suggestion: initialize the list with an impossible value.

EDIT

Actually, this behaviour was changed in hibernate 5.4.10. See the commit:

SHA-1: ab9ae431858dc6727023d7f03bd6925f99011c62

* HHH-8901 replace "in ()" SQL with "in (null)" in QueryParameterBindingsImpl

And for your case the following sql will be generated:

select * from history h where h.project_id in (null)

It looks strange that the task HHH-8901 description completely irrelevant to these changes.