Response format different when DISTINCT JPQL query condition used on Search SOAP interface

[VKTB]

I have the following investigation in my database:

ID: 1
CREATE_ID: simple/root
CREATE_TIME: 2023-07-06 16:30:08
DOI: NULL
ENDDATE: 2000-07-09 00:00:00
FILECOUNT: 0
FILESIZE: 0
MOD_ID: simple/root
MOD_TIME: 2023-07-06 16:30:08
NAME: INVESTIGATION 1
RELEASEDATE: 2000-07-05 00:00:00
STARTDATE: 2000-04-03 00:00:00
SUMMARY: NULL
TITLE: Analysis reflect work or hour color maybe.
VISIT_ID: 70
FACILITY_ID: 1
TYPE_ID: 2

When I post the following XML, which does not include a DISTINCT JPQL query condition, to the Search SOAP interface:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://icatproject.org" xmlns:ns1="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Header/>
   <ns1:Body>
      <ns0:search>
         <sessionId>my-session-id</sessionId>
         <query>SELECT o FROM Investigation o WHERE o.id = &apos;1&apos;</query>
      </ns0:search>
   </ns1:Body>
</SOAP-ENV:Envelope>

I get the following XML response back:

<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:searchResponse xmlns:ns2="http://icatproject.org">
            <return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:investigation">
                <createId>simple/root</createId>
                <createTime>2023-07-06T16:30:08Z</createTime>
                <id>1</id>
                <modId>simple/root</modId>
                <modTime>2023-07-06T16:30:08Z</modTime>
                <endDate>2000-07-09T00:00:00Z</endDate>
                <fileCount>0</fileCount>
                <fileSize>0</fileSize>
                <name>INVESTIGATION 1</name>
                <releaseDate>2000-07-05T00:00:00Z</releaseDate>
                <startDate>2000-04-03T00:00:00Z</startDate>
                <title>Analysis reflect work or hour color maybe.
Much team discussion message weight.</title>
                <visitId>70</visitId>
            </return>
        </ns2:searchResponse>
    </S:Body>
</S:Envelope>

vs when I post the following XML, which includes a DISTINCT JPQL query condition, to the Search SOAP interface:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://icatproject.org" xmlns:ns1="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Header/>
   <ns1:Body>
      <ns0:search>
         <sessionId>my-session-id</sessionId>
         <query>SELECT DISTINCT o.id, o.title, o.summary, o.visitId, o.name, o.doi, o.startDate, o.endDate FROM Investigation o WHERE o.id = &apos;1&apos;</query>
      </ns0:search>
   </ns1:Body>
</SOAP-ENV:Envelope>

I get the following XML response back:

<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
        <ns2:searchResponse xmlns:ns2="http://icatproject.org">
            <return xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:fieldSet">
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:long">1</fields>
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">Analysis reflect work or hour color maybe. Much team discussion message weight.</fields>
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">70</fields>
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">INVESTIGATION 1</fields>
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:dateTime">2000-04-03T00:00:00Z</fields>
                <fields xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:dateTime">2000-07-09T00:00:00Z</fields>
            </return>
        </ns2:searchResponse>
    </S:Body>
</S:Envelope>

The issue:
The latter XML response does not specify the type of entity and its attribute names. This is a problem because if you look at the latter XML request, it requests 8 attributes but only 6 values are returned in the response so there is no way of knowing which value belongs to which attribute. There are only 6 values returned because the doi and summary attributes were requested but their value is null in the database so the SOAP framework omits them from the response.

[VKTB]

In fact, it's not just in the SOAP interface; the response format is also different in the /entityManager REST-like endpoint.

When I send the following GET request which does not include a DISTINCT JPQL query condition:

http://localhost:8080/icat/entityManager/?sessionId=my-session-id&query=SELECT o FROM Investigation o WHERE o.id = '1'

I get the following JSON response back:

[
    {
        "Investigation": {
            "id": 1,
            "createId": "simple/root",
            "createTime": "2023-07-06T16:30:08.000Z",
            "modId": "simple/root",
            "modTime": "2023-07-06T16:30:08.000Z",
            "dataCollectionInvestigations": [],
            "datasets": [],
            "endDate": "2000-07-09T00:00:00.000Z",
            "fileCount": 0,
            "fileSize": 0,
            "fundingReferences": [],
            "investigationFacilityCycles": [],
            "investigationGroups": [],
            "investigationInstruments": [],
            "investigationUsers": [],
            "keywords": [],
            "name": "INVESTIGATION 1",
            "parameters": [],
            "publications": [],
            "releaseDate": "2000-07-05T00:00:00.000Z",
            "samples": [],
            "shifts": [],
            "startDate": "2000-04-03T00:00:00.000Z",
            "studyInvestigations": [],
            "title": "Analysis reflect work or hour color maybe.\nMuch team discussion message weight.",
            "visitId": "70"
        }
    }
]

vs when I send the following GET request which includes a DISTINCT JPQL query condition:

http://localhost:8080/icat/entityManager/?sessionId=my-session-id&query=SELECT DISTINCT o.id, o.title, o.summary, o.visitId, o.name, o.doi, o.startDate, o.endDate FROM Investigation o WHERE o.id = '1'

I get the following JSON response back:

[
    [
        1,
        "Analysis reflect work or hour color maybe.\nMuch team discussion message weight.",
        null,
        "70",
        "INVESTIGATION 1",
        null,
        "2000-04-03T00:00:00.000Z",
        "2000-07-09T00:00:00.000Z"
    ]
]

Additional differences:

• null values are omitted from the JSON response when a DISTINCT JPQL query condition is not used but they are not omitted when one is used.
• Former returns a list of objects whereas the latter returns a list of lists.

[VKTB]

Because Python-ICAT consumes ICAT over SOAP, it means that the client.search method does not return a list of investigations objects but instead returns a list of tuples (without the null values) when a DISTINCT JPQL query condition is used.