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:56:54 GMT
hi olli,

the problem is identified in method buildJoinTree of SqlQueryStatement.

...
                 SelectionCriteria c = (SelectionCriteria) o;
                 // BRJ: Outer join for OR
                 boolean useOuterJoin = (crit.getType() == Criteria.OR);

...

in the first query crit1 is the main criteria (not ORed) and is used to 
build the join, which will be an INNER join in this case. the OR-ed 
criteria crit2 does not influnce the join.

in the second query crit1 is the OR-ed criteria and thus useOuterJoin is 
set to true resulting in an OUTER join.

it looks like my solution to simply use an outer-join based on the 
curreent criteria was too simple :( may be we should check _all_ 
criteria instead.

jakob


Jakob Braeuchi wrote:

> 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
>>
>>
> 
> ------------------------------------------------------------------------
> 
> MySQL
> -----
> 
> query12:
> 
> 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' )
> 
> 
> query21:
> 
> 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...' )
> 
> 
> hsqldb
> ------
> 
> query12:
> 
> 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' )
> 
> 
> query21:
> 
> 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...' )
> 
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> ---------------------------------------------------------------------
> 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


Mime
View raw message