search

ehrbase / ehrbase

An open source openEHR server
http://ehrbase.org
Apache License 2.0
265 stars 105 forks source link

AQL error: bad SQL grammar #273

Closed ppazos closed 4 years ago

ppazos commented 4 years ago

The AQL seems correct, but it seems I'm getting some malformed SQL mapping, maybe I'm missing something.

POST /ehrbase/rest/openehr/v1/query/aql HTTP/1.1
Accept: application/json; charset=UTF-8
Content-Length: 392
Content-Type: application/json; charset=UTF-8
Host: localhost:8080
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.5.12 (Java/11.0.6)
Accept-Encoding: gzip,deflate

{"q":"SELECT c, c/uid/value FROM EHR e CONTAINS COMPOSITION c CONTAINS CLUSTER cluster[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1] WHERE c/archetype_details/template_id/value = 'Laborbefund' AND e/ehr_status/subject/external_ref/id/value = '07f602e0-579e-4fe3-95af-381728bf0d49' AND '2020-07-05' <= cluster/items[at0006]/value/value AND cluster/items[at0006]/value/value <= '2020-07-08'"}

HTTP/1.1 400 
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: 0
X-Frame-Options: DENY
Content-Type: application/json;charset=UTF-8
Transfer-Encoding: chunked
Date: Thu, 09 Jul 2020 01:37:12 GMT
Connection: close

{"error":"Could not process query, reason:org.springframework.jdbc.BadSqlGrammarException: Access database using Jooq; bad SQL grammar [select ehr.js_composition(composition_join.id,'local.ehrbase.org')::text as \"c\", \"composition_join\".\"id\"||'::'||'local.ehrbase.org'||'::'||1 + COALESCE(\n(select count(*)\nfrom \"ehr\".\"composition_history\"\nwhere \"composition_join\".\"id\" = \"ehr\".\"composition_history\".\"id\"\ngroup by \"ehr\".\"composition_history\".\"id\"), 0) as \"/uid/value\" from \"ehr\".\"entry\" right outer join \"ehr\".\"composition\" as \"composition_join\" on \"composition_join\".\"id\" = \"ehr\".\"entry\".\"composition_id\" join \"ehr\".\"status\" as \"status_join\" on \"status_join\".\"ehr_id\" = \"composition_join\".\"ehr_id\" join \"ehr\".\"party_identified\" as \"subject_ref\" on \"subject_ref\".\"id\" = \"status_join\".\"party\" where (\"ehr\".\"entry\".\"template_id\" = ? and (\"ehr\".\"entry\".\"template_id\"='Laborbefund' AND ehr.js_canonical_party_ref(\"subject_ref\".\"party_ref_namespace\",\"subject_ref\".\"party_ref_type\",\"subject_ref\".\"party_ref_scheme\",\"subject_ref\".\"party_ref_value\")::json #>>'{id,value}'='07f602e0-579e-4fe3-95af-381728bf0d49' AND '2020-07-05'<=\"ehr\".\"entry\".\"entry\" @@ '\"/composition[openEHR-EHR-COMPOSITION.report-result.v1 and name/value=''Ergebnisbericht'']\".\"/content[openEHR-EHR-OBSERVATION.laboratory_test_result.v1]\".#.\"/data[at0001]\".\"/events\".\"/events[at0002]\".#.\"/data[at0003]\".\"/items[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]\".#.\"/items[at0006]\".#.\"/value\".\"value\" '::jsquery AND \"ehr\".\"entry\".\"entry\" @@ '\"/composition[openEHR-EHR-COMPOSITION.report-result.v1 and name/value=''Ergebnisbericht'']\".\"/content[openEHR-EHR-OBSERVATION.laboratory_test_result.v1]\".#.\"/data[at0001]\".\"/events\".\"/events[at0002]\".#.\"/data[at0003]\".\"/items[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]\".#.\"/items[at0006]\".#.\"/value\".\"value\"<='2020-07-08' '::jsquery))]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near \"2020\"\n  Position: 1681","status":"Bad Request"}
birgerhaarbrandt commented 4 years ago

@ppazos please attach OPT and example composition so that @chevalleyc can directly try to reproduce the error

ppazos commented 4 years ago

@Geisterfalle @chevalleyc FYI: https://github.com/ehrbase/fhir-bridge/blob/master/src/main/resources/opt/Laborbefund.opt

ppazos commented 4 years ago

2020-07-29: re-tested for https://github.com/ehrbase/project_management/issues/319

The error still happens, it seems related to the last issue reported in https://github.com/ehrbase/ehrbase/issues/270

birgerhaarbrandt commented 4 years ago

@ppazos can you please check if this has been resolved with the latest changes

chevalleyc commented 4 years ago

Tested with the current code base the following expression:

"q":"SELECT cluster
        FROM EHR e
        CONTAINS COMPOSITION c
        CONTAINS CLUSTER cluster[openEHR-EHR-CLUSTER.specimen.v1]
        WHERE '2020-01-01' <= cluster/items[at0034]/value/value AND cluster/items[at0034]/value/value <= '2020-12-12'
        "

Seems working without flaw. In doubt, could you please post a valid composition matching the template and the query to finalize testing before closing this issue. Tx

chevalleyc commented 4 years ago

Assuming this issue is not occurring anymore.