Sorting Nulls with Oracle
Sorting Nulls with Oracle
- Subject: Sorting Nulls with Oracle
- From: David Avendasora <email@hidden>
- Date: Mon, 25 Jan 2010 11:14:04 -0500
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.
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 _______________________________________________
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