Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Wrong SQL using COALESCE
Wrong SQL using COALESCE [message #1711347] Thu, 15 October 2015 08:29 Go to next message
Frank Enders is currently offline Frank EndersFriend
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

Re: Wrong SQL using COALESCE [message #1711705 is a reply to message #1711347] Mon, 19 October 2015 11:55 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1380
Registered: July 2009
Senior Member
Please file a bug adding as much as you can about the conditions it occurs under, and vote for it. I can't say what might be causing this - a non-deterministic code path, or a timing issue, so someone will need to step through the code to see how it could possibly happen and work backward from there.
Previous Topic:@OrderBy problem
Next Topic:Nested Object initialization
Goto Forum:
  


Current Time: Thu Mar 04 10:44:12 GMT 2021

Powered by FUDForum. Page generated in 0.01663 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top