Re: Derby not honoring order by clause (inslight requested)

[BJ Freeman]

if I read this correct
summary:
using the entity engine you get the correct results
using SQL you don't.
since the entity engine returns the correct results what is it sending
to the dB and how is it sorting.
If you are sending sql(sql pass through) then you by-passing ofbiz and
just the JBDC and databse are concerned.

=========================
BJ Freeman
http://bjfreeman.elance.com
Strategic Power Office with Supplier Automation <http://www.businessesnetwork.com/automation/viewforum.php?f=93>
Specialtymarket.com <http://www.specialtymarket.com/>

Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man
Linkedin
<http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro>


Bob Morley sent the following on 4/10/2010 11:07 PM:

> I was working on a unit tester as part of OFBIZ-3670.  This unit tester makes
> use of a RollbackTestCase that starts a transaction in setup and then rolls
> it back in teardown.  At any rate, I was a testing a method that will do a
> select against an entity and includes an order by clause "from_date DESC".
> The entities are created inside this same transaction as where I fetch them,
> but regardless if I changed it to "from_date ASC" derby was always returning
> back the records in the same order.
>
> Here is a snippet if you do not want to apply the patch --
>
>         Timestamp date01 = UtilDateTime. nowTimestamp();
>         Timestamp date02 = UtilDateTime.addDaysToTimestamp(date01, 5);
>         getDelegator().create("Agreement", UtilMisc.toMap("agreementId",
> "TEST_AGMT_01", "agreementTypeId", "EULA", "description", "test01",
> "fromDate", date01));
>         getDelegator().create("Agreement", UtilMisc.toMap("agreementId",
> "TEST_AGMT_02", "agreementTypeId", "EULA",  "description", "test02",
> "fromDate", date02));
> ...
> gptList = delegator.findByAnd(entityName, UtilMisc.toMap(mainId,
> mainValueId), UtilMisc.toList("-fromDate"));
>
> Am I missing something obvious here?  I can't imagine this is a derby bug;
> but the dates look good on debugging (5 days apart) and the findByAnd
> definition returns back a list of two entities (with their fromDate fields
> properly set).  If I change the "-fromDate" to just "fromDate" it makes no
> difference on the result.
>
> When debugging I worked down to the generated SQL (it was not getting the
> values from cache) ...
>
> SELECT AGREEMENT_ID, PRODUCT_ID, PARTY_ID_FROM, PARTY_ID_TO,
> ROLE_TYPE_ID_FROM, ROLE_TYPE_ID_TO, AGREEMENT_TYPE_ID, AGREEMENT_DATE,
> FROM_DATE, THRU_DATE, DESCRIPTION, TEXT_DATA, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM OFBIZ.AGREEMENT
> WHERE (AGREEMENT_TYPE_ID = ?) ORDER BY FROM_DATE DESC
>
> For now I commented out my assertion that I am getting back the exepected
> GenericValue so that the unit test passes; but in reality it is not
> returning the "latest" as it is designed to do.