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