Hi Gang

I am using Hibernate 3.2.0 with an Oracle 10g DB.

I have had fairly extensive experience with Hibernate (also using it as a JPA implementation) over the last couple of years and read "Hibernate in Action" cover to cover several times, so I don't post this message lightly.

I am trying to execute a very simple piece of HQL through the Query interface (I have also tried the equivalent using the Criteria interface with the same result) using pagination (i.e. the setMaxResult(int) operation). The code is as follows:

org.hibernate.Session hSession = (org.hibernate.Session)entityManager.getDelegate();
org.hibernate.Query hQuery = hSession.createQuery("from Title t");
hQuery.setFirstResult (0);
hQuery.setMaxResults(3);
return hQuery.list();

The "Title" class/table basically has 2 properties plus some audit columns and holds "reference" data such as {(Dr, Doctor), (Mr, Mr), (Adm, Admiral)} etc

When I try to execute the Query I get an error. I switched Hibernate over to debug mode and saw something I suspect is a bug related to Oracle's unique way of doing things. Highlights from the log are as follows:

DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - HQL: from org.lpab.domain.Title t
DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select title0_.CODE as CODE13_, title0_.CREATED_BY as CREATED2_13_, title0_.CREATED_DATE as CREATED3_13_, title0_.LAST_MODIFIED_BY as LAST4_13_, title0_.LAST_MODIFIED_DATE as LAST5_13_, title0_.NAME as NAME13_ from TITLE title0_
DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
1DEBUG org.hibernate.engine.query.HQLQueryPlan - HQL param location recognition took 0 mills (from Title t)
DEBUG org.hibernate.engine.query.QueryPlanCache - located HQL query plan in cache (from Title t)
DEBUG org.hibernate.engine.query.HQLQueryPlan - find: from Title t
DEBUG org.hibernate.engine.QueryParameters - named parameters: {}
DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG org.hibernate.SQL -
select
*
from
( select
title0_.CODE as CODE13_,
title0_.CREATED_BY as CREATED2_13_,
title0_.CREATED_DATE as CREATED3_13_,
title0_.LAST_MODIFIED_BY as LAST4_13_,
title0_.LAST_MODIFIED_DATE as LAST5_13_,
title0_.NAME as NAME13_
from
TITLE title0_ )
where
rownum <= ?
DEBUG org.hibernate.SQL -
select
*
from
( select
title0_.CODE as CODE13_,
title0_.CREATED_BY as CREATED2_13_,
title0_.CREATED_DATE as CREATED3_13_,
title0_.LAST_MODIFIED_BY as LAST4_13_,
title0_.LAST_MODIFIED_DATE as LAST5_13_,
title0_.NAME as NAME13_
from
TITLE title0_ )
where
rownum <= ?
DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select title0_.CODE as CODE13_, title0_.CREATED_BY as CREATED2_13_, title0_.CREATED_DATE as CREATED3_13_, title0_.LAST_MODIFIED_BY as LAST4_13_, title0_.LAST_MODIFIED_DATE as LAST5_13_, title0_.NAME as NAME13_ from TITLE title0_]
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4635)
at oracle.jdbc.driver.OraclePreparedStatement.setInt (OraclePreparedStatement.java:4627)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:116)
at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1618)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1557)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java :224)
at org.hibernate.loader.Loader.doList(Loader.java:2157)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2041)
at org.hibernate.loader.Loader.list(Loader.java:2036)
at org.hibernate.loader.hql.QueryLoader.list (QueryLoader.java:388)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list (SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

I noticed that 2 different SQL statements are present in the log. One has a sub query and the Oracle "rownum" logical column with a binding parameter associated with it (as expected) but the SQL reported to be "not executable" is a "select all" statement with no parameters and is actually the sub query of the first statement. I have done enough research to know that the "Invalid column index" msg is reported by Oracle when trying to bind a parameter to a prepared statement that takes no parameters. I guess that Hibernate is only dealing with the sub query at execution time thus the parameter binding process is failing?

Any help would be much appreciated. Thanks in advance.

I see the exact same problem when using the console in the eclipse hibernate tools, against Oracle database.

If I execute a simple 'from <class>' query, no worries, but the same query fails with the "Invalid column index" error if I try to limit the number of rows returned using the max results dropdown selection box. Surely it's trying to use rownum to in the exact same way to limit results...

Hi Paul

I expect you are right. We are experiencing the same problem. I'm very surprised neither of us have been able to find a solution anywhere and that no one has posted anything helpful. I would have thought thousands of Hibernate users would have had the same problem.

Tim

I am not sure, but I think that the first row number in oracle must be 1, not 0

Hi,

I have used Hibernate 3.2.0, and Oracle 10G, with pagination using setMaxResult and setFirstRow as well. No Such problem here!

This is just a suggestion, try giving the PK of your table a unique index. Also check and ensure you are using the Oracle9 dialect.


Hope this helps, do let me know.

Hi Rober2D2, hi bellerophon.

Thanks for your responses. It is correct the Oracle pseudo column "rownum" has a minimum value of 1, however the JPA spec states that paginated result columns start at 0 (thus it the implementation's responsibility to translate appropriately).

I have this element in my persistence.xml:

<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/>

so it seems the Hibernate JPA implementation should have all the information it needs to properly interact with the DB (at least as far as the dialect is concerned).

Any further suggestion however would be most appreciated. I don't believe I have stumbled on such an obvious bug. I must be missing something.

re. Tim

Hi All

I downloaded the latest version of Hibernate (3.2.5) and the problem disappeared. I therefore deduce it was a Hibernate bug. Very surprised no one else came up against it. Perhaps 3.2.4.sp1 was superseded very quickly and wasn't widely used. Thanks to all who sought to help to out.

re. Tim