2

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?

SternK
  • 11,649
  • 22
  • 32
  • 46
Mithrand1r
  • 2,313
  • 9
  • 37
  • 76

1 Answers1

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.

SternK
  • 11,649
  • 22
  • 32
  • 46
  • actually I have found a solution to my problem already. hibernate v5.4.21 fix this issue so update of hibernate is just enough – Mithrand1r Aug 30 '20 at 15:02
  • I am surprised that this is an issue. This is just invalid sql as postgresql documentation states. Hibernate just passed the `native query` directly to the jdbc driver. And by the way as stated in the hibernate [documentation](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#hql-in-predicate) the empty list is also prohibited for the hql/jpql queries. – SternK Aug 30 '20 at 15:12
  • Actually this behavior was changed in hibernate 5.4.10, see my updated answer. – SternK Aug 30 '20 at 16:19