Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Simple Native SQL with NULL Parameter fails on Oracle

Description

Encounter the similar problem in 5.3.7 which is described at:

Entity:

@Entity @Table(name = "ADDRESS") @Data public class Address { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "adddr_seq") @SequenceGenerator(name = "adddr_seq", allocationSize = 1) private Long id; @Column(name = "ADDRESS_VALUE") private String addressValue; @OneToOne(cascade = CascadeType.ALL) @JoinColumn(name = "PERSON_ID") private Person person; }

Native query with null parameter for NUMBER column type:

Query nativeQuery = em.createNativeQuery("select * from ADDRESS a where (? is null OR a.ID = ?)", Address.class); nativeQuery.setParameter(1, null); nativeQuery.setParameter(2, null); List resultList = nativeQuery.getResultList();

Exception thrown for version 5.4.27.Final:

SQL Error: 932, SQLState: 42000 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

When database column is VARCHAR2, exception didn't occur:

Query nativeQuery = em.createNativeQuery("select * from ADDRESS a where (? is null OR a.address_value = ?)", Address.class); nativeQuery.setParameter(1, null); nativeQuery.setParameter(2, null); List resultList = nativeQuery.getResultList(); System.out.println(resultList);

I also tried to execute same code with EclipseLink JPA provider and everything was fine.

I did test with Hibernate version org.hibernate.orm:hibernate-core:6.0.0.Alpha6 :

2021-01-17 20:36:04.343 TRACE 3184 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [JAVA_OBJECT] - [null] 2021-01-17 20:36:05.439 WARN 3184 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 17004, SQLState: 99999 2021-01-17 20:36:05.439 ERROR 3184 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid column type: 2000 2021-01-17 20:36:05.462 ERROR 3184 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select * from ADDRESS a where (? is null OR a.id = ?)]] with root cause java.sql.SQLException: Invalid column type: 2000 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4121) ~[ojdbc8-19.9.0.0.jar:19.9.0.0.0]

Currently, only solution for Hibernate version 5.4.27.Final is to wrap parameter with conversion function with required type:

select * from ADDRESS a where (TO_NUMBER(?) is null OR a.id = TO_NUMBER(?))

For Hibernate version 6.0.0.Alpha6 I didn't find working workaround yet.

Activity

Show:

Andreas Loew March 2, 2022 at 10:41 AM

This is already existing as a Spring Data JPA issue, but it’s still open and not assigned since three months - please join mie in voting for the Spring Data JPA issue… :-)

https://github.com/spring-projects/spring-data-jpa/issues/2370

Christian Beikov March 2, 2022 at 10:33 AM

Spring Data could pass TypedParameterValue values to avoid this issue, yes. But that is something you should request in the Spring community.

Andreas Loew March 2, 2022 at 10:23 AM

Is there also any chance to bring together the TypedParameterValue approach with declarative Queries like in Spring Data JPA repositories?

Such as, when I have within a Spring JpaRepository<TfzAbrechnungsportion, Long>

@Query(value = "SELECT (...)" + "FROM (...) " + "WHERE (...) AND ap.ende <= COALESCE(:zeitpunktEnde, ap.ende)" List<TfzAbrechnungsportion> getStatusHistory(@Param("vens") String vens, @Param("zeitpunktBeginn") Date zeitpunktBeginn, @Param("zeitpunktEnde") Date zeitpunktEnde);

being able to make the JDBC driver not bind the nullable param "zeitpunktEnde" to the type “VARBINARY" , but use a TypedParameterValue approach?

Currently, I don't see any other way than to explicitly fully implement the JpaRepository method in “old style” using EntityManager API and use TypedParameterValue to set parameter values…!?

Thanks a million! slightly smiling face

Christian Beikov March 2, 2022 at 8:51 AM

In case the type is unknown like in your example, Hibernate 6.0 now tries to ask the JDBC driver about the parameter type and has smarter fallbacks. I’m pretty sure that 6.0.0.CR1 fixes this already, but in general, all of this is dependent on the support of the JDBC driver. To make this work for all drivers, you will have to set a TypedParameterValueinstead.

Fixed

Details

Assignee

Reporter

Components

Fix versions

Priority

Created January 17, 2021 at 7:53 PM
Updated March 2, 2022 at 10:41 AM
Resolved March 2, 2022 at 8:52 AM

Flag notifications