Re: Warning about null and open question to Oracle

gordonh_at_acslink.net.au (Gordon E. Hooker) wrote:
>mccusker_at_fast.net (Jim McCusker) wrote:
>
>>In article <Pine.NXT.3.90.950613090306.17269C-100000_at_iluvatar>,
>> John Jones <john_at_iluvatar.tip.duke.edu> wrote:
[Much info about NULL's, relational theory, and Oracle's implementation  deleted for the sake of brevity]

Along the same lines,
Why do least() and greatest() treat nulls as significant while min() and max() dont? least(NULL,0,10) is NULL, but select min(DUMMY) from a table with 3 rows where DUMMY has the values (NULL,0,10) returns 0? Min() and max() are really min_known() and max_known(). It's easy to add "where DUMMY is not NULL" to a query, so they made it so you you don't need to for min() and max(). It's a thousand times harder to slap a nvl() around every column you want to check, so, of course, it's not optional.
The other problem is to figure out what to cast the NULL value to. The choice for numbers would be MINVAL - 1 (for least()), but that's not possible for sufficiently correct values of MINVAL ;-} . Any other number could exist in the actual data, and could cause an incorrect result to be returned.

Are least() and greatest() broken, or is it min() and max()?

-- 
Terry Greenlaw (on-site _at_ Lockheed)                      Encompass Technologies
z50816_at_mip.lasc.lockheed.com                                   greenlaw_at_crl.com
Politians should be changed regularly, like diapers, for the same reason. - RHF
 The torch of chaos and doubt -- this is what the sage steers by. - Chuang Tzu