Patient Profile fails when CDM and Vocabulary are configured in separate schemas (WebAPI 2.15.1)
Los mantenedores suelen responder en 3 días
Nadie ha tomado este issue todavía.
Evaluación
- Dificultad
- 2/5
- Tiempo estimado
- 1-3 horas
- Aptitud para principiantes
- 55/100
Línea de trabajo
Read src/main/resources/resources/person/sql/personInfo.sql and compare its vocabulary table qualifier with the master version linked in the issue. Then inspect the other Patient Profile SQL templates for the same schema assumption and check the related tests. Done means profile queries use the configured Vocabulary schema consistently; the issue does not identify specific tests.
Escrito por el modelo de indexación a partir del texto del issue.
Descripción
Description
We encountered an issue in OHDSI ATLAS when attempting to view patient profiles, either directly through the Profiles section or through the Samples tab of a generated cohort.
Although the patients exist in the CDM and cohort generation and sampling work correctly, ATLAS displays the following error:
Can't find [patientID]
Environment
- WebAPI version: 2.15.1
- Build: 2.15.1-SNAPSHOT
- Commit:
ba50f13a - Database: PostgreSQL 16.4
- Deployment: Docker
Data source configuration
Our CDM and Vocabulary are configured in separate schemas using webapi.source_daimon:
| Daimon type | Schema |
|---|---|
| CDM (0) | clinical_cdm |
| Vocabulary (1) | shared_vocabulary |
| Results (2) | clinical_cdm_results |
The vocabulary schema is shared across multiple CDM data sources.
Steps to reproduce
- Configure a data source with separate CDM and Vocabulary schemas.
- Ensure that the
persontable exists in the CDM schema and theconcepttable exists only in the Vocabulary schema. - Open the Profiles section in ATLAS.
- Select the configured data source.
- Search for a valid person ID.
Actual behavior
WebAPI returns HTTP 500, and ATLAS displays Can't find [patientID].
The WebAPI logs show:
org.springframework.jdbc.BadSqlGrammarException
ERROR: relation "clinical_cdm.concept" does not exist
at org.ohdsi.webapi.service.PersonService.getPersonProfile(PersonService.java:81)
The executed SQL is:
SELECT gender_concept_id,
year_of_birth,
concept_name AS gender
FROM clinical_cdm.person p
JOIN clinical_cdm.concept c
ON p.gender_concept_id = c.concept_id
WHERE person_id = ?;
Root cause
The SQL template personInfo.sql uses the same @tableQualifier parameter for both the person and concept tables, ignoring the separately configured Vocabulary schema.
Affected source file:
https://github.com/OHDSI/WebAPI/blob/ba50f13a/src/main/resources/resources/person/sql/personInfo.sql
Expected behavior
Patient Profile queries should use the CDM schema for clinical tables and the configured Vocabulary schema for vocabulary tables.
The expected SQL template would be:
SELECT gender_concept_id,
year_of_birth,
concept_name AS gender
FROM @tableQualifier.person p
JOIN @vocabularyTableQualifier.concept c
ON p.gender_concept_id = c.concept_id
WHERE person_id = @personId;
Confirmed workaround
We created a PostgreSQL view in the CDM schema pointing to the shared vocabulary table:
CREATE VIEW clinical_cdm.concept AS
SELECT *
FROM shared_vocabulary.concept;
After creating this view, patient profiles were successfully displayed in ATLAS.
Additional observations
The same SQL template in the v2.15.2 tag still references the CDM schema for the concept table:
https://github.com/OHDSI/WebAPI/blob/v2.15.2/src/main/resources/resources/person/sql/personInfo.sql
However, the current master branch appears to contain a correction using @vocabularyTableQualifier:
https://github.com/OHDSI/WebAPI/blob/master/src/main/resources/resources/person/sql/personInfo.sql
Could you please confirm whether this issue has already been addressed and whether the fix is planned to be included in a stable release of the 2.15.x series?
It would also be helpful to verify that all other Patient Profile queries consistently respect the configured Vocabulary schema.
Thank you for maintaining WebAPI and ATLAS.
- Lenguaje dominante
- Java
- Estrellas
- 154
- Forks
- 185
- Merge medio
- 3 d 57 min
- PR fusionados (30 d)
- 14
Preparar el entorno
- Incluye un Dockerfile o un archivo de Docker Compose
- Sin plantilla de pull request
- Sin guía de contribución
Primeros pasos
- Lee el issue completo y luego la guía de contribución del proyecto.
- Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
- Haz un fork del repositorio y trabaja en una rama.
- Abre un pull request que haga referencia al número del issue.
Más de OHDSI/WebAPI
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 86/100
Los mantenedores suelen responder en 3 días
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 68/100
Los mantenedores suelen responder en 3 días
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 72/100
Los mantenedores suelen responder en 3 días
-
CDM cache warming issues one record-count query per concept on Snowflake, Spark and BigQuery (getConceptsForBatch.sql relies on integer division)Posiblemente ocupada @DG47 la tomó hace 10 días. Abierto
Dificultad 3/5 1-2 días Aptitud para principiantes 66/100
Los mantenedores suelen responder en 3 días
-
GET /conceptset/{id}/expression returns items in nondeterministic order (query has no ORDER BY)Abierto
Dificultad 3/5 1-2 días Aptitud para principiantes 67/100
OHDSI/WebAPI#2543 · 3 comentarios ·
Los mantenedores suelen responder en 3 días
Todos los issues de OHDSI/WebAPI
Issues similares
-
lompotc silently drops Java/C# class members past 128 instead of erroring, as its header claimsAbiertobug sev-5 vm
Dificultad 2/5 1-3 horas Aptitud para principiantes 76/100
-
enhancement
Dificultad 2/5 1-3 horas Aptitud para principiantes 65/100
Los mantenedores suelen responder en 1 día
-
Dificultad 1/5 1-3 horas Aptitud para principiantes 85/100
checkstyle/checkstyle#22914 · 1 comentario ·
Los mantenedores suelen responder en 1 día
-
Add support for PostgreSQL 19Abiertocomponent/data-layer
Dificultad 2/5 1-3 horas Aptitud para principiantes 75/100
Los mantenedores suelen responder en 1 día
-
opentelemetry_exporter_otlp-1.40.0-py3-none-any.whl: 3 vulnerabilities (highest severity is: 6.5)AbiertoMend: dependency security vulnerability untriaged
Dificultad 1/5 Menos de una hora Aptitud para principiantes 77/100
opensearch-project/data-prepper#7314 ·
Los mantenedores suelen responder en 1 día