search

OHDSI / WebAPI

OHDSI WebAPI contains all OHDSI services that can be called from OHDSI applications
Apache License 2.0
129 stars 166 forks source link

Failed to add new sample on Netezza (UncategorizedSQLException) #1782

Closed DKashenko closed 3 years ago

DKashenko commented 3 years ago

WebApi 2.8.1

Expected behavior

Sample created successfully

Actual behavior

netezza_sample

WebAPI/cohortsample/555/Netezza53 Request Method: POST Status Code: 500

{"payload":{"cause":null,"stackTrace":[],"message":"An exception occurred: org.springframework.jdbc.UncategorizedSQLException","localizedMessage":"An exception occurred: org.springframework.jdbc.UncategorizedSQLException","suppressed":[]},"headers":{"id":"bc66107e-ef42-e8e6-f620-382138948e3e","timestamp":1613374421678}}

2021-02-15 07:37:55.976 ERROR 39 --- [nio-8080-exec-8] o.o.w.u.GenericExceptionMapper           : org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT *
FROM (
     SELECT
         cohort.subject_id AS person_id,
         cohort.cohort_definition_id AS cohort_definition_id,
         person.gender_concept_id AS gender_concept_id,
         CAST(DATE_PART('YEAR', cohort.cohort_start_date) - person.year_of_birth  AS INTEGER) AS age
     FROM RESULTS_TEST_280.cohort
     JOIN ADMIN.person ON person_id = subject_id
) AS cte
WHERE cohort_definition_id = ?

ORDER BY RAND()
]; SQL state [HY000]; error code [1100]; ERROR:  Function 'RAND()' does not exist
    Unable to identify a function that satisfies the given argument types
    You may need to add explicit typecasts
; nested exception is org.netezza.error.NzSQLException: ERROR:  Function 'RAND()' does not exist
    Unable to identify a function that satisfies the given argument types
    You may need to add explicit typecasts
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:654)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:720)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:730)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:780)
    at org.ohdsi.webapi.cohortsample.CohortSamplingService.sampleElements(CohortSamplingService.java:434)
    at org.ohdsi.webapi.cohortsample.CohortSamplingService.createSample(CohortSamplingService.java:176)
    at org.ohdsi.webapi.service.CohortSampleService.createCohortSample(CohortSampleService.java:137)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
    at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.ohdsi.webapi.shiro.filters.ResponseNoCacheFilter.doFilter(ResponseNoCacheFilter.java:26)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)

Steps to reproduce behavior

  1. Login by a user who has access to Netezza DS
  2. Run any valid cohort on Netezza
  3. Try to add a new sample
chrisknoll commented 3 years ago

On Netezza, can you try to substitute RANDOM() instead of RAND() and execute it to see if that works? if so, it's just a Sql translation issue.