A little extra debugging info on this.... it doesn't happen on Postgres which uses the more modern join syntax.
SELECT * FROM TRADESOURCELIST t0 JOIN TRADESOURCE t1 ON (t0.TRADESOURCE_ID = t1.TRADESOURCE_ID) WHERE (t1.NIC_ID_SOURCE = ?) OR (t1.NIC_ID_SUBJECT = ?) On Fri, Jun 26, 2009 at 3:48 PM, Bryan Lewis <[email protected]> wrote: > 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. > > >
