cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Lewis <jbryanle...@gmail.com>
Subject Re: change in query behavior with orExp and a join
Date Sat, 27 Jun 2009 12:08:01 GMT
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 <jbryanlewis@gmail.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.
>
>
>

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