db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Criteria.addOrCriteria() not commutative? (inner vs outer join)
Date Mon, 06 Oct 2003 19:16:16 GMT
hi oli,

i could reproduce this behaviour with hsqldb and mysql, see attachement.
but i do not yet know why the queries are reolved differently.

jakob

oliver.matz@ppi.de wrote:

> Hello,
> 
> I have a siutation where the two folloing snippets
> lead to non.equivalent queries:
> 
>     crit1.addOrCriteria(crit2);
>     Query qry12 = new QueryByCriteria(BookArticle.class, crit1);
> 
> vs
>     crit2.addOrCriteria(crit1);
>     Query qry21 = new QueryByCriteria(BookArticle.class, crit2);
> 
> In our project, there is a situation in which these
> two queries (executed in a syabse database) 
> result in different collections, but I have not mananged
> to reproduce this in the OJB test suite against HSQLDB.
> 
> Nevertheless, I would like to know whether this is intended.
> 
> Here are crit1 and crit2:
> 
>  Criteria crit1 = new Criteria();
>  crit1.addEqualTo("articleName", "Hamlet");
>  crit1.addEqualTo("productGroup.description", "Strange Books...");
> 
>  Criteria crit2 = new Criteria();
>  crit2.addEqualTo("stock", new Integer(32));
> 
> The two resulting queries are:
> 
> SELECT A0.Einzelpreis,A0.Kategorie_Nr,A0.ISBN,A0.AUTHOR,
>        A0.Auslaufartikel,A0.MindestBestand,A0.Lagerbestand,
>        A0.Artikel_Nr,A0.Liefereinheit,A0.BestellteEinheiten,
>        A0.Lieferanten_Nr,A0.Artikelname FROM BOOKS A0 
> INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr 
> WHERE (A0.Artikelname =  'Hamlet' ) AND A1.Beschreibung =  'Strange
> Books...'  
> OR  (A0.Lagerbestand =  '32' )
> 
> or, respectively:
> 
> SELECT A0.Einzelpreis,A0.Kategorie_Nr,A0.ISBN,A0.AUTHOR,
>        A0.Auslaufartikel,A0.MindestBestand,A0.Lagerbestand,
>        A0.Artikel_Nr,A0.Liefereinheit,A0.BestellteEinheiten,
>        A0.Lieferanten_Nr,A0.Artikelname FROM BOOKS A0 
> LEFT OUTER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr 
> WHERE A0.Lagerbestand =  '32'  OR  
> ((A0.Artikelname =  'Hamlet' ) AND A1.Beschreibung =  'Strange Books...' )
> 
> The difference is the type of the join (inner vs outer).
> In the sybase database, where a different join syntax is used,
> this may result in a different result set.
> 
> Why does OJB use two different kinds of join here?
> According to the SQL standard, are these queries supposed to
> be equivalent?  If so, is it a known bug in the sybase query engine?
> 
> Olli
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

Mime
View raw message