Cohort Pathway executions fail

[t-abdul-basser]

Expected behavior

Cohort pathway execution succeed.

Actual behavior

Cohort Pathway execution fail

Steps to reproduce behavior

1. Create Cohort pathway design.
2. Execute

Error Message [From UI]

java.util.concurrent.CompletionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE #Codesets (
codeset_id int NOT NULL,
concept_id bigint NOT NULL
)
; INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 9 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
select concept_id from ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT where concept_id in (443800,4302555,4090070,4202828,442274,196168,4275882,4171394,40479853)
UNION select c.concept_id
from ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT c
join ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
and ca.ancestor_concept_id in (443800,4302555,4090070,4202828,442274,196168,4275882,4171394,40479853)
and c.invalid_reason is null
) I
LEFT JOIN
(
select concept_id from ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT where concept_id in (606406,606439,606050,602882,4211822,4070773,3170126,4034017,4034018,45765512,4261326,4219015,40318194,4230947,4312727)
) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C UNION ALL
SELECT 10 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
select concept_id from ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT where concept_id in (4125593,441092,4061424,4139126,4015590,3011536,43054893,43021054,37017322,441085,192380,4210896,195877,4061522,437937,4030415,197031,4061672,43021951,43021952,3045823,432969,4217975,4038942,4150948,4148890,4094910,4061785,4060237,4299535,4059982,4061686,4059984,4061411,4059981,4133029,442594,440466,4061423,4214930,3040000,196484,4042412,198212,4129023,4218813,432375,441919,4307820,4059985,4239301,4041878,40519557,40524802,40524805)
UNION select c.concept_id
from ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT c
join ohdsi_cumc_deid_2021q4r1.dbo.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
and ca.ancestor_concept_id in (4125593,441092,4061424,4139126,4015590,3011536,43054893,43021054,37017322,441085,192380,4210896,195877,4061522,437937,4030415,197031,4061672,43021951,43021952,3045823,432969,4217975,4038942,4150948,4148890,4094910,4061785,4060237,4299535,4059982,4061686,4059984,4061411,4059981,4133029,442594,440466,4061423,4214930,3040000,196484,4042412,198212,4129023,4218813,432375,441919,4307820,4059985,4239301,4041878,40519557,40524802,40524805)
and c.invalid_reason is null
) I
) C UNION ALL
SELECT 11 as codeset_id, c.concept_id FROM (select distinct I.concept_i

Note: Full error message from logs available.

Supporting materials:

Version: Atlas 2.14.1
Environment: Linux/Kubernetes (Kind Cluster)

[chrisknoll]

That SQL looks like it's from a cohort definition generation, but there's several in a pathway analysis so hard to tell which.

The SQL error is usually output directly after the SQL block, so, while you don't' have to provide the entire log, could you go into the log and find this SQL error message, and find the error immediately after the SQL output: something like 'permission denied on table' or 'syntax error at X. Let me know what the error is and we can troubleshoot.

[t-abdul-basser]

Thanks @chrisknoll. From memory Its a temp table-related error message. I will digs the relevant snippets out the logs and post here.