This discussion is archived
1 7 8 9 10 11 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 120. Re: Treatment of zero-length strings as NULLs?
    45878 Newbie
    Currently Being Moderated
    >Surely you'll admit that that is inconsistent, even if you'd argue (and I'd be inclined to agree) that it isn't terribly important?

    Yes, it does appear inconsistent therefore confusing.
    SQL> select 'ABC' || ''              "MyColumn1",
      2         LENGTH('ABC')            "MyColumn2",
      3         LENGTH('')               "MyColumn3",
      4         LENGTH('ABC')+LENGTH('') "MyColumn4"
      5  from dual;
    
    MyColumn1            MyColumn2            MyColumn3            MyColumn4
    --------- -------------------- -------------------- --------------------
    ABC                          3                      
  • 121. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > NULL is "undetermined". This is sharply different to nothing and empty which are defined values.
    >
    An empty string is not a NULL because an empty string is a value: a string of length 0. These is
    quite different.

    Trying to put your argument into some kind of context. Let's say product/language ABC has a DATE data type that has also a length. The length determines whether or not the date includes century, year. month, day, hours, seconds and milliseconds. Silly internal structure, yes.

    However, using your argument - If such a DATE data type has a zero length is it null? is there a difference between a zero length date (no date value) and a null date (no date value)?

    The problem I have with your argument in this context is that you are using the physical representation/mechanism of the data type's storage to prop up your argument that a string data type with a zero length is different than a null string data type.

    What is the difference between a zero length string and a null string - just what is the length of a null string?

    Arguing that the length is unknown.. so what? A length of zero and an unknown length simply described the very same thing - that the string data type has no value.

    Yes, there are technical arguments around this - how this is represented in memory and how operators should treat such operands. (using COM objects in Win32 is even more complex than just IsNull or IsEmpty as there are more than just these 2 states)

    But implementing a relational design is not about technical issues of data representation. It is about WHAT data that attribute represents.

    IMO, implying in any way that a NULL string has a different meaning than an empty/zero-length string is a SERIOUS flaw in database design.
  • 122. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Personally I'd be happy for Oracle to treat '' and
    NULL as the same if it just supported these
    behaviours:

    1) WHERE middle_name = '' -- treat as WHERE
    middle_name IS NULL
    <snipped>
    2) LENGTH ('') = 0

    I agree Tony.

    There is no argument that Oracle is inconsistent in this aspect in some cases.

    But there is an argument to be made that zero length string (look Ma, no value!) has somehow a different mystical meaning that a null string (look Ma, no value!). :-)
  • 123. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    The problem I have with your argument in this context is that you are using the physical representation/mechanism of the data type's storage to prop up your argument that a string data type with a zero length is different than a null string data type.

    Not at all. It makes no difference to me if the empty string is a Pascal string with a length byte set to "0", or a C style nill terminated string where the first byte contains the terminator character, or some other representation.

    I'm just using the semantics of notional "empty string" vs "null".

    NULL has no type. It is not an empty string, or an empty date, or an empty anything.

    A NULL is a NULL. It is undetermined. A third logic value.

    An empty string is a known value that is (1) of a known type (2) of a known value : specifically, an empty string.

    I'm not going to support argument of OP that it is possible to construct 4VL around meaning of empty string vs NULL, because I think that is incorrect assumption for meaning of NULL cannot be determined or assumed to mean a specific thing.

    I cannot conceive of the notion of an "empty date", or "empty number" I don't believe such a thing is possible. I'm open for you to convince me, but still these things are not NULL by virtue of their known type alone.
  • 124. Re: Treatment of zero-length strings as NULLs?
    27876 Newbie
    Currently Being Moderated
    Right. I know what you did. Don't repeat it. You were just quoting the link where "YOU CAN DIE" was the subtopic of the topic "WHAT IF YOU DRIVE 200 KM/H?".
    I get a feeling that you are somehow getting paid to be rude in these otherwise volunteer for free forums.

    You seem to be assuming that it is just a matter of being able to drive fast having dirty reads available, there are no other consequences.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1729145331430
  • 125. Re: Treatment of zero-length strings as NULLs?
    Hans Forbrich Oracle ACE Director
    Currently Being Moderated
    But there is an argument to be made that zero length string (look Ma, no value!) has somehow a
    different mystical meaning that a null string (look Ma, no value!). :-)
    Reminds me of system we were working on in the 80s. There was a field that could be left blank, but the interpretation of the blank-edness seemed important at the time. So we contemplated creating a table that contained 'reasons for leaving field empty', which included things like (paraphrased)

    - user does not currently have that information
    - user refuses to provide that information
    - user promises to provide that information later
    - there is no information to provide
    - the information has been provided but it is, in fact, nothing
    - we do not know why the information has not been provided

    (In the end, we simply stored the updating userid and the reader went to originator for more info. <g>)
  • 126. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > A NULL is a NULL. It is undetermined. A third logic value.

    Heavens forbid!! Don't tell me there are more than 0's and 1's to this!!?

    ;-)

    > NULL has no type. It is not an empty string, or an empty date, or an empty anything.

    Interesting notion. Part of the problem I think is treating null as a value. I see it as a state - like infinity. Infinity is not some kind of artibrary value as that means you can have an "infinity + 1" value.

    If one sees a data type as describing a list of possible values (numbers, chars, etc), then what is a variable of that type when its "value set' is empty? Surely then the variable is without a value?

    What then make this state of the variable different than the same variable being in null state?

    The only difference I can see is purely technical. Simplistically. A pointer can be null. It does not point to anything. No malloc has been done. A pointer can have an address - thus the pointer is not null. Memory has been malloced but the memory struct has not been initialised.

    In this scenario there is a very clear technical difference.

    But on a database design level? A database development level? I cannot see how that technical difference can be abstracted into some conceptual issue that states that a variable with a zero length means something different than a null (no length) variable.

    You illustrated that nicely by calling is a "third logic value". There is nothing like that. We cannot now suddenly assign a 3rd mysterious value and say something is neither TRUE or FALSE.

    And yeah, I've just taken my lead pipe to Schrödinger's cat and it is dead.... ;-)
  • 127. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > I cannot conceive of the notion of an "empty date", or "empty number" I don't believe such a thing is possible.

    I gave Re: Treatment of zero-length strings as NULLs? earlier, although I can see why you might miss it in this thread.

    The requirement originally given for an empty string was the case where someone has no phone number and we want to record this information as a fact (there is definitely no phone number, not merely a gap where we wish we knew it). So, why should it be so hard to conceive of the case where there is definitely no date, as distinct from an unknown date? Alright, "empty" and "zero-length" don't make sense for a date so there would have to be some other internal magic value, but the point is that if it's so vital to be able to record "there is no phone number here", why is nobody demanding a way to record "there is no date here"? Because we can manage without, as we can with strings.
  • 128. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    On the subject of null string length and concatenation, I suspect this is why in some other RDBMSs the CONCAT() function returns null when one of its arguments is null, and it is Oracle's concatenation functions that are the problem. However as I think someone mentioned here recently (excuse me if I don't go looking for it in this thread) the way it actually works happens to be very useful in most cases.
  • 129. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    PMFJI...
    > An empty number is null, an empty date is null,
    why should an empty string be not null?
    Exactly. Why treat the VARCHAR2 data type any
    different in this aspect than a DATE or NUMBER
    data
    type? Neither a DATE or a NUMBER can be "empty"
    and
    null as two distinctly different states - so why
    would one want to apply that to the VARCHAR2 data
    type?
    *******************************************************************************
    *******************************************************************************
    * > Because the zero-length string (forget "empty" as it
    * > seems to be causing you confusion) '' is the VARCHAR2
    * > analogue of the NUMBER 0:
    *******************************************************************************
    *******************************************************************************

    BRAVO TONI !!! THAT'S THE POINT. THAT'S THE POINT.

    CD, please, make the poster with this banner.


    >
    'ABC' || '' = 'ABC'

    In Oracle, because NULL and '' are treated the same,
    we allow:

    'ABC' || NULL = 'ABC'

    ... which is inconstent with the defined behaviour of
    NULL in other cases.
    Absolutely right. As someone already wrote - an inconsistency par excellence. And one inconsistency implies another inconsistency, making the whole system "dummy".


    >
    Also:

    TRANSLATE('ABC','#ABC','#') = ''

    But in Oracle LENGTH(TRANSLATE('ABC','#ABC','#')) is
    NULL when it "should" be 0.
    Just what is the difference between an empty scalar
    versus a null scalar?

    Like I said, forget "empty". You may as well say
    what is the difference between "nothing" (number 0)
    and NULL.
    Why do you think it is a good idea? It is a good
    idea
    for VARCHAR2 only? And if so, why not for DATE and
    NUMBER and other scalar data types too?
    NUMBER has 0. There is no clearly defined equivalent
    for DATE, unless you arbitrarily pick one such as
    0001-01-01.

    I agree that Oracle does what it does and that we
    have to (and can) live with that, and indeed that it
    isn't a big deal to do so - maybe it even has some
    advantages. But you are wrong to suggest that
    '' and NULL really are the same thing conceptually.
    I'm confident that C J Date and J Celko would for
    once unite in their agreement on that point.
    Well spoken.

    Regards

    Albert
  • 130. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Sorry Tony, I misspelled your name. It must be read as:

    ----------------------------------------------------------------------------------------
    BRAVO TONY !!! THAT'S THE POINT. THAT'S THE POINT.
    ----------------------------------------------------------------------------------------

    Once again: CD, please, I would appreciate it if you make the poster with this banner.


    Albert
  • 131. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    Actually, concatenation of strings has the algebraic structure of a "monad". We have a an "identity" in the empty string. Hence 'xyz' || '' = 'xyz'.

    Properties of a monad:
    1. closed under the given operation (i.e.string1 || string2 is a string)
    2. has an identity
    3. associativity: (a||b)||c = a||(b||c)

    Integers, on the other hand, have the algebraic structure of a "group" under addition, with 0 as our identity. Hence, 4 + 0 = 4.

    Properties of a group:
    1. closed under the given operation (number1 + number2 is a number)
    2. has an identity
    3. associativity
    4. every element has an inverse: 4 + -4 = 0 (the identity)

    Cheers.
  • 132. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    I get a feeling that you are somehow getting paid to
    be rude in these otherwise volunteer for free
    forums.
    Not at all. You got a wrong feeling. Just wanted to clarify all that stuff.

    You seem to be assuming that it is just a matter of
    being able to drive fast having dirty reads
    available, there are no other consequences.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P1
    1_QUESTION_ID:1729145331430
    No, I am aware of other consequences. Consider the isolation level 0 just as:

    SET SYBASE TO MYSQL ON

    That's all.

    Regards

    Albert
  • 133. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    As I was reading through this thread (been about an
    hour so far) this is EXACTLY
    what I was thinking. The fact that Albert mentioned
    that the UI-operator pressed
    the space-bar to indicate "Hey, I know it is empty"
    sounded unusual to me. It could mean a
    multitude of things depending on your business.
    -------------------------------------------------------
    Customer phone is unlisted
    Customer does not own a phone
    Customer will call back with phone number
    Customer has international phone number (does not fit
    US phone mask)
    Customer lives in area with no phone service
    (South Pole or some such place)
    -------------------------------------------------------


    Miss Kate,

    you just cannot imagine how nicely your statements, quoted above, illustrates that you are totally out of the point.

    The semantics and multitude of possible meanings and reflections of the zero-length-string in our brains or businesses is not the point. You can take the empty-string in the meaning whichever you want, just as I took it to indicate that "Scott has not tel. device at all". Theoretically, although empty-string has not its own ascii representation code and must itself be represented by another marker internally at the physical level, it's quite possible to take it to represent say CHR(65), that is character 'A', so long as RDBMS is concerned and aware of it to be the zero-length-string at the logical level. But all this stuff has nothing to do with NULLs.

    The point is: empty-string is NOT NULL, just as 0 (zero) is NOT NULL, regardless of multi-valued logic on which NULLs are based and whether the whole that concept of NULLs and multi-valued logic is misguided or not:

    * (3VL) {true, false, unknown}
    * (4VL) {true, false, unknown, inapplicable}
    * (XVL) {true, false, unknown, inapplicable, maybe (probably), ...}.

    That's all. In figuring out all this multi-valued logic missleadings, don't confuse NOT NULL, as a value of attribute, with NOT NULL as a logical operation (NOT as logical operator, negation of NULL). NOT NULL as a value is always NOT NULL, but NOT NULL as negation of NULL is still NULL.

    Regards

    Albert
  • 134. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Personally I'd be happy for Oracle to treat '' and
    NULL as the same if it just supported these
    behaviours:

    1) WHERE middle_name = '' -- treat as WHERE
    middle_name IS NULL
    Since [ WHERE middle_name = '' ] returns no rows, I'd say that Oracle is CONSISTENT with its INCONSISTENCY. Treating it as [ WHERE middle_name IS NULL ], Oracle would be INCONSISTENT with its INCONSISTENCY, but it doesn't mean CONSISTENCY, just like (NOT) NULL, as negation of NULL, doesn't mean (NOT NULL), but is still NULL.

    2) LENGTH ('') = 0

    To say LENGTH('') is NULL implies that we don't now
    how many characters there are in a zero-length
    string, which is barmy.
    Perfectly spoken. Tony, you are doing well today.

    Regards

    Albert
1 7 8 9 10 11 26 Previous Next