Closed SahaLinaPrueger closed 1 year ago
SahaLinaPrueger
commented
1 year ago Please note the additional information. I added a connecting error there. Should be fixed automatically with the fixing of the main error, I guess. The connecting error just occurs because the selected filter is 'saved# until the next opening of the directory.
leventegal-she
commented
1 year ago Hibernate generates an invalid query:
select count(distinct case0_.id) as col_0_0_ from cases case0_
left outer join Person person1_ on case0_.person_id=person1_.id
left outer join Location location2_ on person1_.address_id=location2_.id
left outer join users user3_ on case0_.reportingUser_id=user3_.id
left outer join Facility facility4_ on case0_.healthFacility_id=facility4_.id
left outer join sormastosormasorigininfo sormastoso5_ on case0_.sormasToSormasOriginInfo_id=sormastoso5_.id
left outer join Hospitalization hospitaliz6_ on case0_.hospitalization_id=hospitaliz6_.id
left outer join previoushospitalization previousho7_ on hospitaliz6_.id=previousho7_.hospitalization_id
left outer join ClinicalCourse clinicalco8_ on case0_.clinicalCourse_id=clinicalco8_.id
left outer join Symptoms symptoms9_ on case0_.symptoms_id=symptoms9_.id
left outer join Therapy therapy10_ on case0_.therapy_id=therapy10_.id
left outer join HealthConditions healthcond11_ on case0_.healthConditions_id=healthcond11_.id
left outer join MaternalHistory maternalhi12_ on case0_.maternalHistory_id=maternalhi12_.id
left outer join PortHealthInfo porthealth13_ on case0_.portHealthInfo_id=porthealth13_.id
left outer join sormastosormasorigininfo sormastoso14_ on case0_.sormasToSormasOriginInfo_id=sormastoso14_.id
left outer join sormastosormasshareinfo sormastoso15_ on case0_.id=sormastoso15_.caze_id
left outer join EpiData epidata16_ on case0_.epiData_id=epidata16_.id
left outer join exposures exposures17_ on epidata16_.id=exposures17_.epiData_id
left outer join Location location18_ on exposures17_.location_id=location18_.id
left outer join activityascase activities19_ on epidata16_.id=activities19_.epiData_id
left outer join Location location20_ on activities19_.location_id=location20_.id
left outer join samples samples21_ on case0_.id=samples21_.associatedCase_id
left outer join PathogenTest pathogente22_ on samples21_.id=pathogente22_.sample_id
left outer join Person person23_ on case0_.person_id=person23_.id
left outer join Location location24_ on person23_.address_id=location24_.id
left outer join Visit visits25_ on case0_.id=visits25_.caze_id
left outer join surveillancereports surveillan26_ on case0_.id=surveillan26_.caze_id
left outer join Person person27_ on case0_.person_id=person27_.id
left outer join immunization immunizati28_ on person27_.id=immunizati28_.person_id
Vaccination vaccinatio29_ left outer join immunization immunizati32_ on vaccinatio29_.immunization_id=immunizati32_.id
where (case0_.archived=? or case0_.archived is null) and case0_.deleted=? and (case0_.sormasToSormasOriginInfo_id is null or sormastoso5_.ownershipHandedOver=?) and not (exists (select sormastoso33_.id from sormastosormasshareinfo sormastoso33_ inner join (sharerequestinfo_shareinfo requests34_ inner join sharerequestinfo sharereque35_ on requests34_.sharerequestinfo_id=sharereque35_.id) on sormastoso33_.id=requests34_.shareinfo_id and (sharereque35_.creationDate=(select max(sharereque36_.creationDate) from sharerequestinfo sharereque36_ left outer join sharerequestinfo_shareinfo shares37_ on sharereque36_.id=shares37_.sharerequestinfo_id left outer join sormastosormasshareinfo sormastoso38_ on shares37_.shareinfo_id=sormastoso38_.id where sormastoso38_.id=sormastoso33_.id)) where sormastoso33_.caze_id=case0_.id and sormastoso33_.ownershipHandedOver=? and sharereque35_.requestStatus=?)) and (exists (select case40_.id from externalshareinfo externalsh39_ left outer join cases case40_ on externalsh39_.caze_id=case40_.id where case40_.id=case0_.id group by case40_.id having case0_.changeDate>max(externalsh39_.creationDate) and (case0_.changeDate is not null) or symptoms9_.changeDate>max(externalsh39_.creationDate) and (symptoms9_.changeDate is not null) or hospitaliz6_.changeDate>max(externalsh39_.creationDate) and (hospitaliz6_.changeDate is not null) or previousho7_.changeDate>max(externalsh39_.creationDate) and (previousho7_.changeDate is not null) or therapy10_.changeDate>max(externalsh39_.creationDate) and (therapy10_.changeDate is not null) or clinicalco8_.changeDate>max(externalsh39_.creationDate) and (clinicalco8_.changeDate is not null) or healthcond11_.changeDate>max(externalsh39_.creationDate) and (healthcond11_.changeDate is not null) or maternalhi12_.changeDate>max(externalsh39_.creationDate) and (maternalhi12_.changeDate is not null) or porthealth13_.changeDate>max(externalsh39_.creationDate) and (porthealth13_.changeDate is not null) or sormastoso14_.changeDate>max(externalsh39_.creationDate) and (sormastoso14_.changeDate is not null) or sormastoso15_.changeDate>max(externalsh39_.creationDate) and (sormastoso15_.changeDate is not null) or epidata16_.changeDate>max(externalsh39_.creationDate) and (epidata16_.changeDate is not null) or exposures17_.changeDate>max(externalsh39_.creationDate) and (exposures17_.changeDate is not null) or location18_.changeDate>max(externalsh39_.creationDate) and (location18_.changeDate is not null) or activities19_.changeDate>max(externalsh39_.creationDate) and (activities19_.changeDate is not null) or location20_.changeDate>max(externalsh39_.creationDate) and (location20_.changeDate is not null) or samples21_.changeDate>max(externalsh39_.creationDate) and (samples21_.changeDate is not null) or pathogente22_.changeDate>max(externalsh39_.creationDate) and (pathogente22_.changeDate is not null) or person23_.changeDate>max(externalsh39_.creationDate) and (person23_.changeDate is not null) or location24_.changeDate>max(externalsh39_.creationDate) and (location24_.changeDate is not null) or visits25_.changeDate>max(externalsh39_.creationDate) and (visits25_.changeDate is not null) or surveillan26_.changeDate>max(externalsh39_.creationDate) and (surveillan26_.changeDate is not null) or vaccinatio29_.changeDate>max(externalsh39_.creationDate) and (vaccinatio29_.changeDate is not null) or immunizati32_.changeDate>max(externalsh39_.creationDate) and (immunizati32_.changeDate is not null))) and case0_.dontShareWithReportingTool=false;Code parts for building the query:
ExternalShareInfoService::buildLatestSurvToolShareDateFilter
CaseService::createChangeDateFilter
Unit test for this functionality not failing:
CaseFacadeEjbTest::testCaseCriteriaChangedSinceLastShareWithReportingTool
abrudanancuta
commented
1 year ago Validated together with @leventegal-she on his local environment. Tested Cases and Events sent to Survnet:
Bug Description
Checking the checkbox 'only cases changed since last shared with reporting tool' and apply the filter leads in an error message. This filter is very important for the health departments because of reporting requirements.
Steps to Reproduce
1.check the checkbox Only cases changed since last shared with reporting tool 2.click the button Apply Filters
Expected Behavior
Filter should work and cases should be shown.
Screenshots
System Details
Additional Information
log.txt
Relevant StackTrace
``` Caused by: org.omg.CORBA.TRANSACTION_ROLLEDBACK: ----------BEGIN server-side stack trace----------org.omg.CORBA.TRANSACTION_ROLLEDBACK: vmcid: 0x2000 minor code: 1806 completed: Maybe at org.glassfish.enterprise.iiop.impl.POAProtocolMgr.mapException(POAProtocolMgr.java:356) ... at com.sun.corba.ee.impl.presentation.rmi.codegen.CodegenStubBase.invoke(CodegenStubBase.java:226) at de.symeda.sormas.api.caze.__CaseFacade_Remote_DynamicStub.count(de/symeda/sormas/api/caze/__CaseFacade_Remote_DynamicStub.java) at de.symeda.sormas.api.caze._CaseFacade_Wrapper.count(de/symeda/sormas/api/caze/_CaseFacade_Wrapper.java) at de.symeda.sormas.ui.caze.AbstractCaseGrid.lambda$setLazyDataProvider$b88b9d74$1(AbstractCaseGrid.java:306) at com.vaadin.data.provider.CallbackDataProvider.sizeInBackEnd(CallbackDataProvider.java:142) at com.vaadin.data.provider.AbstractBackEndDataProvider.size(AbstractBackEndDataProvider.java:66) at com.vaadin.data.provider.DataProviderWrapper.size(DataProviderWrapper.java:85) at de.symeda.sormas.ui.utils.FilteredGrid.getItemCount(FilteredGrid.java:132) at de.symeda.sormas.ui.caze.CasesView.updateStatusButtons(CasesView.java:858) at de.symeda.sormas.ui.caze.CasesView.lambda$new$7a32c94f$2(CasesView.java:197) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:706) at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:399) at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:363) at com.vaadin.data.provider.AbstractDataProvider.fireEvent(AbstractDataProvider.java:90) at com.vaadin.data.provider.AbstractDataProvider.refreshAll(AbstractDataProvider.java:51) at com.vaadin.data.provider.DataProviderWrapper.refreshAll(DataProviderWrapper.java:64) at de.symeda.sormas.ui.caze.AbstractCaseGrid.reload(AbstractCaseGrid.java:292) at de.symeda.sormas.ui.caze.CasesView.lambda$createFilterBar$79546433$2(CasesView.java:571) ... Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626) at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1665) at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:111) at de.symeda.sormas.backend.caze.CaseFacadeEjb.count(CaseFacadeEjb.java:567) at de.symeda.sormas.backend.caze.CaseFacadeEjb.count(CaseFacadeEjb.java:536) at de.symeda.sormas.backend.caze.CaseFacadeEjb.count(CaseFacadeEjb.java:355) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ... at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:205) ... 129 more Caused by: org.hibernate.exception.DataException: could not execute query at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.loader.Loader.doList(Loader.java:2871) at org.hibernate.loader.Loader.doList(Loader.java:2850) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) at org.hibernate.loader.Loader.list(Loader.java:2677) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1454) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) ... 191 more Caused by: org.postgresql.util.PSQLException: Der Spaltenindex 3 ist außerhalb des gültigen Bereichs. Anzahl Spalten: 2. at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:70) at org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:129) at org.postgresql.jdbc.PgPreparedStatement.bindLiteral(PgPreparedStatement.java:1060) at org.postgresql.jdbc.PgPreparedStatement.setBoolean(PgPreparedStatement.java:291) at jdk.internal.reflect.GeneratedMethodAccessor1061.invoke(Unknown Source) ```Connecting error: Bug description: Checking the checkbox only cases changed since last shared with reporting tool and apply the filter leads in an error message. If you leave the case directory afterwards without unchecking the box only cases changed since last shared with reporting tool leads in an error message if you want to open the case directory again.