[
https://issues.apache.org/jira/browse/OJB-120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Armin Waibel resolved OJB-120.
------------------------------
Resolution: Fixed
Fix Version/s: 1.0.5
Add new method Platform.boolean supportsOrderByInSubSelect(). If a database
doesn't support a "order by"-clause in the subquery this method returns false
and a warning will be logged.
> addExists() builds invalid SQL if subquery has orderby clause
> -------------------------------------------------------------
>
> Key: OJB-120
> URL: https://issues.apache.org/jira/browse/OJB-120
> Project: OJB
> Issue Type: Bug
> Components: PB-API
> Affects Versions: 1.0.3
> Reporter: Vasily Ivanov
> Priority: Minor
> Fix For: 1.0.5
>
>
> 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.
> I think that addExists(subQuery) should check that subQuery doesn't have any
> orderby clause added and if it does throw more meaningful exception rather
> than "ORA-00907: missing right parenthesis".
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
