This discussion is archived
1 2 3 4 5 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 30. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    I didn't say that. You did say that. But again: why
    Sybase? I am not in love with Sybase. I only respect
    it.
    I had to work with T-SQL which I would call an abonimation. Each product has it's irks, but I rather deal with Oracle's NULL string handling than with the half baked SP language from Sybase. BTW: Did they finally manage to have a decent transaction model, or are dirty reads still an option?

    C.
  • 31. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    I didn't say that.
    You said:
    That's the point - Oracle's spaghetti code, millions of lines of code based on screwed up definition of NULL.
    As you said yourself, spaghetti code is a pejorative term used to describe code that is poorly structured and thus hard to maintain. So presumably the reason why the null semantics of CHAR and VARCHAR2 values can not now be altered is because of the poor structure of Oracle's code. That's what I'm disagreeing with.

    As regards relics of the 80s, the other RDBMS products you mention all came later, so whether Oracle is right or wrong it's a bit unfair to complain that it's different from SQL Server and MySQL etc. I can see some logic in both approaches (unlike the 2 + 2 example). I happen to prefer Oracle's, probably because it's the one I'm used to and it's simpler, but the fact is that it works the way it works and the most constructive approach until they introduce a VARCHAR datatype that works differently (they've been saying they might for 15 years so I'm not holding my breath) would surely be to accept it, rather than going on about how insane it is and how Oracle's codebase is poorly structured.
  • 32. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Holly Mary! I am feeling bad blood. Once again, just to clarify: I used the term "spaghetti code" in the pejorative meaning "Huge, tons of software, millions of line of code scattered all over the world and served in each and every one restaurant, just like spaghetti from Italian Cuisine", NOT in its primary meaning in hackers world - "poorly structured code". It was pretty clear from the context. If not, it's perfectly clear now.

    The fact that thousands of applications exists and is based on screwed up definition of NULL has nothing to do with "poorly structured code". I've never said that. You did.

    Albert
  • 33. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Please, avoid the never-ending confrontation Oracle-Sybase. This is the least I wanted. Also, this is not the right place for that.

    You talk about "abonimation"? Hmm...

    > ..., but I rather deal with Oracle's NULL string handling than with the half baked SP
    language from Sybase.

    You are welcome. But it doesn't mean you are very smart.

    > BTW: Did they finally manage to have a decent transaction model, or are dirty reads still an
    option?

    Dirty reads were never an option in Sybase. Where have you read this? There were some problems with locking that was fixed a long time ago.

    Albert
  • 34. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    No bad blood, I'm just only aware of one meaning of spaghetti code, and while we're at it, pejorative ;)
  • 35. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    No problem. Knowledge has no limit. Now you know the second meaning of "spaghetti code": mine.

    Albert
  • 36. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    NULL is fundamentally a conceptual problem, in SQL standard, and for all SQL database implementors.

    All vendor implementation specific problems arise from the conceptual problems.

    Preference between Oracle and MS treatment of NULL is like to choose between be hung or be shot.
  • 37. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > NULL is fundamentally a conceptual problem, in SQL
    standard, and for all SQL database implementors.

    All vendor implementation specific problems arise
    from the conceptual problems.

    Preference between Oracle and MS treatment of NULL is
    like to choose between be hung or be shot.

    Really? Be hung or be shot. Who said that? Winnetou or Old Shatterhand? Knowledge has no limit. If you spend just a few seconds to google the term "null" or "null set" you will surely find this:

    -----------------------------------------------------------------------------------------------------------
    1. null set

    In mathematical sets, the null set, also called the empty set, is the set that does not contain anything. It is symbolized ø or { }. There is only one null set. This is because there is logically only one way that a set can contain nothing.

    The null set makes it possible to explicitly define the results of operations on certain sets that would otherwise not be explicitly definable. The intersection of two disjoint sets (two sets that contain no elements in common) is the null set. For example:

    {1, 3, 5, 7, 9, ...} {2, 4, 6, 8, 10, ...} = ø

    The null set provides a foundation for building a formal theory of numbers. In axiomatic mathematics, zero is defined as the cardinality (that is, the number of elements in) of the null set. From this starting point, mathematicians can build the set of natural numbers, and from there, the sets of integers and rational numbers.
    -----------------------------------------------------------------------------------------------------------
    2. null (SQL)

    Attributes in tables in SQL database management systems can optionally be designated as NULL. This indicates that the actual value of the column is unknown or not applicable.

    In SQL DBMSs, the result of testing NULL = NULL is neither TRUE nor FALSE but rather unknown. This is because one unknown value might not be equal to another unknown value. Thus to test if a column is NULL or is not NULL, a special syntax must be used: either 'column IS NULL' or 'column IS NOT NULL'. Because of this, special care must be taken when joining tables on columns that can be null.

    Furthermore, because NULL means "unknown value", negation of NULL results in NULL... you don't know the value so you don't know the negation of the value either. Therefore, the expression 'NOT NULL' evaluates to NULL.

    Strictly spoken, NULL is not a value since it is an inherent property of values that they can be compared with other values of the same data type.

    -----------------------------------------------------------------------------------------------------------

    Really a conceptual problem.

    Cheers

    Albert
  • 38. Re: Treatment of zero-length strings as NULLs?
    27876 Newbie
    Currently Being Moderated
    Dirty reads were never an option in Sybase. Where have you read this?
    <quote>
    the application should query the table using isolation level 0. Other applications that require data consistency, such as deposits and withdrawals to specific accounts in the table, should avoid using isolation level 0.
    .....
    Scans at isolation level 0 do not acquire any read locks for their scans, so they do not block other transactions from writing to the same data, and vice versa.
    .....
    Because scans at isolation level 0 do not acquire any read locks, it is possible that the result set of a level 0 scan may change while the scan is in progress.
    </quote>

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug860.htm
  • 39. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    Set theory is great stuff and served a semantic role in the creation of the relational calculus, however the RDBMS NULL doesn't have much of anything to with the empty set in set theory.

    Consider:

    1. {}={} but equality does not hold for NULL. In fact {} has all of the behavior of any other set, it simply doesn't contain anything. No one would argue that NULL behaves the same as any other scalar.
    2. The empty set is a very explicit concrete item, while NULL is considered ""unknown"

    That said, differentiating between an empty string and NULL in relational calculus is nowhere specified in ANSI SQL.

    I appreciate your frustration. I feel the same frustration when I work with a technology that is new to me and the mental map I use to predict behavior doesn't quite work.

    Oh, and to Kamal's point, in SQL Server as well as Sybase if you do not set the isolation level to at least "read commited" then you get dirty reads. Likewise you'll see dirty reads in DB2 in "read uncommited" mode.

    Cheers,
    Scott
  • 40. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    Here are the relevant parts of the ANSI SQL standard

    http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

    r) null value (null): A special value, or mark, that is used to indicate the absence of any data value.

    ...

    A null value is an implementation-dependent special value that
    is distinct from all non-null values of the associated data type.
    There is effectively only one null value and that value is a member
    of every SQL data type. There is no <literal> for a null value,
    although the keyword NULL is used in some places to indicate that a
    null value is desired.

    [Scott: hmm, does the empty string constitute a literal? perhaps]
  • 41. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    Please, avoid the never-ending confrontation
    Oracle-Sybase. This is the least I wanted. Also, this
    is not the right place for that.

    You talk about "abonimation"? Hmm...
    After working with it, that's my opinion on that topic.
    > ..., but I rather deal with Oracle's NULL string
    handling than with the half baked SP
    language from Sybase.

    You are welcome. But it doesn't mean you are very
    smart.
    Coming from you, this could still be a compliment.
    > BTW: Did they finally manage to have a decent
    transaction model, or are dirty reads still an
    option?

    Dirty reads were never an option in Sybase. Where
    have you read this? There were some problems with
    locking that was fixed a long time ago.
    So they've finally introduced MVCC or the like? Writers don't block readers anymore? What about the spaghetti code that relied on this?

    C.
  • 42. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    I agree with Scott that the null set is not what we are discussing here.

    I didn't see anything in the section on "null (SQL)" about your requirement to be able to distinguish between "touched" and "non-touched" columns, or for that matter any explanation of why this is apparently such a monumental issue only for string values and not for numbers, dates and so on.
  • 43. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    I don't really see your point.

    You can google for NULL? Great.

    I'm not denying that the SQL standard includes NULL (*), I'm just raising out the well established argument that inclusion of NULL at all is widely regarded as a major failing of the SQL "implementation" of the relational model.

    In bickering over which failed vendor interpretation of a flawed concept is least ambiguous, the point is missed.

    (*) We should all praise our lords that the SQL3 standard initial draft didn't go ahead allowing multiple user defined NULL types.
  • 44. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    I think we should all press for reality to be made less ambiguous in order to simplify the implementation of nulls in relational databases.

    In the meantime, perhaps the ANSI SQL standard should be extended to include the Zen concept of Mu.
1 2 3 4 5 26 Previous Next