1 9 10 11 12 13 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 150. Re: Treatment of zero-length strings as NULLs?
        537882
        Hmm.. that is like saying that a glass without water (does not contain a value) is not empty (null) as it is full of air.

        Good analogy. It's the glass that's the difference.

        An empty glass of water is an empty glass of water, this is distinct from NULL which means there may be an empty glass, or a full glass of water, or a half empty, or half full glass of water, or no glass at all, but it is undetermined.

        We can compare two empty glasses of water and agree that they contain the same quantity of water (none).

        We can't agree the same about the quantity represented by:

        GLASS_WATER_VOLUME = NULL
        • 151. Re: Treatment of zero-length strings as NULLs?
          138365
          We can't agree the same about the quantity represented by

          GLASS_WATER_VOLUME = NULL
          But, I think you can both agree that there is a glass without water.

          Should one mark that as NULL or NOT NULL is only one convention.

          And if you are people in Oracle Corporation which deciding about this convention then I would understand your discussion ...

          Cheers!
          • 152. Re: Treatment of zero-length strings as NULLs?
            Hans Forbrich
            Unfortunately, volume is a numeric quantity.

            And this discussion has already determined that the numeric value of zero has a storage byte length of something greater than 0, whereas the null legitimately has [can have] a storage length of 0.


            Dates and numbers that are not set potentially do not use storage, and the null can be detected that way. Same thing happens for fixed length character types - if it does not contain the correct number of characters, it can not be accepted and therefore is null.

            However, varchar strings are different. A string may contain no characters and still, under some definitions, be a valid string - as some RDBMSs have decided. Or it may be considered null - as Oracle with varchar2 has decided. Internally, I suspect there would need to be an additional flag set to indicate the null-edness of of the data.


            It's too bad Oracle doesn't put this to rest. They could do that very simply ... introduce datatype varchar3. Internally, I suspect there would need to be an additional flag set to indicate the null-edness of of the data. If zero-length string, check flag.

            Then we'd be arguing whether setting the length to zero sets the field to null or not. <g>
            • 153. Re: Treatment of zero-length strings as NULLs?
              551707
              Hmm.. that is like saying that a glass without water (does not contain a value) is not empty (null) as it
              is full of air.
              That's your problem Billy. This example nicely illustrates it. A glass without water is EMPTY, but the content of the glass is NOT NULL. Saying that the content of the glass IS NULL is just like saying that we dont't know what is in the glass (water and how much water, or may be wine and how much wine, is it full or half-full or even empty etc.). Unlike NULL, EMPTY (or NOTHING if you like it more, though EMPTY is much better) is a KNOWN value. And as such - it's a value. You can even consider this example just as a numeric example: just saying that the volume of the glass is 0 (zero) liter of water. And you agree that zero is NOT NULL. Now, just imagine a string of random characters "laid down vertically" in the glass, decreasing its length (height) to zero-length (zero-hight). Is this real EQUIVALENCE between 0 (zero) and zero-length-string (empty string)?

              To William: this "empty" business can be applied only to strings (sequences) and streams. It's not applicable to DATEs and NUMBERs since there it is meaningless (at the logical level).

              There is a difference between numbers' and characters' representation and their semantics in our visual world (say on the physical screen). Note that characters are represented on the screen by itself. You see the character 'A' just like the character (letter) A. The character itself is its own picture. But you cannot see the number 5 on the screen because numbers are an abstract category. What you see on the screen is only the picture of number 5 used to represent it in our visual world. You see the character '5'.

              Just as character '0' is the picture of zero in alphanumeric world, so is zero the picture of zero-length-string in abstract numeric world. The problem is: you can see the picture of zero on the screen, but you cannot see the picture of zero-length-string in abstract numeric world because you cannot "enter" that world. You can only imagine it at our logical level of abstraction. Hope this helps.

              I return back now. Let's say that zero-length-string IS NULL. But please, be consistent then! It's not possible to have two heads on our shoulders. Only one. That implies we shouldn't write '' = '' anymore. Now, it should be written as
              '' IS '' 
              Take into consideration that our "favourite" expression
              ( 'ABC' || '' ) = 'ABC'
              is no more valid, neither as syntax nor as the result. Now, the syntax and the result are as follow:
              ( 'ABC' || '' ) IS ''
              just because
              ( 'ABC' || NULL ) IS NULL
              I am not in doubt you'll be very happy with this.


              Regards

              Albert
              • 154. Re: Treatment of zero-length strings as NULLs?
                William Robertson
                > this "empty" business can be applied only to strings (sequences) and streams. It's not applicable to DATEs and NUMBERs since there it is meaningless (at the logical level).

                You can't just say "it is meaningless (at the logical level)" after I've explained that it isn't. This all started with your GUI screen needing to be able to capture the fact that a person has no phone number, and for this to be different from the phone number field never having been entered and the phone number being unknown. What if instead of having no phone number they have no billing date? Why is a "we know there isn't one" string a real requirement that Oracle is stupid for not providing, and a "we know there isn't one" date is meaningless at the logical level? I suspect the answer is that you don't need dates as often as you need strings.

                Regarding IS '', IS on its own is not an operator. You are really scraping the barrel if that is supposed to be an argument about inconsistency.
                • 155. Re: Treatment of zero-length strings as NULLs?
                  6363
                  What if instead of having no phone number they have no billing date?
                  Exactly the point. No one is asking to be able to distinguish whether there is no shipped date because it is unknown and therefore null, or whether it is known to be empty because for example it has not yet been shipped.

                  The end result the user will see in these cases, and also in the cases of zero length and null strings, will be blanks. To apply the implicit distinguishing criteria will require code in all applications and reports that access it, tending to logic being decentralized and therefore duplicated, and somewhat not normalized. A view could be used to correct this if it is a concern.

                  If an application can be made to insert both '' and null to make this differentiation couldn't it also use some other values to make the same differentiation, spaces or 'NA' for example? If code still needs to be written to replace 'NA' with blank, then you are still no worse off than using '' and null.

                  Additionally as has also been pointed out many times, deliberately empty and unknown are not alway the only two reasons for data being absent in a business context. In all cases where the number of reasons for being unable to input a value are greater than two, values other than '' and null will need to be used anyway.

                  Practically there are not many reasons why it benefits to treat '' and null differently in a database. I have seen lots of programming language comparisons, storage and implementation details, all of which should not be a concern if data is to be managed efficiently.

                  Can someone tell me why, from a data management standpoint I need to distinguish between:

                  I have no string because there is no string.
                  I have no string because the string is null or unknown.

                  And not

                  I have no number because there is no number.
                  I have no number because the number is null or unknown.

                  Or

                  I have no date because there is no date.
                  I have no date because the date is null or unknown.

                  ?
                  • 156. Re: Treatment of zero-length strings as NULLs?
                    551707
                    You can't just say "it is meaningless (at the logical
                    level)" after I've explained that it isn't.
                    It is pretty obvious that the term "EMPTY" is the main cause of this ridiculous confusion. From that reason, please just forget "empty" forever. Instead, use the term "zero-length-string". From the same reason, forget any "touching business" and other sexy businesses.

                    From my logical point of view, I can argue and setup the definition that zero-length-string is the STRING whose LENGTH is a NUMBER equal to ZERO. I can also admit that this definition make the sense in my brain. Now, as a well-known zero-length-string terrorist, pardon - theorist, I'm going to ask you:

                    From your logical point of view (=> staying at the logical level of abstraction), what are:

                    1. zero-length-DATE

                    2. zero-length-NUMBER

                    as zero-length-string's equivalents, and what sense do they make in your brain (unless your arguments are not coming from Stephen King's novels or Steven Spielberg's movies) ?
                    What if instead of having no phone
                    number they have no billing date?
                    Why is a "we know
                    there isn't one" string a real requirement that
                    Oracle is stupid for not providing, and a "we know
                    there isn't one" date is meaningless at the logical
                    level? I suspect the answer is that you don't need
                    dates as often as you need strings.
                    You are totally missing the point. Sorry.

                    >
                    Regarding IS '', IS on its own is not an operator.
                    You are really scraping the barrel if that is
                    supposed to be an argument about inconsistency.
                    What do you want William?

                    If zero-length-string is supposed to be NULL, then ( ('ABC') || ' ' ) IS ' '. What's wrong? I didn't misspell anything. You must be consistent in inconsistency. Maybe you have two heads on your shoulder for your lefts and rights, each head with its own double-logics, but I have no skills for such a drilling.

                    Regards

                    Albert
                    • 157. Re: Treatment of zero-length strings as NULLs?
                      Billy~Verreynne
                      > A glass without water is EMPTY, but the content of the glass is NOT NULL.
                      Saying that the content of the glass IS NULL is just like saying that we dont't
                      know what is in the glass (water and how much water, or may be wine and how much
                      wine, is it full or half-full or even empty etc.).

                      You are missing the point. By pushing this analogy in the direction you did, a glass becomes a numeric data type. A data type that describes quantity. And in such a case an empty glass will have a zero quantity (value). Which means we are no longer dealing with an analogy about strings.

                      Question. As you keep insisting that there is and should be a difference between zero length string and a null string - why? What business problem do you want to solve by being able to differentiate between these two?

                      And to counter a continuing (and pointless) argument about the nature of "no value" vs "unknown value" of a string. (and why just a string? why not other data types too!?)

                      In your example of "representation and semantics of numbers and characters in our visual world" where you state that 5 is a numeric symbol that represents an abstract value.

                      I could state that in my system, the hash (#) character states that a string value is unknown. I can use the tilde (~) character to state that a value is known and there is none. I can use the exclamation(!) character to state the the string chars following is a "best guess" with a 50-50 chance of being correct. The ampersand (&) is used to state the string value was obtained from a dubious legacy system source. Etc. Etc.

                      I think that most would agree that, yes this can be done and these characters can be used to describe states (assuming those characters are never used as values themselves). I think most would also agree that this would be not the brightest thing to do. Software will have to interpret additional state information from the 1st char of the string. There is the risk that these characters could be used in future as valid input values for the string.

                      The point? It is a wrong approach. Relational design is specifically about reducing complexity of relationship - not about creating a single attribute that contains multiple data entities.

                      So instead of this flawed approach I described, the correct approach would be to have a separate attribute to carry the data about the state of string - should that state information be required.

                      So again - why do you have the need to differentiate between a zero length string and a null string? Why would you want to treat a string column in an Oracle table like that? What business problem are you trying to solve?

                      What makes your approach valid and not as flawed as the approach described above?
                      • 158. Re: Treatment of zero-length strings as NULLs?
                        537882
                        So again - why do you have the need to differentiate between a zero length string and a null string? Why would you want to treat a string column in an Oracle table like that? What business problem are you trying to solve?

                        There is a differentiation between the argument of whether an empty string '' is equivalent to NULL. (It isn't) and the OP concept that it is possible to assign different known meaning between the values of '' and NULL (you can't) The OP assumes that NULL necessarily implies that an actual value exists but is not know. The known value may actually be '', which is disregard. Hence all derived information based on this premise is open to be mistake.


                        It is possible that a value can be NULL due to "unknown data" but also take the value that OP assigns to '' doesn't exist.
                        • 159. Re: Treatment of zero-length strings as NULLs?
                          537882
                          Should one mark that as NULL or NOT NULL is only one convention.

                          This "convention" is part of the basis that the entire relational (and tenuously SQL) model is based on.

                          Oracle do not determine it.

                          To get this wrong implies everything built on it is fallible.

                          It is a very important point.
                          • 160. Re: Treatment of zero-length strings as NULLs?
                            537882
                            I think point is that expression is:
                            "IS NULL"
                            "IS" on it's own is not meaningful.
                            IS ''
                            This is just something make up with no meaning at all.
                            • 161. Re: Treatment of zero-length strings as NULLs?
                              William Robertson
                              As I said earlier I can see the point that a zero might suffice in the case of numbers ("there is no commission" = "commission = 0"; n + known nonvalue = n and so on). I'm not completely convinced but for the sake of world peace I won't argue this one.

                              I can also see some logic and usefulness in the "definitely no phone number" feature, if only out of respect for Codd, although it still seems rather arbitrary and messy and an unnecessary complication (defeating NOT NULL constraints and so on, although perhaps Sybase and others have a NOT EMPTY constraint type; also there is the question of how to handle this in CSV and fixed-length text files, although this is a practical difficulty rather than a logical one). However I have yet to be convinced that there is any kind of ANSI standards compliance issue in failing to support this feature or that it is any sort of big deal.

                              > From your logical point of view (=> staying at the logical level of abstraction), what are:
                              1. zero-length-DATE

                              For the very last time, a DATE has no meaningful length. Nobody is saying it has.

                              We are trying unsuccessfully to have a debate with you about why your application so urgently needs to distinguish between "there is no string" and "the string value has not been entered", but apparently not between "there is no date" and "the date value has not been entered". It is this ability to record a known non-value that is important, and not its length or physical implementation. Isn't that the whole point of supporting empty/zero-length strings in the database? Your argument seems to consist of saying that because a date has no meaningful length the situation cannot arise, telling everyone they are confused and missing the point, and rambling a bit about novelists called Steven.
                              • 162. Re: Treatment of zero-length strings as NULLs?
                                551707
                                A glass without water is EMPTY, but the content of the glass is NOT NULL.
                                Saying that the content of the glass IS NULL is just like saying that we dont't
                                know what is in the glass (water and how much water, or may be wine and how much
                                wine, is it full or half-full or even empty etc.).
                                You are missing the point.
                                Maybe I was really missing YOUR point, but not MY point in the context of our discussion. Your original statement (comparison) itself was a "contradictio in adjecto", apart from the fact the comparison itself had been missing The Flying Spontinalli's point. Look at it:

                                [The Flying Spontinalli]
                                This doesn't imply that an "empty string" is the same thing as a NULL. "Empty
                                string" is not a variable state, it is a value. A string. It is Empty. This is
                                it's defined value.
                                [your comparison]
                                "Hmm.. that is like saying that a glass without water (does not contain a value) is not empty (null) as it is full of air."

                                1. "without water" is suggested to be the same as "does not contain a value"
                                2. "empty" is suggested to be the same (synonym) as "null"
                                3. "full of air" is suggested to be equivalent to (null <=> empty), though "air" has nothing to do with "water", so is totally out of the context

                                So, read my "comparison" as:

                                Saying that "without water" means NULL is just like saying that we don't know how much water there is in a glass without water (forget "glass" as a container for water).

                                I'll respond you tomorrow.

                                Albert
                                • 163. Re: Treatment of zero-length strings as NULLs?
                                  537882
                                  Also in case of SQL Types as I mention earlier.

                                  POINT(NULL,NULL) is clearly not a point, but Oracle treat POINT(NULL,NULL) as NOT NULL. An "empty" point. If '' is NULL then POINT(NULL,NULL) should also be NULL.
                                  • 164. Re: Treatment of zero-length strings as NULLs?
                                    27876
                                    Isn't that something like:
                                    ptr = malloc(....) ; /* if malloc() succeeds, ptr is not null */
                                    *ptr = NULL ;
                                    1 9 10 11 12 13 Previous Next