Derby not honoring order by clause (inslight requested)

[Bob Morley <rm...@emforium.com>]

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.
-- 
View this message in context: http://n4.nabble.com/Derby-not-honoring-order-by-clause-inslight-requested-tp1835819p1835819.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.

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

[Bob Morley <rm...@emforium.com>]

BJ Freeman wrote:
> 
> 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>
> 

Hi BJ -- I think you misunderstood.  I am using the entity model via the
findByAnd method on the standard delegator implementation.  When debugging I
stepped down to the generation for the sql statement and just posted the raw
sql that Ofbiz was generating.

BTW - I had edited my original post to fix a mis-spelling in the title, so I
just quoted your post back to this thread.
-- 
View this message in context: http://n4.nabble.com/Derby-not-honoring-order-by-clause-insight-requested-tp1835819p1836051.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.