Search queries that sort on a SP used in the query have a redundant join in the generated sql.

[michaelabuckley]

Describe the bug

This query Patient?name=smith&_sort=name produces sql with a redundant join.

The second join of the string table (t2) is redundant. This query will do more work and be slower.

Observed Results

SELECT t1.RES_ID FROM HFJ_RESOURCE t1 
INNER JOIN HFJ_SPIDX_STRING t0 ON (t1.RES_ID = t0.RES_ID) 
LEFT OUTER JOIN HFJ_SPIDX_STRING t2 ON ((t1.RES_ID = t2.RES_ID) AND (t2.HASH_IDENTITY = ?)) 
WHERE ((t0.HASH_NORM_PREFIX = ?) AND (t0.SP_VALUE_NORMALIZED LIKE ?)) 
ORDER BY t2.SP_VALUE_NORMALIZED ASC NULLS LAST

Expected Results

SELECT t1.RES_ID FROM HFJ_RESOURCE t1 
INNER JOIN HFJ_SPIDX_STRING t0 ON (t1.RES_ID = t0.RES_ID) 
WHERE ((t0.HASH_NORM_PREFIX = ?) AND (t0.SP_VALUE_NORMALIZED LIKE ?)) 
ORDER BY t0.SP_VALUE_NORMALIZED ASC NULLS LAST

To Reproduce

Run the test ca.uhn.fhir.jpa.dao.r4.FhirResourceDaoR4SearchSqlTest#testSortJoin in https://github.com/hapifhir/hapi-fhir/pull/6331

Expected behavior

The shorter query is produced.