You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Vasily Ivanov <ba...@gmail.com> on 2006/08/21 06:02:21 UTC
addExists(subQuery) builds invalid SQL if subquery has orderby
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: addExists(subQuery) builds invalid SQL if subquery has orderby
Posted by Vasily Ivanov <ba...@gmail.com>.
Hi Jakob,
We are using Oralce 10g. It has problems with order by in sub queries.
Cheers,
Vasily
On 8/25/06, Jakob Braeuchi <jb...@gmx.ch> wrote:
> hi vasily,
>
> forgot to post the sql and the testcase.
>
> SELECT A0.Kategorie_Nr
> FROM Kategorien A0
> WHERE EXISTS (
> SELECT B0.Artikel_Nr
> FROM Artikel B0
> WHERE (B0.Kategorie_Nr = A0.Kategorie_Nr) AND
> B0.Artikelname LIKE '%Tofu%' ORDER BY 1)
>
> this sql breaks in hsqldb.
>
>
> public void testSubQueryExists()
> {
> ReportQueryByCriteria subQuery;
> Criteria subCrit = new Criteria();
> Criteria crit = new Criteria();
>
> subCrit.addEqualToField("productGroupId",
> Criteria.PARENT_QUERY_PREFIX + "groupId");
> subCrit.addLike("articleName", "%Tofu%");
> subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
> subQuery.setAttributes(new String[]{"articleId"});
> subQuery.addOrderByAscending("articleId"); // PROBLEMS in hsqldb
>
> crit.addExists(subQuery);
> Query q = QueryFactory.newQuery(ProductGroup.class, crit);
>
> Collection results = broker.getCollectionByQuery(q);
> assertNotNull(results);
> assertEquals(1, results.size());
> }
>
> jakob
>
> Jakob Braeuchi schrieb:
> > 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: ojb-user-unsubscribe@db.apache.org
> >> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >>
> >>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: addExists(subQuery) builds invalid SQL if subquery has orderby
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi vasily,
forgot to post the sql and the testcase.
SELECT A0.Kategorie_Nr
FROM Kategorien A0
WHERE EXISTS (
SELECT B0.Artikel_Nr
FROM Artikel B0
WHERE (B0.Kategorie_Nr = A0.Kategorie_Nr) AND
B0.Artikelname LIKE '%Tofu%' ORDER BY 1)
this sql breaks in hsqldb.
public void testSubQueryExists()
{
ReportQueryByCriteria subQuery;
Criteria subCrit = new Criteria();
Criteria crit = new Criteria();
subCrit.addEqualToField("productGroupId",
Criteria.PARENT_QUERY_PREFIX + "groupId");
subCrit.addLike("articleName", "%Tofu%");
subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
subQuery.setAttributes(new String[]{"articleId"});
subQuery.addOrderByAscending("articleId"); // PROBLEMS in hsqldb
crit.addExists(subQuery);
Query q = QueryFactory.newQuery(ProductGroup.class, crit);
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
assertEquals(1, results.size());
}
jakob
Jakob Braeuchi schrieb:
> 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: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: addExists(subQuery) builds invalid SQL if subquery has orderby
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org