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 <jbryanlewi..mail.com> 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.
>
>
>
This archive was generated by hypermail 2.0.0 : Sat Jun 27 2009 - 08:08:35 EDT