HERACLES SQL error on postgres ( WebAPI )

Implementers
1

I am getting an SQL error when trying to view reports on HERACLES using WebAPI. I think it is either

  1. the isNull function does not exist in postgres.
  2. the sql is trying to use the “+” operator to concatenate the string in the select statement.

I am not sure if this supposed to be corrected by spring/hibernate layers or if that is not working correctly. The sql error code is 42883 ( undefined function ), so I may be very off base with my assumption about the “+”.
I am not seeing any other SQL issues with other apps using WebAPI ( CIRCE, HERMES, ATLAS ). The tomcat logs are showing the connection as postgres and is able to

here is a small example of the failed query. ( having to type manually due to security restrictions )
org.springframework.jdbc.core.jdbcTemplate - - Executing SQL query [ select concept_hierarchy.concept_id, isNull( concept_hierarchy.so_concept_name, ‘NA’) + ‘||’ + isNull( …) concept_path

I am going to start looking at the code to try and debug, but wanted to see if someone else has seen this issue or if I am missing a configuration option somewhere.

Thanks,
Richard

2

I found another example that is coming from webapi.service.CohortResultsService . It has an error message that is resolving better. “Operator does not exist” . This query is using the “+”, but I do not see an isNull.

Looking through the queries that fail and the ones that pass, the “+” seems to be the one thing in common. isNull occurs only when paired with “+”, but not for all the occurrences.

3

I manually modified the resources/cohortresults/sql/condition/sqlConditionTreemap.sql to help debug the issues. I changed the isNull to coalesce and the + ‘||’ + to just || . This got the query to run without error.

I am not sure how to address this to fix the issue, since this would break other sql implementations. Although coalesce should work with all.

4

I found an issue with resources/conditionera/sqlConditionEraTreemap.sql also. The ROUND(hr2.avg_value,5) fails due to avg_value being the wrong type. I fixed it with a cast ROUND(cast(hr2.avg_value as numeric), 5).

I can enter these in as bugs tomorrow, but most of my manual “fixes” would not be portable to other SQL and is probably something better handled in the SQLtranslate functionality. coalesce instead of isNull should work in all though.

I ended up making a lot of manual changes for the isNull and “+” issues. I am still getting other failures and debugging them one by one. But most of heracles viewing and analyze is working.

5

Richard,

Thank you for finding these errors. Sounds like postgres version may need
some tweaks. Can you add these under the WebAPI issue tracker on GH? Then
we can work together on getting it resolved (Heracles scripts vs SQL render
vs other issue).

Thanks!

6

No problem. I will wait until I get I get finished with working through the errors and then write them up.