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 ol...@ppi.de on 2003/09/30 11:54:47 UTC
Criteria.addOrCriteria() not commutative? (inner vs outer join)
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
Re: Criteria.addOrCriteria() not commutative? (inner vs outer join)
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: Criteria.addOrCriteria() not commutative? (inner vs outer join)
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
>
>