Hibernate produces SQL - "in ()" - which is invalid in at least Oracle, MySQL and Postgres

Description

Given HQL like:

"from Animal an where an.id in ()"

Hibernate generates SQL like:

"select columns from animal where id in ()"

The "in ()" syntax is not valid in Oracle, MySQL or Postgres. It is valid in H2.

I think that Hibernate should produce "in (null)" instead of "in ()" for platforms where "in ()" is not valid. I believe that "in (null)" has the same semantics as "in ()".

There are already some tests relevant to this issue:

https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/test/java/org/hibernate/test/hql/HQLTest.java#L249

  • I believe that this test checks that "in ()" parses as valid HQL - which, since was fixed, it does for all dialects.

https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/test/java/org/hibernate/test/hql/ASTParserLoadingTest.java#L492

  • I believe this test checks that "in ()" translates into SQL that runs on all databases. This test is currently disabled for databases other than H2. When this issue is fixed then this test should pass on all dialects.

Environment

Database platforms: at least Oracle, MySQL and Postgres

Status

Assignee

Unassigned

Reporter

Gareth Daniel Smith

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.2.0.Final
5.2.12

Priority

Minor
Configure