Re: Sorting Nulls with Oracle
Re: Sorting Nulls with Oracle
- Subject: Re: Sorting Nulls with Oracle
- From: Chuck Hill <email@hidden>
- Date: Mon, 25 Jan 2010 12:17:40 -0800
On Jan 25, 2010, at 8:14 AM, David Avendasora wrote:
Hi all,
Aaron Rosenzweig and I discussed the issue of how to properly
implement control over how Oracle and EOF sort nulls at this month's
WONoVA (http://groups.google.com/group/webobjects-nova).
The only time, as far as I know, where it becomes necessary to worry
about this is when you are using ERXBatchingDisplayGroup and the
result set is larger than the batch size. Oracle and EOF don't agree
on how to do this so you will get results that are confusing to
users if there are nulls in a column being sorted on.
Basically, what we've come down to is that there are 3 different
behaviors that have to be handled - you should only need to figure
out which one you want to use and then implement it for your
application:
1) Nulls Last - Oracles default - null values will always be LAST in
the result set, regardless of sort direction (asc, desc).
2) Nulls First - null values will always be FIRST in the result set,
regardless of sort direction (asc, desc).
3) EOF-like - null values will be sorted as though it were the
smallest possible value. First when sorted ascending, Last when
sorted descending.
To accomplish #1 and #2 you have to create new ComparisonSupport
classes in EOF, otherwise EOF will resort the result set according
to the conflicting "nulls are the smallest possible value" rule.
To accomplish #3 Oracle's default sorting behavior needs to be
modified. Luckily Oracle allows you simply to add NULLS FIRST or
NULLS LAST to the end of a ORDER BY statement. For example "ORDER BY
NAME ASC NULLS FIRST". I have modified the EROraclePlugin to add
NULLS FIRST to ascending sorts and NULLS LAST to descending sorts.
Simple.
Well, not so fast. There are two issues that still need addressing:
1) Oracle query results are not deterministic if you are sorting on
a column with non-unique data in it. Because of this, the same DB
record could show up in the DisplayGroup in multiple batches and
some may never show up. The only way around this is to always sort
on some guaranteed unique column, or by possibly implementing more
advanced Oracle functions in the plugin. See here for more detail: http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
The existing code in the ERXSQLHelper.OracleSQLHelper
limitExpressionForSQ() method allows for several different methods
of trying to deal with this, but none are a complete solution. At
this point, our business logic allows us to add a unique column to
the sort so the existing code works just fine, but eventually
someone is going to get bit by this, but I'm not sure what to do
about it other than document the shortcoming in a comment in the
source code.
I would expect that this is true for most/all databases. And I also
suspect that this is a common issue when using ERXBatchingDisplayGroup
and commonly overlooked. Forcing the last sort value to be the PK
would address this, I think. You will probably have to wrestle EOF if
the PK is not exposed.
Chuck
2) One solution does not fit everyone (ie. Aaron and I have
different business requirements for null sorting), so we need a way
of specifying which sorting method an application uses. Obviously
the Property seems the most logical, but since the EROraclePlugin
doesn't depend upon ERExtensions, and I'm assuming it probably
shouldn't, we can't use Wonder's property handling. How should this
be done? Suggestions?
Dave
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden