`GET /studies/{studyId}/analysis/search/id` returns HTTP 500 (malformed `idSearch` native query)

[MitchellShiell]

The analysis ID-search endpoint is broken on develop. Every GET /studies/{studyId}/analysis/search/id request returns HTTP 500 with a PostgreSQL grammar error, regardless of parameters — including the only supported parameter, fileId. The POST variant returns HTTP 400. The endpoint is effectively unusable.

The cause is an unfinished refactor of the idSearch named native query left over from the removal of donor/specimen/sample from the base schema (#878): the generated SQL's WHERE clause ends with a dangling AND submitter_sample_id — a bare text column with no comparison operator.

Affected version

• Branch: develop (reproduced on song-server commit 3c7bf83b)
• Also present in the published image ghcr.io/overture-stack/song-server:5131f6f8 ("score client name change" score client name change #905)

Steps to reproduce

Against a Song 5.3.0 server (noSecurityDev, ID_USELOCAL=true) with a study demo, the genomicVariants schema registered, and at least one analysis submitted:

# The only supported query parameter — still 500s:
curl -s "http://localhost:8080/studies/demo/analysis/search/id?fileId=ff190160-0bb6-59f4-91ee-95f1162d4c7f"

# Legacy parameters (never read post-5.3.0) also 500:
curl -s "http://localhost:8080/studies/demo/analysis/search/id?submitterSampleId=DO001-SA01"

Actual result

{
  "errorId": "unknown.error",
  "httpStatusCode": 500,
  "httpStatusName": "INTERNAL_SERVER_ERROR",
  "message": "Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet",
  "debugMessage": "[ROOT_CAUSE] -> org.postgresql.util.PSQLException: ERROR: argument of AND must be type boolean, not type text\n  Position: 111"
}

POST /studies/demo/analysis/search/id with body {"objectId": ".*"} returns HTTP 400.

Expected result

HTTP 200 with a JSON array of matching analyses (filtered by objectId/fileId regex), or an empty array when nothing matches.

Root cause

song-server/src/main/java/bio/overture/song/server/model/entity/IdView.java builds the idSearch named native query by string concatenation, and the string terminates on a dangling AND + column name:

@NamedNativeQuery(
    name = IdView.ID_SEARCH_QUERY_NAME,
    query =
        "SELECT DISTINCT " + STUDY_ID + ", " + ANALYSIS_ID + ", " + ANALYSIS_STATE
            + " FROM IdView WHERE "
            + STUDY_ID + " = :" + ModelAttributeNames.STUDY_ID
            + " AND " + OBJECT_ID + " ~* :" + ModelAttributeNames.OBJECT_ID
            + " AND " + SUBMITTER_SAMPLE_ID,   // <-- dangling: no operator / right-hand side
    resultSetMapping = IdView.ID_VIEW_DTO)

The emitted SQL is:

SELECT DISTINCT study_id, analysis_id, analysis_state
FROM IdView
WHERE study_id = :studyId
  AND object_id ~* :objectId
  AND submitter_sample_id          -- text column used as a boolean → error at position 111

PostgreSQL rejects AND submitter_sample_id because submitter_sample_id is text, not boolean.

Related leftovers from the same refactor:

• IdView still declares donorId, specimenId, and sampleId @Columns that are no longer part of the search contract.
• UriResolver.expandDonorUri / expandSpecimenUri / expandSampleUri and FederatedIdService's REST/URI plumbing are now dead code (no callers), since IdService only exposes generateAnalysisId() and getFileId(...).

Impact

• The /search/id endpoint (GET and POST) cannot be used at all.
• Any client that resolves analyses by object/file ID via this endpoint is broken.
• Documentation examples that relied on this endpoint have been updated to use GET .../analysis / .../analysis/paginated plus client-side filtering as a workaround.

Suggested fix

Terminate the WHERE clause after the object_id regex predicate and drop the trailing AND SUBMITTER_SAMPLE_ID:

query =
    "SELECT DISTINCT " + STUDY_ID + ", " + ANALYSIS_ID + ", " + ANALYSIS_STATE
        + " FROM IdView WHERE "
        + STUDY_ID + " = :" + ModelAttributeNames.STUDY_ID
        + " AND " + OBJECT_ID + " ~* :" + ModelAttributeNames.OBJECT_ID,

Then remove the now-unused donorId/specimenId/sampleId columns from IdView (and, separately, the dead donor/specimen/sample URI-resolution code) so the view and the search contract match the consolidated schema. Add an integration test covering GET /studies/{studyId}/analysis/search/id?fileId=....

Environment

• Song server: develop @ 3c7bf83b / image ghcr.io/overture-stack/song-server:5131f6f8
• Postgres: postgres:11.1, Flyway-managed schema (migrations through V1_23)
• Profiles: dev, noSecurityDev; ID_USELOCAL=true; SCHEMAS_ENFORCELATEST=true

[leoraba]

this endpoint lost its search purpose by removing query params donorId, specimenId, sampleId. Fixing the SQL query and view will enable the ability to look by objectId only.

Adding the ability of searching by "dynamic" fields could be inefficient but technically doable, open to discuss what is the desirable solution for this issue.