You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bryan Lewis <jb...@gmail.com> on 2009/06/26 21:48:25 UTC
change in query behavior with orExp and a join
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.
Re: change in query behavior with orExp and a join
Posted by Bryan Lewis <jb...@gmail.com>.
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 <jb...@gmail.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 <jb...@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.
>>
>>
>>
>
Re: change in query behavior with orExp and a join
Posted by Bryan Lewis <jb...@gmail.com>.
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 <jb...@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.
>
>
>