Hi,

while using sql query, if the query has an "in or negated form" (i.e. if the sql query has "IN" or "NOT IN" clauses ) in it and if querycache is enabled and setCachable is made true, Hibernate framework throws a ClassCastException.
i.e. a query like
String queryString = "SELECT EMPNAME FROM EMP WHERE EMPID NOT IN('11','22')";
session.createSQLQuery(queryString).setCachable(true);

I have deployed my application on Jboss 4.0.4 and I am using hibernate 3.1.3. Hibernate is deployed as MBean and OSCache is used as second level cache. I have enabled query cache.

I have tested the scenario using hibernate-3.2.0.cr5 as well. But the problem persistes. The exception is originating from hibernate framework.

Here is the exception trace.

java.lang.ClassCastException
at org.hibernate.cache.StandardQueryCache.put(StandardQueryCache.java:83)
at org.hibernate.loader.Loader.putResultInQueryCache(Loader.java:2118)
at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2062)
at org.hibernate.loader.Loader.list(Loader.java:2020)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)


If I change the SQL to HQL as below.

queryString = "SELECT e.empname FROM Emp e WHERE e.empid NOT IN('11','22')");
session.createQuery(queryString).setCachable(true);
the query executes without any problem.

Also, If I disable querycache or remove setCacheable(true) then the query executes without any problem.

Am I missing something here? or is this a bug in hibernate framework?

Regards,
Teja