• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Sorting Nulls with Oracle
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


  • Follow-Ups:
    • Re: Sorting Nulls with Oracle
      • From: email@hidden
References: 
 >Sorting Nulls with Oracle (From: David Avendasora <email@hidden>)

  • Prev by Date: Re: recognising dead instances
  • Next by Date: Re: Updating EO by inserting new EO and archiving the old EO - any suggestions?
  • Previous by thread: Sorting Nulls with Oracle
  • Next by thread: Re: Sorting Nulls with Oracle
  • Index(es):
    • Date
    • Thread