Andy Hassall wrote:
> On Fri, 11 Mar 2005 08:49:18 -0800, DA Morgan <damorgan_at_x.washington.edu>
> wrote:
>
>
>>Frank van Bortel wrote:
>>
>>
>>>Oracle treats '' as NULL - this is not ANSI SQL compliant,
>>>as correctly stated before.
>>
>>I do not believe you are correct. It is Oracle that is ANSI
>>compliant and SQL Server that is not.
>>
>>Reference:
>>Tom Kyte
>>Expert one-on-one Oracle
>>Chapter 1, page 38
>
>
> The example given on page 38 does not deal with Oracle's treatment of '' as
> NULL - rather it demonstrates the converse, that it treats NULL as NULL in the
> Standard sense when used in comparisons. The example given shows a port from
> SQL Server where the developers were surprised that a condition involving NULL
> (and Tom explicitly writes that the variable was set to NULL, not empty string)
> was never true.
>
> Apparently in some configurations of SQL Server, a comparison of a non-empty
> string with NULL can be treated as if the NULL were an empty string and so
> yields false, i.e. the slightly forced expression NOT('not null' != NULL) is
> TRUE.
>
>
>>"SQL is supposed to operate under tri-alued logic and Oracle implements
>>Null values the way ANSI SQL requires them to be implemented."
>
>
> Indeed, in the example given, the behaviour expected by the T-SQL developers
> was wrong compared with the Standard and general conventions of NULL in SQL,
> and Oracle's was correct - Boolean expressions involving comparison with NULL
> end up UNKNOWN. 'not null' != NULL is neither true nor false; it is unknown.
> NOT('not null' != NULL) is similarly unknown.
>
>
>>and
>>
>>"For example SQL Server supports the ANSI method of Null comparison,
>>just not by default (it would break thousands of existing legacy
>>applications built on that database)."
>>
>>Just a wild hunch ... but I doubt Tom got it wrong.
>
>
> Tom didn't get it wrong. However, there are additional issues with Oracle's
> treatment of empty string versus NULL that are the subject of the rest of this
> thread, that aren't covered by the example given in that page of One-on-one.
>
> Oracle's non-Standard treatment of empty strings as NULL, except when used in
> concatenation where NULL is treated as either an empty string or NULL depending
> on whether it would produce an empty string again, is never going to change, as
> again it would break thousands of applications and it's Just How Oracle Works.
> But denying it's a difference compared with the Standard doesn't help; Oracle
> documents this as a difference compared with Standard (more precisely - they
> claim full compliance with SQL-92 Entry Level, but only partial compliance with
> SQL-92 Transitional due to this issue), and there's references in the thread I
> referenced in another reply as well.
>
> This doesn't make Oracle "bad", and that's one of the major points of that
> chapter of One-on-one; different databases work differently, and you should
> always try to fully understand the way the one you're using (e.g. Oracle) works
> so you can write effective code that takes advantage of the database, not works
> against it.
>
> If you want another Tom Kyte quote, try here:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:7967215895365162726::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5984520277372
>
> "'' when assigned to a varchar2(1) becomes '' which is a zero length string and
> a zero length string is NULL in Oracle (it is no long '')"
>
> And, with another bit of a typo:
>
> "the docs are telling you want ansi says vs what we do. "
>
> (Presumably 'want' should be 'what').
Thanks for the clarification and the link to AskTom.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Mar 11 2005 - 16:58:54 CST
