Re: Criteria.addOrCriteria() not commutative? (inner vs outer join)

[Jakob Braeuchi]

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

[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]