This discussion is archived
1 6 7 8 9 10 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 105. Re: Treatment of zero-length strings as NULLs?
    Tony Andrews Explorer
    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:

    '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.

    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.
  • 106. Re: Treatment of zero-length strings as NULLs?
    521710 Newbie
    Currently Being Moderated
    CD are my initals, not a reference to a storage media
    or change directory command. ;-)
    Ah, really? I thought that is abbreviation for "corps diplomatique" or luminous intensity unit - candela ;)
    Then maybe changing your name and surname will be reasonable option ...
  • 107. Re: Treatment of zero-length strings as NULLs?
    94799 Explorer
    Currently Being Moderated
    Utterly hilarious.
  • 108. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > What is an empty number, or an empty date?

    These would be values where the fact that there isn't a value is to be recorded as a known fact, as distinct from a mere gap where the value is unspecified. For example in a mobile phone system, the date a customer ported their number in from another operator, when it is known that they did not. An empty number, if such a thing existed, could stand for the ID of the previous service provider. This is what zero-length-string theorists demand for character datatypes but apparently are happy to get by without for dates and numbers.
  • 109. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    Then maybe changing your name and surname will be
    reasonable option ...
    Using my pl/sql script for new year wishes, I'll be brief ;-) ...
     *    * ******
     **   * *    *
     * *  * *    *
     * *  * *    *
     *  * * *    *
     *   ** *    *
     *    * ******
    C.
  • 110. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    This is what zero-length-string theorists ...
    First time I read your answer like this:

    This is what zero-length-string terrorists ... ;-) ...

    C.
  • 111. Re: Treatment of zero-length strings as NULLs?
    Tony Andrews Explorer
    Currently Being Moderated
    > What is an empty number, or an empty date?

    These would be values where the fact that there isn't
    a value is to be recorded as a known fact, as
    distinct from a mere gap where the value is
    unspecified. For example in a mobile phone system,
    the date a customer ported their number in from
    another operator, when it is known that they did not.
    An empty number, if such a thing existed, could stand
    for the ID of the previous service provider. This is
    what zero-length-string theorists demand for
    character datatypes but apparently are happy to get
    by without for dates and numbers.
    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

    That would resolve a very common code-porting issue for those who do that sort of thing.

    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.
  • 112. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Tony, it does not make any sense to me to equate the number 0 to a zero length string.

    As for an empty string versus null - my problem is interpreting a string attribute without an explicit value as having a different meaning when equal to '' and when null.

    I think that any relational design that treats '' as having one meaning and null another meaning as flawed. To me it is like moving an attribute into an object name and instead of having an INVOICES relation, creating an INVOICES_2006 relation and removing the YEAR attribute from the relation.

    It simply does not feel right to me. Not from a design perspective. Not from a coding and performance and maintenance perspective.
  • 113. Re: Treatment of zero-length strings as NULLs?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    Oh fu*k we're allways falling on offending each other!!
    > An empty number is null, an empty date is null,
    why should an empty string be not null?
    A string without characters and an unexisting string are two different things in a procedural OO language like PL/SQL and it's not terminology.
    Not entirely correct. Arguing that a string is a
    collection of characters and thus can be initialised
    (not null) but "empty" is treading in very murky
    waters.
    But if I consider a string as an object there is a reson to mark such a difference.
    I didn't say that a string is a collection but I sayd that a string with no characters can be called empty. I didn't say it's the proper way to call it!
    Exactly. And a VARCHAR2 data type is defined as a
    variable-length character string. It is a [url
    http://en.wikipedia.org/wiki/Scalar_%28computing%29]sc
    alar data type - not a vector type.
    Who's talking about varchar2?

    In strict programming terms that comes down to a
    variable pointer that points to a memory structure
    that is null - thus the argument that pointer is
    "empty" as it does exist. I.e. pointer is valid and
    not null, but without a value that can be
    dereferenced versus an unitialised pointer that is
    very obviously null.
    And this is how varchar2 works.
    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?
    For the reason that a null date and a null number can't be interpreted as an empty date or as an empty number because they are not entities of this world while a string without characters is an existing entity.
    Just what is the difference in meaning between
    "nothing/empty" versus "null"?
    The same difference that exists between a stupid and nothing. The fact that you can't find anything useful inside theese two entities doesn't tell me that they're the same thing!

    Yeah.. well, seeing that I do not live in Disney
    World I find that very hard to grasp ito relational
    design and concepts. I do not see how one can ever
    infer any information of an attribute depending on
    whether that attribute is not-null, null or "empty".
    You're not in Disney World?
    Well fine, but you're in something different from the real world anyway!
    Interpreting an empy thing and nothing as the same thing is a reason to consider it limited.

    Think about it.

    Bye Alessandro
  • 114. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    Just what is the difference in meaning between
    "nothing/empty" versus "null"?
    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.

    Two strings of 0 length are equal to each other, but the result of NULL=NULL or ''=NULL is undefined.
  • 115. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > For the reason that a null date and a null number can't be interpreted as an empty date or as an empty number because they are not entities of this world while a string without characters is an existing entity.

    Just what we need, an appeal to mysticism ;)

    Actually I think you are arguing from practicality, in that a zero-length string is reasonably simple to implement and represent, while there is no obvious way to provide the equivalent for other datatypes. While that is a fair point, what bothers me is why this is such a huge "Oracle has it wrong" deal when no empty string is provided, preventing us from making the vital distinction between known non-value and unknown value, when everyone seems to get by just fine when it comes to dates and numbers.
  • 116. Re: Treatment of zero-length strings as NULLs?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    It is not mysticism.

    The domain of string datatype contains the string ''
    from

    http://en.wikipedia.org/wiki/String_%28computer_science%29

    where they say as well that
    The empty string is the unique string over Σ of length 0, and is denoted ε or λ.

    The domains of number and date datatypes do not contain their null equivalent so we don't have this analogy on their side.

    There are many side effects for this interpretation.

    When you want a not null value on varchar2 fields you can put there '' because it is considered null while it is a valid string.

    and
    SQL> declare
      2  begin
      3     dbms_output.put_line('-- doesn''t put the new line');
      4     dbms_output.put_line('');
      5     dbms_output.put_line('--');
      6  end;
      7  /
    -- doesn't put the new line
    --

    Procedura PL/SQL completata correttamente.

    SQL> -- NO ONE RETURNS A ROW
    SQL>
    SQL> select *
      2  from dual
      3  where dummy <> '';

    Nessuna riga selezionata

    SQL>
    SQL> select *
      2  from dual
      3  where not (dummy = '');

    Nessuna riga selezionata

    SQL>
    SQL> select *
      2  from dual
      3  where dummy = '';

    Nessuna riga selezionata

    SQL>
    SQL> select *
      2  from dual
      3  where not (dummy <> '');

    Nessuna riga selezionata

    SQL>
    SQL> -- BUT THE TABLE IS NOT EMPTY
    SQL>
    SQL> select *
      2  from dual;

    D
    -
    X

    SQL>
    SQL> -- AND FINALLY THIS IS FINE
    SQL>
    SQL> select *
      2  from dual
      3  where dummy||'a' <> ''||'a';

    D
    -
    X

    SQL>
    This is what I'm talking about!

    Bye Alessandro
  • 117. Re: Treatment of zero-length strings as NULLs?
    45878 Newbie
    Currently Being Moderated
    >...why this is such a huge "Oracle has it wrong" deal when no empty string is provided, preventing us from making the vital distinction between known non-value and unknown value, when everyone seems to get by just fine when it comes to dates and numbers.

    This is the achilles' heel to the pro-Albert argument, if someone who agrees with OP Albert on this issue, please explain why you can live without this all-important distinction with let's say, a DATE data type? Otherwise, I believe you have lost the debate of why Oracle does not distinguish the difference between an empty field VARCHAR2 and a null VARCHAR2.
  • 118. Re: Treatment of zero-length strings as NULLs?
    Tony Andrews Explorer
    Currently Being Moderated
    This is the achilles' heel to the pro-Albert
    argument, if someone who agrees with OP Albert on
    this issue, please explain why you can live without
    this all-important distinction with let's say, a
    DATE data type? Otherwise, I believe you have
    lost the debate of why Oracle does not distinguish
    the difference between an empty field VARCHAR2 and a
    null VARCHAR2.
    You are all missing the point when you talk about DATEs. All DATEs have the same structure (year, month, day etc.) - there is no "zero-length date" to confuse with NULL, so the potential confusion does not arise. But variable-length character strings such as VARCHAR2 have a variable length (no kidding!), and the issue is whether the length of a string value of type VARCHAR2(n) can range from 0 to n or only from 1 to n. It is generally regarded in the big wide world outside the Oracle camp that it can range from 0..n.

    If 'ABC' || '' = 'ABC', why isn't LENGTH('ABC')+LENGTH('') = LENGTH('ABC')?

    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?
  • 119. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    If 'ABC' || '' = 'ABC', why isn't LENGTH('ABC')+LENGTH('') = LENGTH('ABC')?

    It's not actually inconsistent since the 'ABC'||'' is not an algebraic expression.

    LENGTH('ABC'||'') = LENGTH('ABC') is consistent with 'ABC'||'' = 'ABC'
1 6 7 8 9 10 26 Previous Next