Re: addExists(subQuery) builds invalid SQL if subquery has orderby

hi vasily,

order by in the subquery seems to be a problem for hsqldb. it works in 
mysql.

jakob

Vasily Ivanov schrieb:
> 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
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
> 
> 
>