This discussion is archived
1 5 6 7 8 9 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 90. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    True. but of course, there is no need for vendors to imeplement the physical representation of database as a literal translation of logical representation. Most if not all major commercial SQL DBMS do however use very literal translation (with the exception, maybe, of Sybase IQ) , this is how this kind of "advantage" of null appear. You can get the same advantage by full normalisation.
  • 91. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Well, you can take as many left and right turns in your discussion and
    dissection of the statement
    <snipped>

    Well Kamal, some seem to firmly believe that 3 left turns do equal a single right turn in all aspects.

    Kind of like have a very large 2 (written in a huge bold font) plus an equally large 2 equals 5.

    One cannot argue that kind of "logic" - not worth the effort.
  • 92. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Not one of the relavant RDBMS systems on the market today handles zero-length
    strings as NULLs (Sybase ASE, MS SQL Server, IBM DB2, MySQL, PostgreSQL etc.).
    These RDBMS are perfectly aware of the following possibilities:
    <snipped>

    Which smells. Why?

    The inference that there is some meaning to a lack of a value. I see it as a pretty idiotic to infer that:
    <value> = fine, we have a value for that attribute
    <empty> = fine, there is no value for the attribute in this tupple
    <null> = problem, there should be a value for this attribute in this tupple but we do not know what it is

    > 3. Scott has the telephone device, but the operator doesn't know its number:

    What in the case where the operator knows the area code, but not the rest of the number? Or can tell you what the number is, but is unsure if it is still valid? Or can tell you with a 50% degree of certainty that it is the correct number?

    The correct relational design will be to have a separate attribute to carry that data. In the simple case, create an attribute called TELEPHONE_KNOWN and put a Y/N char(1) constraint on it.

    Inferring information in a RDBMS about data based on nonsensical states such as whether an attribute is null or empty? That is birdseed IMO.
  • 93. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    No, no, you were, in fact, discussing the isolation level 0. The link you gave "Dirty reads" is a
    subtitle of "Choosing an isolation level" parent title.
    Well, you can take as many left and right turns in your discussion and dissection of the statement but
    if you are NOT denying that dirty reads happen (no matter in which isolation level) then your statement that "dirty
    reads were never and option in sybase" is not correct. Maybe you mean that they were never an option but were rather
    a necessity in sybase?
    Without the isolation level 0 dirty reads don't exist.
    And hence means you agree dirty reads exist (in which isolation level they exist and in which they do not,
    is not the question here). The question is simply -

    "Do they or do they not exist in that product?"
    YES, YES, YES they EXIST, EXIST, and EXIST... Hundred times: THEY EXIST (and I pray the Lord let them continue to exist at least until the end of this century)

    But neither as an option, nor as a necessity to bypass something that could be considered as design flaw or limitation in the product, as it was the point of Mr/Mrs/Miss CD in his/her initial post. Dirty reads are just a consequence of the option called "transaction isolation level 0" that is a real feature, big deal, trade-off between speed and consistency. I repeat: the FEATURE, not a design flaw. Has your car an option for you "to die" or it has an option "to drive 200 km/h"? If you drive 200 km/h, your chance to die is pretty real, but it's your own choice and your own responsibility. If Oracle decided not to let its customers to drive above 120 km/h because it's too dangerous, I'd be screaming: "Hey Larry, for God's sake, give me an option to drive fast on my own responsibility and don't try to protect me against myself and my own will".

    BTW, what RDBMS is considered to be the fastest database in the world? Oracle, Sybase, Ingres? None. MySQL is the fastest database in the world, but only if MyISAM table handler (flat ISAM files) was used, not InnoDB (transaction safe). AFAIK, InnoDB company was yet another Oracle's acquisition made in 2005. Stop the enemy before he stops you! Who care for NULLs.

    Yes, yes, perfectly clear to me, you were just quoting the link. So, is this finally clarified, or will we continue going nowhere ?

    Again, I was just quoting the link where dirty reads is the topic of discussion.
    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?".


    Regards

    Albert
  • 94. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Other questions in T-SQL could be: Did they introduce array structures after 12.5?
    Is the text datatype for variables finally supported?
    Don't waste your (valuable) time. Any further question about Sybase not related to the topic of this thread will be ignored. The right place for you is Sybase Forum. Or just download a free ASE-15 developer edition (25 user connections) with all licensable options enabled and find out yourself.

    2. I confirmed that readers block writers? Forever?
    As long as transaction lasts, I'd say.
    And you believed that? No, it was just my New Year's
    Gift to you. I hope, you enjoyed it. I could also
    have written it as: "No, now brains block jerks
    forever", but was afraid you'd never enjoy it.
    I'm afraid I can't follow you.
    Maybe you really can't. Have you a sense of humour? Or are you playing your games? Here is what I originally wrote: "No, now readers block writers forever. It was Wall Street's feature request." And you caught this literally? Not as a joke? Ok, my bad. This joke should have been written as:

    "No, now brains block jerks forever. It was Wall Street's feature request."

    Did Sybase finally change it's transaction model, or not?
    Ignored.

    For additional fighting go to the forums.sybase.com
    and say there what you have. This is not the place
    for it.
    Agreed. So do you have any questions on Oracle topics,...
    Sure, read the title of the thread.

    ... or do you want to keep complaining about the differences between the Oracle DBMS and other products?
    I really don't. You do.

    Almost forgot: Here's a Happy New Year the PL/SQL way. ;-)
    I'm so sorry CD, too late. I didn't know the tread existed. Wow!!! I didn't even know the PL/SQL is so suitable to write greeting cards, banners and posters. Would you mind if I ran your code on my machine to impress my wife?

    Since I'm not so smart as you, here is my humble contribution using poor Transact-SQL and SQSH interactively (since I failed to do it with SQL*Plus):

    Greeting Card 1.
    pubs2:1> print ""
    pubs2:2> print "HAPPY NEW YEAR"
    pubs2:3> print ""
    pubs2:4> go

    HAPPY NEW YEAR

    pubs2:1>
    Greeting Card 2.
    pubs2:1> create procedure greeting
    pubs2:2> as
    pubs2:3> select greet "I WISH YOU" from greeting_table
    pubs2:4> go
    pubs2:1> greeting
    pubs2:2> go

    I WISH YOU
    --------------
    HAPPY NEW YEAR  

    pubs2:1>
    where greeting_table is one-row, one-column "dummy" table containing the banner text.

    Regards

    Albert
  • 95. Re: Treatment of zero-length strings as NULLs?
    551707 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.
    Hi Scott,

    you are absolutely right. I just wanted to emphasize the importance of set theory as a prerequisite to understanding relational theory. And not only from that reason, what will be clear later. The concept of NULL in relational theory has no equivalence with the definition of null-set (or empty-set) in set theory. Note that NULL is not a value. It is only a marker for missing value, and as such, totally indenpendent of the data type. Empty-string (not to be confused with the blank space ' ', that is CHR(32)), however, is a value having the datatype alphanumeric (char/varchar or alpha/varalpha). It's a string of no characters that represents itself by its length that is equal to 0 (zero). We CAN write ' ' = ' ', but CANNOT write NULL = NULL.

    Consider this. The equation
    0 (zero) = LENGTH('')
    is perfectly valid, but
    0 (zero) = LENGTH(NULL)
    is invalid, since the length of NULL is unknown, or not applicable, or even pointless. This implies that there is no equivalence between ' ' and NULL and they cannot be considered as synonyms. In other words: ' ' IS NOT NULL. Let me be more precise.

    The number 0 (zero) is defined as the cardinality of the empty set { }. This implies that the length of empty-string can also be defined as the cardinality of the empty set { }. The empty-string itself is a string having 0 (zero) characters.

    The same role the number 0 (zero) plays in numeric world, the empty-string could play in alphanumeric world (defined as an "alphanumeric, visual description" of the cardinality of the empty set { }). Zero means 0 elements of something in sense of quantity. The empty-string, containing literally 0 characters, can be considered as a "picture" of 0 (zero) in alphanumeric world. Thus, we can setup the real equivalence between 0 (zero) and ' ' (empty-string), which also implies the equivalence between addition operation for 0 (as the mathematical operation in numeric world) and concatenation operation for ' ' (as the string operation in alphanumeric world):
    ( a + 0 + 0 + 0 + ... = a ) <=> ( alpha || '' || '' || '' || ... = alpha )
    That's real equivalence between 0 (zero) and '' (empty-string). The equivalence itself implies:
    [ ( p = 0 ) IS NOT NULL ] => [ ( alpha = '' ) IS NOT NULL ]
    Note, however, that 0 and ' ', though equivalents, are not identical or equal. We cannot write:
    0 = ''
    since they are different data types.

    Here is the third Codd's rule as explained in James R. Groff & Paul N. Weinberg "SQL: The Complete Reference" under the topic "Codd's Twelve Rules".

    "3. Systematic treatment of null values. Null values (distinct from an empty character string or a string of blank characters and distinct from zero or any other number) are supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way, indenpendent of the data type."


    Regards

    Albert
  • 96. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    Don't waste your (valuable) time. Any further
    question about Sybase not related to the topic of
    this thread will be ignored. The right place for you
    is Sybase Forum. Or just download a free ASE-15
    developer edition (25 user connections) with all
    licensable options enabled and find out yourself.
    Why should I, if I've got an Sybase expert (you!) available, who can show me the technical merit of said product, right?
    Maybe you really can't. Have you a sense of humour?
    Sure I do. Oh, you were trying to be funny ... I'd say you have to do a bit more practice then.
    Sure, read the title of the thread.
    That has been discussed already, anything else?
    ... or do you want to keep complaining about the
    differences between the Oracle DBMS and other
    products?

    I really don't. You do.
    Really? I can't find any quote where I write "Larry, for God's sake ...".
    I'm so sorry CD, too late. I didn't know the tread
    existed. Wow!!! I didn't even know the PL/SQL is so
    suitable to write greeting cards, banners and
    posters. Would you mind if I ran your code on my
    machine to impress my wife?
    Go ahead. If there's someone who can be impressed by you, you shouldn't neglect that opportunity.
    Since I'm not so smart as you, here is my humble
    contribution using poor Transact-SQL and SQSH
    interactively (since I failed to do it with
    SQL*Plus):
    You could always read the concept guide to improve your skills.

    C.
  • 97. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    What in the case where the operator knows the area code, but not the rest of the number? Or can tell you what the number is, but is unsure if it is still valid? Or can tell you with a 50% degree of certainty that it is the correct number?


    Or better case is, how can differentiate between <null> value in column, and <null> value from selection over table outer join.
  • 98. Re: Treatment of zero-length strings as NULLs?
    45878 Newbie
    Currently Being Moderated
    >What in the case where the operator knows the area code, but not the rest of the number?
    Or can tell you what the number is, but is unsure if it is still valid? Or can tell you
    with a 50% degree of certainty that it is the correct number?


    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)

    You could even go one step further and create a MISSING_PHONE_REASON code and if the
    phone number IS NULL then the MISSING_PHONE_REASON must be chosen from a drop-down list.

    Albert:
    Also, depending on the type of data where the space-bar is pressed (phone number versus
    middle name), the ascii 32 value (which is space-bar) could mean many things as well.
    What if the middle name had a space-bar entered into it? Could mean:

    Customer does not have a middle name
    Customer does not wish to disclose their middle name
    Customer has more than one middle name (my aunt-by-marriage has about seven)

    -- or heaven forbid --

    Customer parent was a techno-nerd hippy and gave them "blank" (ascii 32) as a middle name

    From a design standpoint, one does not always see the complete picture for the meaning
    of an empty field (as Billy mentioned regarding inferences).

    --Miss Kate                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 99. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    Here is the third Codd's rule...

    You are not breaking ground to say that Oracle is not a relational DBMS.

    You don't need to go past rule 1 before Oracle fail to meet Codd's defined of RDBMS.

    That is not to say a SQL DBMS is not a good thing.
  • 100. Re: Treatment of zero-length strings as NULLs?
    6363 Guru
    Currently Being Moderated
    An empty number is null, an empty date is null, why should an empty string be not null?

    One reason might be the amount of additional of code it generates helps keep the software industry going.

    While it is true there are different kinds of reasons behind unknown information, there are not just two of them. If you need to distinguish between different reasons for data being absent I would think an empty string and null would not be good choices for possible values. They tend to confuse the kind of simple ad hoc query interfaces that databases were designed to support.

    ANSI is a committee, the sort of place where sets might have left and right sides.

    I turned the page around and got all confused.
  • 101. Re: Treatment of zero-length strings as NULLs?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    An empty number is null, an empty date is null, why
    should an empty string be not null?
    What is an empty number, or an empty date?

    There are strings with no characters that can be called empty strings.

    To call something "empty" you need that that thing must be a collection, a container or a sort of aggregate object.

    Anyway I think that it could be a good thing to have a string datatype that is not considered as null when it doesn't contain any character, without considering the meaning of null.

    Where I live, we give to null the meaning of something that hasn't got a meaning value, but sometime an empty string is a meaning value!

    Bye Alessandro
  • 102. Re: Treatment of zero-length strings as NULLs?
    521710 Newbie
    Currently Being Moderated
    cd you should upgrade your handle to "dvd" cause Albert burn out your capacity of 700 MB for Oracle & SQL ;)
  • 103. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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?

    > There are strings with no characters that can be
    called empty strings.

    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.

    > To call something "empty" you need that that thing
    must be a collection, a container or a sort of
    aggregate object.

    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]scalar data type - not a vector type.

    Just what is the difference between an empty scalar versus a null scalar?

    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.

    > Anyway I think that it could be a good thing to have
    a string datatype that is not considered as null when
    it doesn't contain any character, without considering
    the meaning of 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?

    Just what is the difference in meaning between "nothing/empty" versus "null"?

    > Where I live, we give to null the meaning of
    something that hasn't got a meaning value, but
    sometime an empty string is a meaning value!

    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".
  • 104. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    cd you should upgrade your handle to "dvd" cause
    Albert burn out your capacity of 700 MB for Oracle &
    SQL ;)
    CD are my initals, not a reference to a storage media or change directory command. ;-)

    C.
1 5 6 7 8 9 26 Previous Next