11

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!

MikeO
  • 209
  • 1
  • 6
  • 13
  • what does Auftrag class look like? Also, SQL syntax looks wrong ("where id is null", NOT "where :id is null"...), also, id = null is wrong logic as well. – someuser2 Dec 27 '11 at 12:07
  • not really. first of all this is not SQL but JPQL (there are some minor differences) and if you want to set :id to null and the condition should return true you have to write ":id is null". The resulting statement would be "null is null" in that case and this is true, so the second condition will be ignored. – MikeO Dec 27 '11 at 12:34
  • I don't think that your second condition is completely ignored. I bet that the exception is caused by comparing the numeric a.id to null (:id) – jan Dec 27 '11 at 13:07
  • Please post the generated SQL of this JPQL query. You should be able to see the SQL either in the database log or with `hibernate.show_sql=true` and `hibernate.format_sql=true` in your Hibernate configuration. – tscho Dec 27 '11 at 13:18
  • 1
    Can't post it here. ist too long for a comment and self answering is only allowed on people with 100 reputations and above. The only thing I can tell you, your're right there is a "= null" condition in the query. But as I can remember this is allowed in SQL92. The second point toplink can handle that without any problems. Also take a look on https://hibernate.onjira.com/browse/HHH-2851 – MikeO Dec 27 '11 at 13:46
  • what's also working is to set it this way `((org.hibernate.ejb.QueryImpl) query).getHibernateQuery().setParameter("id", id, StandardBasicTypes.LONG);` But that's dissatifying, because I'm using here the implementation beyond JPA. And this implementation can change... :-( – MikeO Dec 27 '11 at 14:03
  • @MikeO: I think you don't have to post any additions to your original question (like the generated SQL) in comments or self-answers. You should be able to edit your original question with your current reputation. Also include the restriction that you don't want to use any JPA provider specific features in your question. – tscho Dec 27 '11 at 14:14
  • The `LONG` datatype has been deprecated for 20 years (since Oracle 8i). Why are you still using it? – a_horse_with_no_name Jul 18 '13 at 10:38
  • It's a problem when using Java `Long` objects that map to a numeric database column, not a database column with `LONG` datatype – Jim Tough Jun 05 '14 at 14:01

4 Answers4

6

I've had the same problem, I solved it by reversing OR sides, for example:

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

didn't work, but reversing OR sides like this:

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

worked perfectly. I don't understand why, but it works. It probably has something to do with "short circuiting" but in case of null both statements are evaluated anyway. Hope someone can explain this.

Celebes
  • 1,293
  • 1
  • 11
  • 20
6

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)
izio
  • 103
  • 1
  • 6
6

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.

tscho
  • 1,926
  • 12
  • 15
  • he uses null to ingnore the filtering part. If :id is null then he will get a select something where true, so he will not have any filter, and this is what he want. – Florin Ghita Dec 27 '11 at 14:16
  • @FlorinGhita: To be honest I don't know what the SQL standard defines concerning `some_value = NULL` nor how Oracle handles this, but at least on PostgreSQL 9.1 this evaluates to NULL and not to TRUE/FALSE. And afaik there is no guarantee for short circuit evaluation of something like `TRUE OR ...` in SQL. So, I think such an expression should be avoided in any case. – tscho Dec 27 '11 at 14:23
  • in Oracle null = null is false, so, this should not be a problem. – Florin Ghita Dec 27 '11 at 14:25
  • @tscho: to bet is not to be sure ;-). but you're right, you shouldn't use that either if another database has dependencies on that. I will check that what PGSQL does here in detail. A colleague has used that way of filtering in a Hibernate project they did a few months ago. Worked very well there and the performance was much better than using the criterias. Even they are so complex that there is more thinking necessary to understand that part of code. Do you know "Clean Code" from Robert C.Martin. Very good book and one of his principles is to keep your code small, simple and understandable – MikeO Dec 27 '11 at 14:30
  • @MikeO: Please not my edit concerning the effects of `some_value = NULL` on PostgreSQL. – tscho Dec 27 '11 at 15:07
  • After a deeper look on that, I've also the opinion that I've to use criterias here. Even it's harder to understand because my original query is much more complicated, it's the only way to have a guarantee here that you get always a correct statement generated :-(. So thank you for your input! – MikeO Dec 27 '11 at 15:54
  • @MikeO: Thanks for accepting my answer, but I am curious to know how you eventually solved the issue. Is `COALESCE` possible in a JPQL query or did you use the JPA 2.0 Criteria API? Or did you use Hibernate specific features like HQL or or Hibernate Criteria? – tscho Dec 27 '11 at 16:00
  • @tscho: Couldn't use `COALESCE` because this results also in an `ORA-00932: inconsistent datatypes: expected BINARY got NUMBER`. So I've changed to the JPA Criteria API. Native hibernate was no option because using this would break our architecture in our software. The problem is that hibernate handles "null" in parameters wrong and the query statement isn't correctly generated then. So the workaround would be an if clause before which handles this. But that's imho no clean code. So the criteria API is the more clean solution, even I don't like it ;-) – MikeO Dec 30 '11 at 08:47
0

COALESCE didn't work for me. I fixed this limitation by using a non-used value (in my case -1) to get the filtering right

SELECT a FROM Auftrag a WHERE :id = -1 OR a.id = :id
DiegoFlores
  • 21
  • 5