db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vasily Ivanov (JIRA)" <j...@apache.org>
Subject [jira] Created: (OJB-120) addExists() builds invalid SQL if subquery has orderby clause
Date Wed, 23 Aug 2006 01:17:13 GMT
addExists() builds invalid SQL if subquery has orderby clause
-------------------------------------------------------------

                 Key: OJB-120
                 URL: http://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


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.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message