addExists(subQuery) builds invalid SQL if subquery has orderby

"Vasily Ivanov" <[email protected]>
Newsgroups gmane.comp.jakarta.ojb.user
Message-ID <[email protected]>
Hi All,

I've got the following code:
===========Code===========
//build sub query
Criteria subCriteria = new Criteria();
subCriteria.addEqualToField("parentId", Criteria.PARENT_QUERY_PREFIX + "id");
subCriteria.addIn("someChildFeild", someCollection);

ReportQueryByCriteria subQuery =
QueryFactory.newReportQuery(Child.class, subCriteria);
subQuery.setAttributes(new String[] { "1" });
subQuery.addOrderByDescending("someChildFeild"); //******

//build main query
Criteria mainCriteria = new Criteria();
mainCriteria.addExists(subQuery);

ReportQueryByCriteria mainQuery =
QueryFactory.newReportQuery(Parent.class, mainCriteria);
mainQuery.setAttributes(new String[] { "id", "someParentFeild1",
"someParentFeild2" });
mainQuery.addOrderByDescending("someParentFeild2");

===========Generated SQL===========
SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
FROM PARENT A0
WHERE EXISTS (SELECT 1, B0.SOME_CHILD_FEILD as ojb_col_2
		          FROM CHILD B0
		          WHERE (B0.PARENT_ID = A0.ID)
		                        AND  (B0.SOME_CHILD_FEILD IN (?, ?))
	                  ORDER BY 2 DESC)
ORDER BY 3 DESC
=================================
This SQL throws "ORA-00907: missing right parenthesis".

If we remove line marked with //****** we'll get:
===========Generated SQL===========
SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
FROM PARENT A0
WHERE EXISTS (SELECT 1
		          FROM CHILD B0
		          WHERE (B0.PARENT_ID = A0.ID)
		                        AND  (B0.SOME_CHILD_FEILD IN (?, ?)))
ORDER BY 3 DESC
=================================
...which works fine.

Question: Should addExists(subQuery) check that subQuery doesn't have
any orderby added or it's up to developer?

Cheers,
  Vasily
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.