Issue description

In rare cases some of our users observed the following exception when running the pre-installed Audit report (located under /public/audit/reports/Audit_Report) when running JasperReports Server on Oracle database (note the 'ORA-01830: date format picture ends before converting entire input string' closer to the end of the stack trace):

------------------------

com.jaspersoft.jasperserver.api.JSException: exception getting data strategy Arguments: at com.jaspersoft.ji.adhoc.datasource.AdhocDataSourceService.setReportParameterValues(AdhocDataSourceService.java:172) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1859) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1205) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1087) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:982) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: exception getting dataset from cache at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:147) at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:75) at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getWorkingDataSet(CommonDomainDataStrategy.java:1286) at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getRuntimeDataSet(CommonDomainDataStrategy.java:1641) at com.jaspersoft.ji.adhoc.strategy.DomainDataStrategy.getRuntimeDataSet(DomainDataStrategy.java:259) at com.jaspersoft.ji.adhoc.strategy.FlattenedWorkingDataSetStrategy.getRuntimeDataSet(FlattenedWorkingDataSetStrategy.java:86) at com.jaspersoft.ji.adhoc.datasource.AdhocDataSourceService.setReportParameterValues(AdhocDataSourceService.java:145) ... 7 more Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching from cache at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:283) at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:110) ... 13 more Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching data at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:253) at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:279) ... 14 more Caused by: com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select * from ( select '/' as "groovy_ktenant3getTenantFolder", "jiauditevent"."EVENT_DATE" as "jiauditevent_EVENT_DATE", "jiauditevent"."EVENT_TYPE" as "jiauditevent_EVENT_TYPE", "jiauditevent"."REQUEST_TYPE" as "jiauditevent_REQUEST_TYPE", "jiauditevent"."RESOURCE_TYPE" as "jiauditevent_RESOURCE_TYPE", "jiauditevent"."RESOURCE_URI" as "jiauditevent_RESOURCE_URI", "jiauditevent"."TENANT_ID" as "jiauditevent_TENANT_ID", "jiauditevent"."USERNAME" as "jiauditevent_USERNAME", trunc("jiauditevent"."EVENT_DATE") as "jiauditevent_au_date", to_date(("jiauditevent"."EVENT_DATE" - trunc("jiauditevent"."EVENT_DATE")) * 86400, 'SSSSS') as "jiauditevent_au_time", case when ("jiauditevent"."RESOURCE_URI" like '/public/%') then ("jiauditevent"."RESOURCE_URI") else (case when (("jiauditevent"."RESOURCE_URI" is null)) then (null) else ('/' || substr("jiauditevent"."RESOURCE_URI", (length('/') + 1))) end) end as "jiauditevent_orguri", case when (("jiauditevent"."RESOURCE_URI" is null)) then (null) else ('/' || substr("jiauditevent"."RESOURCE_URI", (length('/') + 1))) end as "jiauditevent_orguri1", "jiauditeventproperty"."PROPERTY_TYPE" as "jiauditeventproperty_PROPERTY_", "jiauditeventproperty"."VALUE" as "jiauditeventproperty_VALUE" from "JIAUDITEVENT" "jiauditevent" left outer join "JIAUDITEVENTPROPERTY" "jiauditeventproperty" on ("jiauditevent"."ID" = "jiauditeventproperty"."AUDIT_EVENT_ID") left outer join "JITENANT" "jitenant" on ("jiauditevent"."TENANT_ID" = "jitenant"."TENANTID") where "jiauditevent"."EVENT_TYPE" in ('runReport') and "jiauditevent"."RESOURCE_TYPE" in ('ReportUnit') and (trunc("jiauditevent"."EVENT_DATE") between DATE '2016-12-21' and DATE '2016-12-21') and (("jiauditevent"."RESOURCE_URI" is null) or "jiauditevent"."RESOURCE_URI" like '/' || '%' or "jiauditevent"."RESOURCE_URI" like '/public/%') and (("jiauditevent"."RESOURCE_URI" is null) or not (case when (("jiauditevent"."RESOURCE_URI" is null)) then (null) else ('/' || substr("jiauditevent"."RESOURCE_URI", (length('/') + 1))) end like '/temp/%')) and (("jiauditevent"."TENANT_ID" is null) or "jitenant"."TENANTURI" like '/' || '%') order by "jiauditevent_au_date" desc, "jiauditevent_au_time" desc ) where ROWNUM <= 1000001 at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:462) at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:217) ... 15 more Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: null. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.run(JSControlledJdbcQueryExecuter.java:157) ... 1 more Caused by: java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:79) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 3 more

------------------------

Solution

While we did not identify the exact cause of this issue as it appears to be environment-specific, the problem can be resolved from JasperReports Server's side by by adding the 'round' function for Oracle SQL generator in ../WEB-INF/applicationContext-semanticLayer.xml file: