[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2207) Suggested fix for HQL - MySQL setMaxResults issue

[Alejandro Revilla (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2207?page=comments#action_25180 ] 

Alejandro Revilla commented on HHH-2207:
----------------------------------------

Happens to us with simple code like this:

Criteria crit = session.createCriteria (ASimpleEntity.class);
crit.setMaxResults(1);
crit.list();

For the records, we´ve tried with a simple hbm.xml like this:

<hibernate-mapping package="org.jpos.ee">
  <class name="SysConfig" table="sysconfig">
    <id name="id" column="id" type="string" length="64">
      <generator class="assigned" />
    </id>
    <property name="value" type="text" />
    <property name="readPerm"  type="string" length="32" />
    <property name="writePerm" type="string" length="32" />
  </class>
</hibernate-mapping>

full stacktrace follows:
Target exception: org.hibernate.exception.GenericJDBCException: could not execute query

org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2155)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2036)
        at org.hibernate.loader.Loader.list(Loader.java:2031)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at bsh.Reflect.invokeMethod(Unknown Source)
        at bsh.Reflect.invokeObjectMethod(Unknown Source)
        at bsh.Name.invokeMethod(Unknown Source)
        at bsh.BSHMethodInvocation.eval(Unknown Source)
        at bsh.BSHPrimaryExpression.eval(Unknown Source)
        at bsh.BSHPrimaryExpression.eval(Unknown Source)
        at bsh.Interpreter.eval(Unknown Source)
        at bsh.Interpreter.source(Unknown Source)
        at bsh.Interpreter.main(Unknown Source)
Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
        at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2250)
        at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2276)
        at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:2234)
        at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1615)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1559)
        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:2152)
        ... 18 more

Hibernate version: svn r10726
MySQL JDBC Driver: mysql-connector-java-3.1.12-bin.jar
Java 1.4.2


> Suggested fix for HQL - MySQL setMaxResults issue
> -------------------------------------------------
>
>          Key: HHH-2207
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2207
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.2.0.ga
>  Environment: Hi 3.2.0.ga MySQL
>     Reporter: Toby Moore
>  Attachments: querloaderdiff.patch
>
> Original Estimate: 0 minutes
>         Remaining: 0 minutes
>
> Hi,
> After some experimentation, we'd like to suggest an easy fix to a problem we encountered.
> When using HQL with the Hibernate 3 entity manager to limit the number of records returned ( at the database level, via generated SQL, not in memory) we were getting an exception due to the query builder trying to attach a parameter to the prepared statement that was out of bounds :
> by: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
>    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
>    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2556)
>    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2584)
>    at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:2540)
>    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setInt(NewProxyPreparedStatement.java:677)
>    at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1616)
>    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560)
>    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:2156)
>    ... 10 more
> On looking at the code in the method bindParameterValues in org.hibernate.loader.hql.QueryLoader ( line 491 ) the method returns the next parameter position that needs to be set, not the amount of parameters it has added, which is expected in Loader.java on the line
>          col += bindParameterValues( st, queryParameters, col, session );
> This causes the parameter count to get out of sync and thus throw the expcetion saying that it can't add a parameter out of range.
> A simple fix for this is to change the return line in QueryLoader to be
> return position - startPosition;
> which will return the amount of parameters this method added.
> We've tested this on our mysql implementation and it works, and run the unit tests to check that no extra errors are generated. If this seems like a good fix then i'll submit it as a patch via the JIRA, but would like to hear any thoughts first?
> Kind regards,
> TobyM - MindCandyDesign London

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira