JQPL - PostgreSQL - IN () (Syntax Error)

Description

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.

Environment

PostgreSQL

Activity

Show:
Guilherme Scaglia
August 11, 2017, 6:32 PM

I've also confirmed the same problem also happens when using the Criteria API from JPA 2.1

Assignee

Unassigned

Reporter

Guilherme Scaglia

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure