Temp schema missing when using cohort demographics

[chrisknoll]

Getting this error on databricks note the cohort table is not being qualified with any temp schema:

USING DELTA
AS
SELECT
CAST(ethnicity_concept_id AS BIGINT) * 1000 + 5 AS covariate_id,
 cohort_definition_id,
 COUNT(*) AS sum_value
FROM
temp_cohort_dhgd0361 cohort
INNER JOIN optum_extended_dod.cdm_optum_extended_dod_v3492.person
 ON cohort.subject_id = person.person_id
WHERE ethnicity_concept_id IN (
 SELECT concept_id
 FROM optum_extended_dod.cdm_optum_extended_dod_v3492.concept
 WHERE LOWER(concept_class_id) = 'ethnicity'
 )
 AND cohort.cohort_definition_id IN (21214)
GROUP BY cohort_definition_id,
 ethnicity_concept_id]; nested exception is java.sql.SQLException: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 42P01, Query: CREATE TAB***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running query: [TABLE_OR_VIEW_NOT_FOUND] org.apache.spark.sql.catalyst.ExtendedAnalysisException: [TABLE_OR_VIEW_NOT_FOUND] The table or view `temp_cohort_dhgd0361` cannot be found. Verify the spelling and correctness of the schema and catalog.

Note, did not see this on postgres, but postgres has 'true temp tables', so this query needs to be executed in a way that can handle temp schemas.

@oleg-odysseus , please look into this for a 2.15 hot fix. Thank you!

[oleg-odysseus]

Hi @chrisknoll , I was able to reproduce the same issue on postgres and figured out from the git history that one of my commits was somehow missed in the 2.15 release (created a PR with this cherry-picked commit https://github.com/OHDSI/WebAPI/pull/2458/files ). This commit had 2 places with the same issue fixed - in CC and in Cohort. It is the Cohort part of the fix was missing in master. Best Regards.

[chrisknoll]

I see, maybe my question then applies to both the CC fix and the Cohort Demographics fix: should the tempSchema qualfier be put into the sqlTranslate phase of the query building and not in the 'paramaterizatin' part where we do key-replacements in the query? Ie; shouldn't translateSql handle temp table transformations and not sqlRender (where name/value pairs are handled)