ehrbase
ehrbase copied to clipboard
ehrbase
AQL "ORDER BY c/context/start_time ASC" and "ORDER BY c/context/start_time/value ASC" return different order
SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time ASC
and
SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time/value ASC
should return exactly the same result set with all items in exact the same order
Test Case/s To Reproduce Issue
B-104 Get Compositions (filtered: top 5, ordered by: start_time ASC) B-106 Get Compositions (filtered: top 5, ordered by: start_time value ASC)
# path to test case
tests/robot/QUERY_SERVICE_TESTS/A.1_EXECUTE_AD-HOC_QUERY/A.1__a_Loaded_DB.robot
# robot command to execute related test case(s)
robot -d results -L TRACE -i Aql_loaded_dbAND635 robot/QUERY_SERVICE_TESTS
Actual Result
slightly different order in result set
ORDER BY c/context/start_time ASC
Click to open
{
"q": "SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time ASC",
"columns": [
{
"path": "c",
"name": "#0"
}
],
"rows": [
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "34dc4b8c-575b-4913-ab1d-adae69855471::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "A_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T10:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 1.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 1",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0003",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "25212052-12b2-445c-a2f1-c255afd4fd93::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "A_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T10:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 1.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 1",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0003",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "0c0e5e62-8dd9-4377-a642-6058f7e69a93::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "B_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T11:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 2.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 2",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0004",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "6bcf2652-ce05-4e1c-912e-4dc8b8623d3b::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "B_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T11:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 2.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 2",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0004",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "3c6628a9-480c-47fb-a1eb-63abf90b612b::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "C_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T12:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. House",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "fc376c46-29c1-4090-bc01-5cc046af7f26"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_evaluation.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "EVALUATION",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "quantity",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_QUANTITY",
"units": "kg",
"magnitude": 78.5
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_evaluation.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-EVALUATION.minimal.v1",
"rm_name": "EHR",
"rm_entity": "EVALUATION",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-EVALUATION"
}
},
"archetype_node_id": "openEHR-EHR-EVALUATION.minimal.v1"
}
]
}
]
]
}
ORDER BY c/context/start_time/value ASC
Click to open
{
"q": "SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time/value ASC",
"columns": [
{
"path": "c",
"name": "#0"
}
],
"rows": [
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "34dc4b8c-575b-4913-ab1d-adae69855471::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "A_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T10:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 1.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 1",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0003",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "25212052-12b2-445c-a2f1-c255afd4fd93::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "A_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T10:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 1.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 1",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0003",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "6bcf2652-ce05-4e1c-912e-4dc8b8623d3b::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "B_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T11:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 2.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 2",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0004",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "0c0e5e62-8dd9-4377-a642-6058f7e69a93::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "B_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T11:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. Yamamoto",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "a7c0bdb5-0b46-4db7-90a8-1a74e231361e"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "ADMIN_ENTRY",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "ordinal",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_ORDINAL",
"value": 2.0,
"symbol": {
"_type": "DV_CODED_TEXT",
"value": "option 2",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "at0004",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "local"
}
}
}
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_admin.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-ADMIN_ENTRY.minimal.v1",
"rm_name": "EHR",
"rm_entity": "ADMIN_ENTRY",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-ADMIN_ENTRY"
}
},
"archetype_node_id": "openEHR-EHR-ADMIN_ENTRY.minimal.v1"
}
]
}
],
[
{
"uid": {
"_type": "OBJECT_VERSION_ID",
"value": "186e66b9-9117-4a7c-b7b0-c86c0893f883::local.ehrbase.org::1"
},
"name": {
"_type": "DV_TEXT",
"value": "C_Minimal"
},
"_type": "COMPOSITION",
"context": {
"_type": "EVENT_CONTEXT",
"setting": {
"_type": "DV_CODED_TEXT",
"value": "primary medical care",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "228",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"start_time": {
"_type": "DV_DATE_TIME",
"value": "2021-10-16T12:16:16.166-03:00"
},
"participations": [
{
"mode": {
"_type": "DV_CODED_TEXT",
"value": "not specified",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "193",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"_type": "PARTICIPATION",
"function": {
"_type": "DV_TEXT",
"value": "legal guardian consent author"
},
"performer": {
"name": "Alexandra Alamo",
"_type": "PARTY_RELATED",
"relationship": {
"_type": "DV_CODED_TEXT",
"value": "mother",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "10",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
}
}
}
]
},
"category": {
"_type": "DV_CODED_TEXT",
"value": "event",
"defining_code": {
"_type": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "openehr"
}
}
},
"composer": {
"name": "Dr. House",
"_type": "PARTY_IDENTIFIED",
"external_ref": {
"id": {
"_type": "HIER_OBJECT_ID",
"value": "fc376c46-29c1-4090-bc01-5cc046af7f26"
},
"type": "PERSON",
"_type": "PARTY_REF",
"namespace": "DEMOGRAPHIC"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"territory": {
"_type": "CODE_PHRASE",
"code_string": "UY",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"archetype_details": {
"_type": "ARCHETYPED",
"rm_version": "1.0.2",
"template_id": {
"_type": "TEMPLATE_ID",
"value": "minimal_evaluation.en.v1"
},
"archetype_id": {
"_type": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.minimal.v1"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.minimal.v1",
"content": [
{
"name": {
"value": "Minimal",
"_type": "DV_TEXT"
},
"_type": "EVALUATION",
"subject": {
"_type": "PARTY_SELF"
},
"encoding": {
"_type": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"_type": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"_type": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"data": {
"name": {
"value": "Arbol",
"_type": "DV_TEXT"
},
"archetype_node_id": "at0001",
"_type": "ITEM_TREE",
"items": [
{
"name": {
"value": "quantity",
"_type": "DV_TEXT"
},
"value": {
"_type": "DV_QUANTITY",
"units": "kg",
"magnitude": 78.5
},
"_type": "ELEMENT",
"archetype_node_id": "at0002"
}
]
},
"archetype_details": {
"rm_version": "1.0.2",
"template_id": {
"value": "minimal_evaluation.en.v1"
},
"archetype_id": {
"value": "openEHR-EHR-EVALUATION.minimal.v1",
"rm_name": "EHR",
"rm_entity": "EVALUATION",
"version_id": "1",
"rm_originator": "openEHR",
"domain_concept": "minimal",
"qualified_rm_entity": "openEHR-EHR-EVALUATION"
}
},
"archetype_node_id": "openEHR-EHR-EVALUATION.minimal.v1"
}
]
}
]
]
}
Expected Result
same order in result set
@wlad the expression
SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time
results in comparing json (which is not comparable!). However, at SQL level it is a String comparison. The correct expression is
SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time/value
as it does true DateTime comparison
Thank you for clarification @chevalleyc . Sounds reseanable to me. I'd also like to hear what @ppazos has to say about it because he created related test-data-sets.
@ppazos Do we have to fix test-data-set B/104 (../expected_results/loaded_db/B/104_get_compositions_top_5_ordered_by_starttime_asc.json) ?
{
"meta": {
"_type": "RESULTSET",
"_created": "2021-10-16T09:59:11.803Z",
"_executed_aql": "SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time ASC"
},
"q": "SELECT TOP 5 c FROM COMPOSITION c ORDER BY c/context/start_time ASC",
"columns": [
{
"name": "#0",
"path": "/"
}
],
"rows": [
"__MODIFY_WITH_LIST_OF_5_YOUNGEST_COMPOSITIONS__"
]
}
@wlad conceptually I would expect a comparison of data types in the RM. For instance, the comparison of DV_DATE_TIME would compare it's internal representation. I don't know what is the internal implementation in EHRBASE currently.
Though there is an open discussion in the SEC about how to compare partial dates which are valid, for instance 2010 is bigger or smaller than 2010-05?
@ppazos stricto sensus, DV_DATE_TIME comes with a lot of attributes outside the "pure" temporal part. I don't think this data structure can be compared as is (it is not an object), unless we do specify a compare() method for the type (with details on what it does encompass). Elsewhere partial date comparison is indeed supported.
@chevalleyc we are on agreement, my comment was about RM comparison "in theory". The other attributes besides the datetime data, could be used for fuzzy comparison, which is not fully defined by the current spec.
The issue with partial date time expression comparison, it is not so simple as comparing the strings, because 2010 is referring to any day and month of 2010, which is like referencing a range from Jan 1st to Dec 31st. Similar happens to 2010-10, which refers to any day of that year and month, which is again like referencing a range. So comparing these "ranges" is not trivial. There is a discussion about this in the SEC slack channel which needs more attention.
@ppazos re partial date comparison, in EHRbase we never use string comparison but instead we use timestamps (epoch_offset) derived from the ISO8601 temporal (partial or full). This is also much faster than using strings... We have a test suite https://github.com/ehrbase/openEHR_SDK/blob/develop/client/src/test/resources/testsets/arbitrary/arbitrary_tests.csv for partial date/time (this could be elaborated further to test your example above) but the logic is always based on timestamp. For dv_duration, it's a bit more complicated and we use the ISO8601 'interval' in postgresql to perform the comparison https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
Thanks @chevalleyc my comment was in the context of your previous note "However, at SQL level it is a String comparison.", though I think you where talking about the JSON not the date/time data point.
I tried to search for information about how postgres does compare timestamps and how partial dates/times are represented in postgres, but I wasn't successful, just found some discussions on Stack Overflow about the topic like https://stackoverflow.com/questions/17133422/postgres-and-partial-dates/17133600 which suggest that for partial date/times the ISO 8601 string is used.
I have also checked the tests you mentioned, but it's too cryptic to me since I wasn't involved in the development of those tests and components being tested.
Besides how things are implemented today, and this goes beyond EHRBASE, we need to define how to compare date/time expressions that have different components, like 2010 and 2010-10, so we can answer questions like: is 2010 < 2010-10 or not?
For EHRBASE we can document how things work today, which will help users, and we can also base our tests on that documentation, which can include a comment about the missing bits in the openEHR spec and the technical decisions made to make these comparisons work.
I think it is worthy also to document what happens in AQL results when comparisons are done at paths that point to objects that are not primitive, like comparing DV_XXX, ELEMENT, etc.