cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Lewis <jbryanle...@gmail.com>
Subject change in query behavior with orExp and a join
Date Fri, 26 Jun 2009 19:48:25 GMT
We upgraded to Cayenne3 this week and things are going well, except for this
one query.

        Expression exp =
ExpressionFactory.matchExp("tradeSource.sourceCompany", fromCompany);
        exp =
exp.orExp(ExpressionFactory.matchExp("tradeSource.subjectCompany",
fromCompany));
        SelectQuery query = new SelectQuery(TradeSourceList.className, exp);
        List<TradeSourceList> results = dc.performQuery(query);

In Cayenne2 this generated:

SELECT t0.* FROM TRADESOURCELIST t0, TRADESOURCE t1 WHERE
t0.TRADESOURCE_ID = t1.TRADESOURCE_ID AND
((t1.NIC_ID_SOURCE = ?)
 OR (t1.NIC_ID_SUBJECT = ?))

Now we get:

SELECT t0.* FROM TRADESOURCELIST t0, TRADESOURCE t1 WHERE
(t1.NIC_ID_SOURCE = 5830) OR
(t1.NIC_ID_SUBJECT = 5830)
AND t0.TRADESOURCE_ID = t1.TRADESOURCE_ID

Note the different grouping of parentheses.  The effect is to fetch the
entire 6-million-row table, which we discovered from an
OutOfMemoryException.

This is on Oracle 8 so maybe other people aren't seeing it.  If so, sorry to
bring up that albatross again.  We can work around it by splitting the OR
into two separate queries, but I thought you'd want to know.  Even if it's
not worth fixing, could we get your opinion on how much we should worry
about our other queries?  Maybe we need to test only the small subset that
involve both an OR and a join.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message