Consider the following JPQL query:
Consider also the case when you set the ids parameter to an empty collection. In such a case, Hibernate may generate a SQL such as:
When using PostgreSQL, the above SQL is invalid and generates an appropriate PSQLException at runtime, such as:
The PostgreSQL documentation for the IN operator claims that at least one item is required, and that this requirement is SQL-compliant. Despite this, the H2 database accepts such syntax, an perhaps other databases accepts it as well.
Either way, It seems Hibernate should either reject empty collections earlier on, remove the where clause entirely, or use some other workaround to avoid the PSQLException from being thrown.
I'm not attaching a test case because the bug is database-specific, and there is no way to run embedded PostgreSQL server in a JVM. Please let me know if there is procedure to submit a test case even in those circumstances.
I've also confirmed the same problem also happens when using the Criteria API from JPA 2.1