Achilles 1.6.3 not working if CDM "cohort" table is empty

[tropicalberto]

Expected behavior

Achilles working without errors

Actual behavior

• 2 Errors raised while doing the analysis (but Achilles ignores them and continues working):

`Executing SQL took 0.0552 secs
Analysis 1414 -- COMPLETE (0.064364 secs)
Analysis 1415 (Number of persons with payer plan period end < payer plan period start) -- START
  |==============================================================================================================================| 100%
Executing SQL took 0.0548 secs
Analysis 1415 -- COMPLETE (0.075058 secs)
Analysis 1700 (Number of records by cohort_concept_id) -- START
  |                                                                                                                              |   0%Analysis 1700 -- ERROR Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: relation "cdm5results.cohort" does not exist
  Position: 365
An error report has been created at  /home/alberto/errorReport.txt

Analysis 1701 (Number of records with cohort end date < cohort start date) -- START
  |                                                                                                                              |   0%Analysis 1701 -- ERROR Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: relation "cdm5results.cohort" does not exist
  Position: 312
An error report has been created at  /home/alberto/errorReport.txt`

• Then the analysis continues, achilles starts "merging scratch Achilles tables", raises an error, and stops working:

|==============================================================================================================================| 100%
Executing SQL took 0.062 secs
Analysis 2201 -- COMPLETE (0.071032 secs)
Merging scratch Achilles tables
  |===============================================================                                                               |  50%Error: Error executing SQL:
**org.postgresql.util.PSQLException: ERROR: relation "s_tmpach_1700" does not exist**
  Position: 51811
An error report has been created at  /home/alberto/errorReport.txt

• achilles() function stops without doing the analysis
• Connections to the database are not closed when the error is raised. May generate problems if achilles() function is called multiple times and max. no. of connection to the database is reached (temporary solution --> restart R)

Steps to reproduce behavior

• Run achilles() on a CDM with an empty cohort table in single-threaded mode in a Postgresql 9.5 database (for security reasons I'm not showing here the user/password/server/port of postgres):

connectionDetails <- createConnectionDetails(
  dbms="postgresql", 
  server="<>", 
  user="<>", 
  password="<>", 
  port="<>",
  schema="cdm5")

# Achilles in single-threaded mode 
achilles(connectionDetails, 
         cdmDatabaseSchema = "cdm5", 
         resultsDatabaseSchema="cdm5results",
         vocabDatabaseSchema = "cdm5",
         numThreads = 1,
         sourceName = "results__", 
         cdmVersion = "5.2.0",
         runHeel = TRUE,
         runCostAnalysis = FALSE)

• the achilles_results table is created in the results schema. The other achilles output tables are not created in the results schema

Temporary solution

• Create cohort table in the achilles results schema

CREATE TABLE cdm5results.cohort 
    ( 
	 cohort_definition_id			INTEGER			NOT NULL , 
     subject_id						INTEGER			NOT NULL ,
	 cohort_start_date				DATE			NOT NULL , 
     cohort_end_date				DATE			NOT NULL
    ) 
;

• run achilles
• some errors are raised, but achilles seems to work ("Done. Achilles Heel results can now be found in database_name.cdm5results"). This is the fraction of the output that shows the errors:

Executing SQL took 0.459 secs
  |==============================================================================================================================| 100%
Executing SQL took 0.252 secs
Done. Achilles results can now be found in schema caliber_dev.cdm5results
Warning: Temp table name '#s_tmpach_ch_condition' is too long. Temp table names should be shorter than 22 characters to prevent Oracle from crashing.
Warning: Temp table name '#s_tmpach_ch_condition' is too long. Temp table names should be shorter than 22 characters to prevent Oracle from crashing.
Warning: Temp table name '#s_tmpach_ch_condition' is too long. Temp table names should be shorter than 22 characters to prevent Oracle from crashing.
Warning: Temp table name '#s_tmpach_ch_condition' is too long. Temp table names should be shorter than 22 characters to prevent Oracle from crashing.
Executing Concept Hierarchy creation. This could take a while
Executing Concept Hierarchy creation. This could take a while
Connecting using PostgreSQL driver
  |==============================================================================================================================| 100%
Executing SQL took 3.04 secs
  |==============================================================================================================================| 100%
Executing SQL took 3.32 mins
  |==============================================================================================================================| 100%
Executing SQL took 1.14 mins
  |==============================================================| 100%
Executing SQL took 3.31 mins
  |==================================================================================================================================| 100%
Executing SQL took 2.45 mins
  |==============================================================================================================================| 100%
Executing SQL took 2.62 mins
  |==============================================================================================================================| 100%
Executing SQL took 31.9 secs
Connecting using PostgreSQL driver
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Connecting using PostgreSQL driver
  |                                                                                                                                  |   0%Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)
Drop Concept Hierarchy Scratch Table -- ERROR (Error: Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 22
An error report has been created at  /home/alberto/errorReport.txt
)

• (this error is the same as the one encountered by @lchonghua, see Achilles 1.6.1 - ERROR: syntax error at or near "." Position: 22 #323)

• After this, the tables cdm5results.achilles_analysis, cdm5results.achilles_results_dist, cdm5results.achilles_heel_results, cdm5results.achilles_results, cdm5results.concept_hierarchy, cdm5results.achilles_results_derived, are created

• If I subsequently run the function exportToJson with the previous output of Achilles, I get this error:

Error in rJava::.jcall(statement, "V", "close") : 
  org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
  Position: 32

Comments

I found the same issue in the OHDSI forums

[alondhe]

Thanks, @tropicalberto and Roger. This appears to be due to a faulty tryCatch block around seeing if the cohort table is present. I'll submit a PR later this week.