| Wrong SQL using COALESCE [message #1711347] |
Thu, 15 October 2015 08:29  |
 Frank Enders
Messages: 1
Registered: October 2015 |
Junior Member |
|
|
Hello together,
I have a NamedQuery, which occasionally produces an error (tested with 2.5.1, 2.6.0 and 2.6.1-RC1). Meaning, generally it works and returns the expected results. But in some cases, an error is thrown instead, due to an invalid SQL Query:
The (invalid) SQL, which is shown in the stacktrace is
SELECT t0.cid AS a1 FROM CONCEPTS t0 LEFT OUTER JOIN TERMS t2 ON ((t2.CONCEPT_SERIAL = t0.CONCEPT_SERIAL) AND ((t2.preferred = ?) AND (t2.language = ?))) LEFT OUTER JOIN TERMS t3 ON ((t3.CONCEPT_SERIAL = t0.CONCEPT_SERIAL) AND ((t3.preferred = ?) AND (t3.language = ?))), CONCEPTRELATIONTYPES t5, CONCEPTS t4, CONCEPTRELATIONS t1 WHERE ((((t4.cid = ?) AND (t5.label = ?)) AND (t1.terminology = ?)) AND (((t4.CONCEPT_SERIAL = t1.CONCEPT_SERIAL_B) AND (t5.CONCEPTRELATIONTYPE_SERIAL = t1.CONCEPTRELATIONTYPE_SERIAL)) AND (t0.CONCEPT_SERIAL = t1.CONCEPT_SERIAL_A))) ORDER BY COALESCE(t2.labelt3.label, ASC LIMIT ?, ?
So the problem is that the COALESCE part is invalid:
... COALESCE(t2.labelt3.label, ASC LIMIT ?, ?
instead of
... COALESCE(t2.label, t3.label) ASC LIMIT ?, ?
That mostly happens when using the query for the first time after the application was deployed. The follwoing requests mostly work.
The named Query, where it is generated from, is:
@NamedQuery(name = "ConceptRelation.findChildConceptIdsByRelationTypeSortedByPreferredTermLabelAscending",
query = "SELECT cr.relationSource.conceptId FROM ConceptRelation cr LEFT JOIN cr.relationSource.terms terms ON terms.isPreferred = 1 AND terms.language = :language LEFT JOIN cr.relationSource.terms termsFallback ON termsFallback.isPreferred = 1 AND termsFallback.language = 'xyz' WHERE cr.relationTarget.conceptId = :conceptId AND cr.relationType.label = :relationTypeLabel AND cr.terminology.serial = :terminologySerial ORDER BY COALESCE( terms.label, termsFallback.label ) ASC")
Any ideas? Am I missing anything?
Thanks and best regards,
Frank
|
|
|
|
Powered by
FUDForum. Page generated in 0.02812 seconds
] 
Search
Help
Register
Login
Home
