CacheDatasetException: exception getting dataset from cache

[eduardo.hssi]

Hello,

We are creating a report based on a domain. This report has dynamic parameters. When publishing the report and running with new parameters it fails to resolve the cache, generating an exception. Executing the query inside the domain, it returns the results successfully.

 

.jaspersoft.jasperserver.api.JSException: exception fetching from cache
Arguments:
        at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategyCacheListener.stateChanged(CommonDomainDataStrategyCacheListener.java:54)
        at com.jaspersoft.commons.datarator.EhCachedData.setState(EhCachedData.java:247)
        at com.jaspersoft.commons.datarator.EhCachedData.setThrowable(EhCachedData.java:268)
        at com.jaspersoft.commons.datarator.EhCachedData.fetchData(EhCachedData.java:219)
        at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getCachedData(BaseDataSetCacheImpl.java:311)
        at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:301)
        at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:108)
        at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:81)
        at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getWorkingDataSet(CommonDomainDataStrategy.java:1290)     
        at com.jaspersoft.ji.adhoc.strategy.CompiledDomainQuery.getWorkingDataSet(CompiledDomainQuery.java:109)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:140)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:79)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:57)
        at com.jaspersoft.ji.adhoc.strategy.StrategyAwareAdhocQueryExecuter.createDatasource(StrategyAwareAdhocQueryExecuter.java:82)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1263)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:732)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:585)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:414)
        at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:826)
        at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61)
        at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select "academic_main_external_id",
"academic_main_name",
"completed_progress_at",
"enrollment_status_name",
"last_access_at",
"topic_category",
"topic_name",
"total_access",
"user_external_id",
"user_name"
from (select
       am.tenant_id,
       am.ies_id as institution_id,
        e.id as enrollment_id,
        es.id as enrollment_status_id,
        es."name" as enrollment_status_name,
        u.external_id  as user_external_id,
       u.safea_user_id,
       r.safea_role,
        coalesce(u.social_name, u."name") as user_name,
        am.external_id  as academic_main_external_id,
        am.id as plataforma_academic_main_id,
        am."name" as academic_main_name,
       t.id as topic_id,
       t2.id as parent_topic_id,
        t.title || coalesce(' (' || t2.title || ')', '') as topic_name,
        t2.title as parent_topic_name,
        ct."name" as topic_category,
        p.created_at as completed_progress_at,
        count(evt.id) as total_access,
        max(evt.created_at) as last_access_at
from
        schema_abc.enrollment e
join schema_abc.role_course rc on
        e.role_course_id = rc.id
join schema_abc.enrollment_status es on
        e.enrollment_status_id = es.id
join schema_abc.role as r on
        rc.role_id = r.id
join schema_abc."user" u on
        e.user_id = u.id
join schema_abc.academic_main am on
        e.academic_main_id = am.id
join schema_abc."content" c on
        c.academic_main_id = am.id
join schema_abc.topic t on
        t.content_id = c.id
join schema_abc.topic_type tt on
        t.topic_type_id = tt.id
join schema_abc.category_type ct on
        tt.category_type_id = ct.id
left join schema_abc.topic t2 on
        t.parent_topic_id = t2.id
left join schema_abc.progress p on
        p.enrollment_id = e.id
        and p.topic_id = t.id
        and p.is_deleted = false
left join (
        select
                evt.id,
                evt.name,
                evt.role_alias,
                evt.topic_id,
                evt.safea_user_id,
                evt.created_at
        from
                schema_xyz.vw_plataforma_event as evt
) as evt
        on evt.safea_user_id = u.safea_user_id
        and evt.role_alias = r.safea_role
        and evt.name = 'TopicClick'
        and (evt.topic_id = t.id or evt.topic_id = t2.id)
where
        e.is_deleted = false
        and e.tenant_id = 42
        and am.id = 67972
        and r.is_student = true
        and am.is_deleted = false
        and u.is_deleted = false
        and ct."name"  <> 'ORGANIZATION'
group by e.id,
        es.id,
        es."name",
        u.external_id,
        u.social_name,
        u."name",
        am.external_id,
        am.id,
        am."name",
        t.title,
        t2.title,
        ct."name",
        p.created_at,
       u.safea_user_id,
       r.safea_role,
       t.id,
       t2.id,
       am.tenant_id,
      am.ies_id) "query"
where ("plataforma_academic_main_id" is null)
 limit 200001

        at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:439)
        at com.jaspersoft.commons.datarator.EhCachedData.fetchData(EhCachedData.java:181)
        ... 21 more
Caused by: java.lang.NullPointerException

2022-08-24T16:04:44,660 ERROR StrategyAwareAdhocQueryExecuter,Student access to the objects subreports #1:93 - Error while executing Ad Hoc query com.jaspersoft.jasperserver.api.CacheDatasetException: exception getting dataset from cache
        at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:145)
        at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:81)
        at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getWorkingDataSet(CommonDomainDataStrategy.java:1290)     
        at com.jaspersoft.ji.adhoc.strategy.CompiledDomainQuery.getWorkingDataSet(CompiledDomainQuery.java:109)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:140)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:79)
        at com.jaspersoft.ji.adhoc_engine.execution.AppQueryExecutor.executeQuery(AppQueryExecutor.java:57)
        at com.jaspersoft.ji.adhoc.strategy.StrategyAwareAdhocQueryExecuter.createDatasource(StrategyAwareAdhocQueryExecuter.java:82)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1263)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:732)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:585)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:414)
        at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:826)
        at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61)
        at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.jaspersoft.commons.dataset.DataSetException: Failed fetching dataset
        at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getCachedData(BaseDataSetCacheImpl.java:326)
        at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:301)
        at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:108)
        ... 18 more
Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching data
        at com.jaspersoft.commons.datarator.EhCachedData.fetchData(EhCachedData.java:220)
        at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getCachedData(BaseDataSetCacheImpl.java:311)