I've fixed it for our purposes, so there's no urgency about this. (We might
be the last Oracle8 users on the planet.) I made the Oracle8Adapter return
a new Oracle8QualifierTranslator. It merely overrides doAppendPart() so it
can wrap the qualifierBuffer in parentheses if it sees that the buffer
contains an "OR".
This might affect the OpenBase adapter too, since it's using the same
legacy-join-syntax code. (Or maybe not... OpenBase might handle the series
of AND and OR clauses more intelligently.) I've entered a low-priority
Jira, CAY-1247.
On Sat, Jun 27, 2009 at 8:08 AM, Bryan Lewis <jbryanlewi..mail.com> wrote:
> 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 : Mon Jun 29 2009 - 11:23:42 EDT