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

Hi Jakob,

We are using Oralce 10g. It has problems with order by in sub queries.

Cheers,
  Vasily

On 8/25/06, Jakob Braeuchi <[email protected]> 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: [email protected]
> >> For additional commands, e-mail: [email protected]
> >>
> >>
> >>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [email protected]
> > For additional commands, e-mail: [email protected]
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>