[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5846) the setFirstResult and setMaxResults methods of criteria don't work properly

the setFirstResult and setMaxResults methods of criteria don't work properly ---------------------------------------------------------------------------- Key: HHH-5846 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5846 Project: Hibernate Core Issue Type: Bug Components: query-criteria Affects Versions: 3.6.1 Environment: hibernate3.6.0-Final PostgreSQL 8.4.6 on x86_64-pc-linux-gnu Reporter: Ryan Zhang Priority: Critical when I use setFirstResult and setMaxResults methods in my DAO, I always get the following exception: <code> WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42601 ERROR: org.hibernate.util.JDBCExceptionReporter - ERROR: syntax error at or near "$1" Position: 12 org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select this_.id as id9_3_, this_.createdby_id as createdby13_9_3_, this_.creation_date as creation2_9_3_, this_.description as descript3_9_3_, this_.modification_date as modifica4_9_3_, this_.modifiedby_id as modifiedby14_9_3_, this_.version as version9_3_, this_.corporation_id as corpora15_9_3_, this_.e_mail as e6_9_3_, this_.enabled as enabled9_3_, this_.first_name as first8_9_3_, this_.language as language9_3_, this_.last_name as last10_9_3_, this_.name as name9_3_, this_.password as password9_3_, this_.primary_warehouse_id as primary16_9_3_, this_.secondary_warehouse_id as secondary17_9_3_, corporatio2_.id as id7_0_, corporatio2_.createdby_id as createdby8_7_0_, corporatio2_.creation_date as creation2_7_0_, corporatio2_.description as descript3_7_0_, corporatio2_.modification_date as modifica4_7_0_, corporatio2_.modifiedby_id as modifiedby9_7_0_, corporatio2_.version as version7_0_, corporatio2_.code as code7_0_, corporatio2_.name as name7_0_, abswarehou3_.id as id12_1_, abswarehou3_.createdby_id as createdby23_12_1_, abswarehou3_.creation_date as creation3_12_1_, abswarehou3_.description as descript4_12_1_, abswarehou3_.modification_date as modifica5_12_1_, abswarehou3_.modifiedby_id as modifiedby24_12_1_, abswarehou3_.version as version12_1_, abswarehou3_.code as code12_1_, abswarehou3_.name as name12_1_, abswarehou3_.address1 as address9_12_1_, abswarehou3_.address2 as address10_12_1_, abswarehou3_.city as city12_1_, abswarehou3_.country as country12_1_, abswarehou3_.postal_code as postal13_12_1_, abswarehou3_.province as province12_1_, abswarehou3_.color as color12_1_, abswarehou3_.corporation_id as corpora25_12_1_, abswarehou3_.default_production_consumption_loc_id as default26_12_1_, abswarehou3_.default_production_output_loc_id as default27_12_1_, abswarehou3_.default_receipt_loc_id as default28_12_1_, abswarehou3_.default_shipment_loc_id as default29_12_1_, abswarehou3_.input_blocked as input16_12_1_, abswarehou3_.local_freight_zone_id as local30_12_1_, abswarehou3_.output_blocked as output17_12_1_, abswarehou3_.shipping_lead_time as shipping18_12_1_, abswarehou3_.zone_to_id as zone31_12_1_, abswarehou3_.contact as contact12_1_, abswarehou3_.first_phone_number as first20_12_1_, abswarehou3_.in_transit_warehouse_id as in32_12_1_, abswarehou3_.is_job_lot as is21_12_1_, abswarehou3_.job_lot_warehouse_id as job33_12_1_, abswarehou3_.on_water_warehouse_id as on34_12_1_, abswarehou3_.quarantine_warehouse_id as quarantine35_12_1_, abswarehou3_.second_phone_number as second22_12_1_, abswarehou3_.discriminator as discrimi1_12_1_, abswarehou4_.id as id12_2_, abswarehou4_.createdby_id as createdby23_12_2_, abswarehou4_.creation_date as creation3_12_2_, abswarehou4_.description as descript4_12_2_, abswarehou4_.modification_date as modifica5_12_2_, abswarehou4_.modifiedby_id as modifiedby24_12_2_, abswarehou4_.version as version12_2_, abswarehou4_.code as code12_2_, abswarehou4_.name as name12_2_, abswarehou4_.address1 as address9_12_2_, abswarehou4_.address2 as address10_12_2_, abswarehou4_.city as city12_2_, abswarehou4_.country as country12_2_, abswarehou4_.postal_code as postal13_12_2_, abswarehou4_.province as province12_2_, abswarehou4_.color as color12_2_, abswarehou4_.corporation_id as corpora25_12_2_, abswarehou4_.default_production_consumption_loc_id as default26_12_2_, abswarehou4_.default_production_output_loc_id as default27_12_2_, abswarehou4_.default_receipt_loc_id as default28_12_2_, abswarehou4_.default_shipment_loc_id as default29_12_2_, abswarehou4_.input_blocked as input16_12_2_, abswarehou4_.local_freight_zone_id as local30_12_2_, abswarehou4_.output_blocked as output17_12_2_, abswarehou4_.shipping_lead_time as shipping18_12_2_, abswarehou4_.zone_to_id as zone31_12_2_, abswarehou4_.contact as contact12_2_, abswarehou4_.first_phone_number as first20_12_2_, abswarehou4_.in_transit_warehouse_id as in32_12_2_, abswarehou4_.is_job_lot as is21_12_2_, abswarehou4_.job_lot_warehouse_id as job33_12_2_, abswarehou4_.on_water_warehouse_id as on34_12_2_, abswarehou4_.quarantine_warehouse_id as quarantine35_12_2_, abswarehou4_.second_phone_number as second22_12_2_, abswarehou4_.discriminator as discrimi1_12_2_ from users this_ left outer join corporation corporatio2_ on this_.corporation_id=corporatio2_.id left outer join warehouse abswarehou3_ on this_.primary_warehouse_id=abswarehou3_.id left outer join warehouse abswarehou4_ on this_.secondary_warehouse_id=abswarehou4_.id order by this_.name asc]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629) at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.convertHibernateAccessException(AbstractSessionFactoryBean.java:303) at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.translateExceptionIfPossible(AbstractSessionFactoryBean.java:282) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621) at com.abc.core.security.dao.UserDAO$$EnhancerByCGLIB$$8ac39f0.findByFilters(<generated>) at com.abc.core.security.service.UserManagerImpl.findUsers(UserManagerImpl.java:43) at com.abc.core.security.service.UserManagerImpl$$FastClassByCGLIB$$c604a90e.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621) at com.abc.core.security.service.UserManagerImpl$$EnhancerByCGLIB$$55fa90fa.findUsers(<generated>) at com.abc.web.security.controller.UserPagedListController.json(UserPagedListController.java:35) 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:597) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549) at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.abc.web.filter.ThreadLocalInitFilter.doFilterInternal(ThreadLocalInitFilter.java:152) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:368) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:99) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:169) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:662) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2536) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) at com.abc.core.entity.dao.DefaultEntityDAO.findByCriteria(DefaultEntityDAO.java:206) at com.abc.core.entity.dao.DefaultEntityDAO.findByCriterions(DefaultEntityDAO.java:222) at com.abc.core.entity.dao.DefaultEntityDAO.findByFilters(DefaultEntityDAO.java:231) at com.abc.core.entity.dao.DefaultEntityDAO$$FastClassByCGLIB$$d0fddf42.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155) ... 84 more Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 12 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) at org.hibernate.loader.Loader.doQuery(Loader.java:802) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) at org.hibernate.loader.Loader.doList(Loader.java:2533) ... 97 more </code>