dhis2-devs team mailing list archive

Thread
Date
[Bug 1231819] Re: exception when find person in lost to follow-up

To: dhis2-devs@xxxxxxxxxxxxxxxxxxx
From: Nguyen Kim Lai <lai.hispvietnam@xxxxxxxxx>
Date: Wed, 02 Oct 2013 07:48:41 -0000
Reply-to: Bug 1231819 <1231819@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Just try to combined trunk code with database dev, still have this
exception

-- 
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.
https://bugs.launchpad.net/bugs/1231819

Title:
  exception when find person in lost to follow-up

Status in DHIS 2:
  New

Bug description:
  I combine trunk version with database 2.11(downloaded from
  www.dhis2.org), system throws exception when find person in lost to
  follow-up category:

   Exception (org.springframework.jdbc.BadSqlGrammarException): StatementCallback; bad SQL grammar [select count(*) from ( select distinct p.patientid, p.name, p.gender, p.phonenumber , psi.programstageinstanceid as programstageinstanceid, pgs.name as programstagename, psi.duedate as duedate ,pgi.followup from patient p inner join programinstance pgi on (pgi.patientid=p.patientid) inner join programstageinstance psi on (psi.programinstanceid=pgi.programinstanceid) inner join programstage pgs on (pgs.programstageid=psi.programstageid) where pgi.patientid=p.patientid and pgi.programid=2006123 and pgi.status=0 and ( ( psi.executiondate is null and psi.duedate>='1913-09-26' and psi.duedate<='2113-09-26' and psi.status is null and (DATE(now()) - DATE(psi.duedate) > 0) and p.organisationunitid=559)) and pgi.status=0 GROUP BY p.patientid, p.name, p.gender, p.phonenumber ,pgi.followup ,psi.programstageinstanceid, pgs.name ORDER BY pgi.followup desc, p.patientid, p.name, duedate asc ) as searchresult]; nested exception is org.postgresql.util.PSQLException: ERROR: column "psi.duedate" must appear in the GROUP BY clause or be used in an aggregate function Position: 173
  org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
  org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
  org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
  org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
  org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
  org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464)
  org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472)
  org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477)
  org.hisp.dhis.patient.hibernate.HibernatePatientStore.countSearch(HibernatePatientStore.java:452)
  sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2)
  sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  java.lang.reflect.Method.invoke(Method.java:601)
  org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
  org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
  org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
  org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
  org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
  org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
  org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  $Proxy103.countSearch(Unknown source)
  org.hisp.dhis.patient.DefaultPatientService.countSearchPatients(DefaultPatientService.java:577)
  sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2)
  sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  java.lang.reflect.Method.invoke(Method.java:601)
  org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
  org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
  org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
  org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
  org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
  org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
  org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
  org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  $Proxy43.countSearchPatients(Unknown source)
  org.hisp.dhis.caseentry.action.caseentry.GetDataRecordsAction.execute(GetDataRecordsAction.java:248)
  sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2)
  sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  java.lang.reflect.Method.invoke(Method.java:601)
  com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)
  com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252)
  org.hisp.dhis.webportal.interceptor.XWorkPortalUserInterceptor.intercept(XWorkPortalUserInterceptor.java:88)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.webportal.interceptor.XWorkPortalModuleInterceptor.intercept(XWorkPortalModuleInterceptor.java:91)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.webportal.interceptor.XWorkPortalMenuInterceptor.intercept(XWorkPortalMenuInterceptor.java:97)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.webportal.interceptor.XWorkPortalParamsInterceptor.intercept(XWorkPortalParamsInterceptor.java:119)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.interceptor.ContextInterceptor.intercept(ContextInterceptor.java:83)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.interceptor.UserSettingInterceptor.intercept(UserSettingInterceptor.java:81)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.security.intercept.LoginInterceptor.intercept(LoginInterceptor.java:85)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.security.intercept.XWorkSecurityInterceptor.intercept(XWorkSecurityInterceptor.java:113)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.interceptor.SystemSettingInterceptor.intercept(SystemSettingInterceptor.java:105)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:136)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249)
  com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.hisp.dhis.interceptor.ExceptionInterceptor.intercept(ExceptionInterceptor.java:110)
  com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
  org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
  org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:546)
  org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
  org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
  org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1212)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
  org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
  org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.hisp.dhis.security.filter.CustomAuthenticationFilter.doFilter(CustomAuthenticationFilter.java:55)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.hisp.dhis.security.filter.AutomaticAccessFilter.doFilter(AutomaticAccessFilter.java:113)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
  org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
  org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
  org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
  org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
  org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
  org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1212)
  org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:152)
  org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
  org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1212)
  org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
  org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
  org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1212)
  org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:399)
  org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
  org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
  org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450)
  org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
  org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
  org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  org.mortbay.jetty.Server.handle(Server.java:326)
  org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
  org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945)
  org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
  org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
  org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
  org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
  org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1231819/+subscriptions
References

[Bug 1231819] [NEW] exception when find person in lost to follow-up
From: Nguyen Kim Lai, 2013-09-27