PLP/PLE ERROR: org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"

[KSadomtsev]

WebApi 2.8.1

Steps:
1)Run any of PLP/PLE analysis on postgresql

Actual:
It should be replaced @results_database_schema by real schema, but it doesn't

Error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"
Position: 13

SQL:
delete from @results_database_schema.cohort_censor_stats where cohort_definition_id = 157

[anthonysena]

@ssuvorov-fls - it would appear that the cohort SQL is adding some extra code to compute inclusion stats which is not required for PLE/PLP. Could you share the design json that exhibited this problem so I can take a look? I'd like to run this through Hydra and inspect the resulting package to see where things may be off.

[ssuvorov-fls]

@anthonysena
estimation.json.zip
Other analyses also throws this exception

[anthonysena]

@ssuvorov-fls - I'm having a bit of trouble reproducing this issue. To test this, I use the instructions from @schuemie posted on https://ohdsi.github.io/Hydra/articles/HydratingPackages.html.

First challenge: the JSON file lacks a "packageName" attribute in the JSON that breaks our ability to use this approach. So, that is something we need to fix (I'll note that in a separate issue). Once I manually added that element to the estimation json, I was able to hydrate a new package without issue:

hydraOutput.zip

I inspected the ./inst/sql/sql_server/Dehydration.sql to see where it either contained a reference to @results_database_schema OR cohort_censor_stats. I did not find any references to cohort_censor_stats in these scripts. I did find references to @results_database_schema but these references should disappear when running through SqlRender based on the {0 != 0}?{...} conditional block (line 180 from that code block - see below). So, I'm not sure of the origin of this issue as it does not appear that Hydra is producing anything incorrectly or even where the original code of delete from @results_database_schema.cohort_censor_stats where cohort_definition_id = 157 would appear in the code as I found 0 references to cohort_censor_stats in the resulting package.

You are using Hydra for this, correct?

{0 != 0}?{
-- Find the event that is the 'best match' per person.  
-- the 'best match' is defined as the event that satisfies the most inclusion rules.
-- ties are solved by choosing the event that matches the earliest inclusion rule, and then earliest.

select q.person_id, q.event_id
into #best_events
from #qualified_events Q
join (
	SELECT R.person_id, R.event_id, ROW_NUMBER() OVER (PARTITION BY R.person_id ORDER BY R.rule_count DESC,R.min_rule_id ASC, R.start_date ASC) AS rank_value
	FROM (
		SELECT Q.person_id, Q.event_id, COALESCE(COUNT(DISTINCT I.inclusion_rule_id), 0) AS rule_count, COALESCE(MIN(I.inclusion_rule_id), 0) AS min_rule_id, Q.start_date
		FROM #qualified_events Q
		LEFT JOIN #inclusion_events I ON q.person_id = i.person_id AND q.event_id = i.event_id
		GROUP BY Q.person_id, Q.event_id, Q.start_date
	) R
) ranked on Q.person_id = ranked.person_id and Q.event_id = ranked.event_id
WHERE ranked.rank_value = 1
;

-- modes of generation: (the same tables store the results for the different modes, identified by the mode_id column)
-- 0: all events
-- 1: best event


-- BEGIN: Inclusion Impact Analysis - event
-- calculte matching group counts
delete from @results_database_schema.cohort_inclusion_result where cohort_definition_id = @target_cohort_id and mode_id = 0;
insert into @results_database_schema.cohort_inclusion_result (cohort_definition_id, inclusion_rule_mask, person_count, mode_id)
select @target_cohort_id as cohort_definition_id, inclusion_rule_mask, count_big(*) as person_count, 0 as mode_id
from
(
  select Q.person_id, Q.event_id, CAST(SUM(coalesce(POWER(cast(2 as bigint), I.inclusion_rule_id), 0)) AS bigint) as inclusion_rule_mask
  from #qualified_events Q
  LEFT JOIN #inclusion_events I on q.person_id = i.person_id and q.event_id = i.event_id
  GROUP BY Q.person_id, Q.event_id
) MG -- matching groups
group by inclusion_rule_mask
;

--- TRUNCATED FOR GITHUB
}

[chrisknoll]

Note on what is in circe 1.9.1:
the cohortCensoredStats is one that I think is a problem: I put it outside the generateStats clause because I figured that we'd want to know how many people were censored out of the cohort due to the 'study window' setting. I realize now that it makes it a dependency on results_dabase_schema even when we don't want to generate stats.

Therefore, I will adjust the query to not write ANY output to cohort_censor_stats when generateStats == false.

Great catch, @anthonysena , this won't impact you in 1.8.4 in your circe reference, but it will impact you when you update to circe 1.9. I'll fix this in 1.9.2.

[ssuvorov-fls]

@chrisknoll Hi, do you have any updates on this issue?

[anthonysena]

Hi @ssuvorov-fls - can you please use 1.9.2-SNAPSHOT of circe-be in the WebAPI pom.xml? We believe this will fix this issue.

[chrisknoll]

I've released 1.9.2 of circe, so if can link to the released dependency.