How can I query a null in a Long value without getting "Expected NUMBER but got BINARY" from OracleDB?

Question

I'm using JPQL and want to query for a null value in a Long field. But I always get a ORA-00932: inconsistent datatypes: expected NUMBER got BINARY. As I've seen there are many people who have problems with this, but does anybody has a workaround for this?

For example this is the query "SELECT a FROM Auftrag a WHERE :id is null OR a.id = :id" and later on I'm setting id with setParameter("id", null).This is used in a more complex query for filtering purpose, so null means in our case to ignore the filter on the column.

Anybody who has an idea?

Kind Regards!

Answer 1

I don't know the specifics of JPQL nor how Oracle handles the WHERE condition of your query. But I'd bet that the second part of your WHERE condition is not completely ignored and that a.id = NULL is causing the problem. Apart from the apparently inconsistent data types a condition like some_value = NULL may not evaluate to TRUE or FALSE but to NULL (at least this happens on PostgreSQL).

EDIT
For your specific use case the combined condition :id IS NULL OR a.id = NULL still works as intended on PostgreSQL. But in another context you won't get any rows with some_value = NULL even if some_value is null. So I think for the sake of robust and understandable code an expression like some_value = NULL should be avoided in any case.
END EDIT

You might be able to work around the problem in JPQL with

SELECT a FROM Auftrag a WHERE :id is null OR a.id = COALESCE(:id, -1)

at least this is possible with native Hibernate HQL. In this case the second part of the WHERE condition evaluates to FALSE if :id is null but the whole WHERE condition evaluates to TRUE, which is what you want.

But for dynamic filtering queries a better approach would be to use the JPA 2.0 Criteria API and include the :id parameter in the query only if it is not null. Again, I don't know the specifics of JPA Criteria but with native Hibernate Criteria this would be

public List<Auftrag> findByFilter(Long id) { 
  Criteria criteria = session.createCriteria(Auftrag.class);
  if (id != null) {
    criteria.add(Restrictions.eq("id", id));
  } // if
  return criteria.list();
}

Hope that helps.

Answer 2

In Oracle (12), i found a workaround using TO_NUMBER:

SELECT a FROM Auftrag a WHERE :id is null OR a.id = TO_NUMBER(:id)