Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of jbryanlewis@gmail.com
 designates 209.85.220.222 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:date:message-id:subject:from:to:content-type;
        b=od2bZOhJWJXwNeU+Vef944Cnnfd1R4vl5/4qQNa629PQQhV8Ztso89q8MSexyY8o6Y
         +vPv34UKmogXITeL6CWTOtD28sJG867rJq+9Pq+vsIGdepwR2E2MczAGmNbvb5+JhIDo
         ZxNmuS2qUfHWnmqa+iuILzc2DDfpoUlkWjUG8=
MIME-Version: 1.0
Date: Fri, 26 Jun 2009 15:48:25 -0400
Message-ID: <86f0c84d0906261248o12e1e28ak3fe45b680a34005d@mail.gmail.com>
Subject: change in query behavior with orExp and a join
From: Bryan Lewis <jbryanlewis@gmail.com>
To: user@cayenne.apache.org
Content-Type: multipart/alternative; boundary=001636c5a82fa3aa1a046d459fcf

--001636c5a82fa3aa1a046d459fcf
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.

--001636c5a82fa3aa1a046d459fcf--