[JIRA] (HHH-16236) Cannot use lists in where clause
by Svetliokapralov (JIRA)
Svetliokapralov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=70121%3... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjIzMzkyMTVl... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16236?atlOrigin=eyJpIjoiNjIzMz... ) HHH-16236 ( https://hibernate.atlassian.net/browse/HHH-16236?atlOrigin=eyJpIjoiNjIzMz... ) Cannot use lists in where clause ( https://hibernate.atlassian.net/browse/HHH-16236?atlOrigin=eyJpIjoiNjIzMz... )
Issue Type: Bug Affects Versions: 6.1.7 Assignee: Unassigned Components: hibernate-core Created: 28/Feb/2023 03:34 AM Priority: Major Reporter: Svetliokapralov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=70121%3... )
This is a sample project demonstrating several Hibernate 6 problems related to using 'IN (:list)' in where clause: https://github.com/svkap/hibernate-6-bug-sample ( https://github.com/svkap/hibernate-6-bug-sample )
The project uses docker-compose for spinning up PostgreSQL + Flyway for db initialization. It uses spring-data and lombok.
It seems there are problems when lists are used in queries and with coalesce.
The Entities are fairly simple but have different column types.
There are 3 different entity definition:
1. Using PostgreSQL enum + @Enumerated(EnumType.STRING)
2. Using varchar column + @Convert(...)
3. Using int column + @Convert(...)
—
The sample project tests several different queries and scenarios.
Result summary:
1. Entity creation work for all scenarios.
2. Entity update work for all scenarios.
3. Return entity, use SpEl ( (:#
{#list == null} = true) OR (i.list IN (:list)) ) works in JPQL
4. Pass Integer/String to native query works for coalesce and SpEl only when projection is returned
—
The exceptions seem the same when using PostgreSQL enum, Converter + String and Converter + Integer:
1. Return entity, native query + pass enums to repository method + ( (coalesce(:list, null) is null) OR (i.list IN (:list)) )
1. java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.JdbcMapping.getJdbcValueBinder()" because "jdbcMapping" is null
2. Return entity, native query + pass strings to repository method + ( (coalesce(:list, null) is null) OR (i.list IN (:list)) )
1. org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id; SQL [n/a]
3. Return entity, native query + pass null to repository method + ( (coalesce(:list, null) is null) OR (i.list IN (:list)) )
1. org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id; SQL [n/a]
4. Return entity, native query + pass enums to repository method + ( (:#{#list == null}
= true) OR (i.list IN (:list)) )
1. java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.JdbcMapping.getJdbcValueBinder()" because "jdbcMapping" is null
5. Return entity, native query + pass null to repository method + ( (:#
{#list == null} = true) OR (i.list IN (:list)) )
1. org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id; SQL [n/a]
6. Return entity, native query + pass enum to repository method
1. java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.JdbcMapping.getJdbcValueBinder()" because "jdbcMapping" is null
7. Return entity, native query + pass string to repository method + ( (:#{#list == null}
= true) OR (i.list IN (:list)) )
1. org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id; SQL [n/a]
7. Return entity, JPQL query + pass enum to repository method + ( (coalesce(:list, null) is null) OR (i.list IN (:list)) )
1. java.lang.ClassCastException: class java.util.ImmutableCollections$List12 cannot be cast to class java.lang.Enum (java.util.ImmutableCollections$List12 and java.lang.Enum are in module java.base of loader 'bootstrap')
7. Return projection, native query + pass enum to repository method + ( (:#
{#list == null}
= true) OR (i.list IN (:list)) )
1. java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.JdbcMapping.getJdbcValueBinder()" because "jdbcMapping" is null
( https://hibernate.atlassian.net/browse/HHH-16236#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16236#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:65be9f2 )