1 10 11 12 13 14 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 165. Re: Treatment of zero-length strings as NULLs?
        551707
        Question. As you keep insisting that there is and should be a difference between zero
        length string and a null string - why?...
        S T O P! You are in contradiction with Oracle itself. Oracle did adopt the concept of NULL (null as UNKNOWN, MISSING, INAPPLICABLE data), but screwed up the concept afterwards, stealing my favourite string - zero-length-string - from its native environment and forcing it to be NULL. See

        http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4174

        That way, we have lost both: zero-length-string and NULL.

        Just a trivial example:
        ---------------------------------------
        There is an employees table with first_name and last_name columns. As everyone is supposed to have both first and last names, it's smart to make both columns non-nullable (required), just to avoid eventual operator's mistake to leave any column blank. And then comes a zero-length-string terrorist, an employee called 'Yoyo'. Neither first, nor last name, just his full name. No problem for Codd: - 'Yoyo' will be his last_name, and his first_name will be my favourite string. In Oracle, there is NO WAY to INSERT Yoyo's record in a consistent way at all (remember, first_name is NOT NULL). You have to be surgeon here and cut 'Yoyo' in halves: last_name = 'Yo', first_name = 'yo' and concat them later in every select statement. Or, make first_name column nullable, as you always do, thus forcing a poor database design. As I said, just a trivial example.

        ... What business problem do you want to solve by being able to differentiate between
        these two?
        It has nothing to do with business problems (at least not directly and not at this level of discussion). It has something to do with reality and, what is most important, with overall system consistency, either with the rest of the world outside Oracle, or with Oracle itself. Consistency facilitates and makes solving business problems easier. Oracle is inconsistent and confusing with Oracle itself. Today, server SQL is in bad mood and is telling me
        zero-length-string IS NULL
        Tomorrow, PL/SQL will be in good mood telling me
        zero-length-string IS NOT NULL
        Am I supposed to believe them both? No, I'd say. Then, whom to believe?

        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!?)
        Just forget "empty" and "touching" forever. Zero-length-string is a string of CHAR/VARCHAR2 DATATYPE. You cannot insert CHAR/VARCHAR2 in DATE column or NUMBER column. Even if you "could", there would be no sense to do it.

        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.
        You are welcome. If your customers need that informations, why not. Just like Amazon.com does with shipment dates when you order a book. But this has nothing to do with NULLs and zero-length-strings. Neither the concept of NULL nor conception of zero-length-string were introduced to potentiate a variety of possible descriptions of scalar's meaning. NULL is just a marker for MISSING VALUE, nothing else. Zero-length-string is just a value, nothing less.


        Regards

        Albert
        • 166. Re: Treatment of zero-length strings as NULLs?
          Billy~Verreynne
          A regular animal farm with a certain animal being more equal than others.

          Translation: one scalar data type is treated different as others as it may have a zero length and others not.

          Saying that, quote "NULL is just a marker for MISSING VALUE, nothing else. Zero-length-string is just a value, nothing less., unquote... well that sounds like a bunch of baloney to me.

          Why?

          The same argument applies to any other scalar data type. Any other data type can have a NO-VALUE as oppose to a MISSING/UNKNOWN/NULL value. As William has shown with numerous examples. Yet, you have chosen to discard that as not applicable to any other scalar data type, but strings.

          Again, that is baloney. (said with a vivid mental picture of the pigs adding "but some animals are more equal than others" to the graffiti on the barn door that says all animals are equal)
          • 167. Re: Treatment of zero-length strings as NULLs?
            491689
            A bit long but interesting discussion...
            Oracle is inconsistent and confusing with Oracle itself.
            In my opinion, if we want to keep this discussion more or less "scientific" we should be rather picky about terminology.

            "Consistency" and "confusion" - what do these terms mean???

            Any formal system is said to be consistent if it doesn't contain contradictions. In other words, there is no proposition A for which both "A" and "not A" is provable (besides "true" and "false", for trivalent logic we have to consider "unknown" as well). For demonstration of inconsistency, it's necessary and sufficient to find some proposition A for which more than one of possible values ("true", "false" and "unknown") can be derived at the same time.

            Staying within the bounds of this definition, we have to admit that Oracle treats nulls and so-called "empty strings" in a consistent manner. If someone claims that he's still aware of some inconsistency, he (she) should be able to show, to demonstrate this inconsistency (by providing reproducible test-case) or stop making such claims.

            On the other hand, "confusion" usually refers to human being mental phenomena and has something in common with "misunderstanding", or "perplexity". In any case, "confusion" is not a property of some formal system, technical equipment or computer program - it's inherently tied with "understanding" and "misunderstanding".

            And like many other things in human life "confusing" is subjective term. What seems "confusing" for you can make a perfect sense for someone else.
            You complain that nulls and "empty strings" in Oracle make you "confused". Many other people don't have any problems with it. So what?

            Kind regards.
            • 168. Re: Treatment of zero-length strings as NULLs?
              551707
              Any formal system is said to be consistent if it doesn't contain contradictions.
              In other words, there is no proposition A for which both "A" and "not A" is provable
              Absolutely right.

              For demonstration of inconsistency, it's necessary and sufficient to find some
              proposition A for which more than one of possible values ("true", "false" and "unknown")
              can be derived at the same time.
              Absolutely right. Aristoteles would claim: "Tertium non datur" (Aristoteles, Metaphysik),
              not knowing for "unknown".

              If someone claims that he's still aware of some inconsistency, he (she) should be able
              to show, to demonstrate this inconsistency (by providing reproducible test-case) or stop
              making such claims.
              Absolutely right.

              Here is an easily reproducable test-case as demonstration of Oracle's inconsistency with
              Oracle itself.
              DECLARE
                zero_length_string CHAR(1) := '';
              BEGIN
                IF( zero_length_string IS NULL )
                THEN
                  dbms_output.put_line( 'zero-length-string IS NULL' );
                ELSE
                  dbms_output.put_line( 'zero-length-string IS NOT NULL' );
                END IF;
              END;

              zero-length-string IS NOT NULL



              CREATE TABLE contradiction (
              zero_length_string CHAR(1)
              );
              INSERT INTO contradiction(zero_length_string) VALUES('');
              SELECT (CASE
                        WHEN zero_length_string IS NULL THEN 'zero-length-string IS NULL'
                      ELSE
                        'zero-length-string IS NOT NULL'
                      END) "Is this a contradiction?"
              FROM contradiction;

              Is this a contradiction?
              --------------------------
              zero-length-string IS NULL
              Staying within the bounds of this definition, we have to admit that Oracle treats
              nulls and so-called "empty strings" in a consistent manner.
              Obviously not, Mr. Andrew.

              On the other hand, "confusion" usually refers to human being mental phenomena and
              has something in common with "misunderstanding", or "perplexity".
              Absolutely right. Oracle makes human beings (programmers, developers, DBAs etc) to be confused.

              So, since you admit that confusion usually refers to human being mental phenomena, I am going to ask you for help: please, don't make confusion here anymore. We have pretty enough confusion even without you. Read the whole thread or leave it forever.


              Kind regards

              Albert
              • 169. Re: Treatment of zero-length strings as NULLs?
                551707
                A regular animal farm with a certain animal being more equal than others.
                Translation: one scalar data type is treated different as others as it may
                have a zero length and others not.
                You are deeply in Orwell. That might be the problem.

                Saying that, quote "NULL is just a marker for MISSING VALUE, nothing else.
                Zero-length-string is just a value, nothing less., unquote... well that sounds
                like a bunch of baloney to me.
                I could admit I understand you.
                I could also admit understanding the syndrome "to-be-born-in-oracle".

                Why?

                The same argument applies to any other scalar data type. Any other data type can
                have a NO-VALUE as oppose to a MISSING/UNKNOWN/NULL value. As William has shown
                with numerous examples. Yet, you have chosen to discard that as not applicable
                to any other scalar data type, but strings.
                Apart from being deeply immersed in George Orwell's world, you have a big problem with
                datatypes (understanding the nature of various datatypes).

                >
                Again, that is baloney. (said with a vivid mental picture of the pigs adding "but
                some animals are more equal than others" to the graffiti on the barn door that
                says all animals are equal)
                Billy, I cannot help you. And, I'd say, nobody here can help you. Your problem is not
                solvable inside this forum. Sorry. Try with C.J. Date.

                Regards

                Albert
                • 170. Re: Treatment of zero-length strings as NULLs?
                  castorp
                  For example, the blank field in an
                  application form that represents a person's mobile
                  tel. number could mean:

                  (1) it is known fact that the person HAS NOT tel.
                  number at all (the field is touched by empty string)

                  (2) the person's tel. number is UNKNOWN, NULL (the
                  field is not touched)
                  I still don't understand why you only complain about this with regards to the VARCHAR2 datatype. Taking a column called "BIRTHDAY", with the datatype DATE. You cannot implement the following:

                  1) the person refuses to reveal his/her birthday (the field is "touched" with which value?)
                  2) the person's birthday is not know (NULL)

                  which is essentially what you want to do with the VARCHAR2 field. So why is it that you don't complain about the DATE datatype not having an "empty" Date. The same is true for a NUMBER column (e.g. income "refuses to reveal his/her income vs. income not known"

                  Thomas
                  • 171. Re: Treatment of zero-length strings as NULLs?
                    William Robertson
                    > 1) the person refuses to reveal his/her birthday (the field is "touched" with which value?)
                    2) the person's birthday is not known (NULL)

                    Well, it could be argued that the date is simply unknown in both cases and should therefore be null. I suggested examples of a billing date and phone number transfer date. We could also try the date of marriage for someone who died unmarried, but unfortunately it will make no difference here. Albert's argument will remain that anyone with a different point of view from his is an idiot who is missing the point.
                    • 172. Re: Treatment of zero-length strings as NULLs?
                      William Robertson
                      My guess is that in the case of
                      INSERT INTO contradiction(zero_length_string) VALUES('');
                      SQL is parsing the '' into a NULL which is standard behaviour and consistent with LENGTH('') etc. For example if you did this:
                      INSERT INTO contradiction(zero_length_string, string_length) VALUES('', LENGTH(''));
                      what would you expect to appear in the second column?

                      However in the PL/SQL assignment the compiler knows it is dealing with a CHAR variable and initialises it, causing it to be blank-padded to its declared length. As the documentation says,

                      When you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length.

                      Possibly PL/SQL is wrong here, because the length of '' is unknown according to Oracle's standard semantics, not < 1. PL/SQL seems to be interpreting '' as Albert's empty string:
                      SQL> DECLARE
                        2      v_unassigned CHAR(1);   
                        3      v_null       CHAR(1) := NULL;
                        4      v_empty      CHAR(1) := '';
                        5  BEGIN
                        6      DBMS_OUTPUT.PUT_LINE('Length of v_unassigned: ' || LENGTH(v_unassigned));
                        7      DBMS_OUTPUT.PUT_LINE('Length of v_null: ' || LENGTH(v_null));
                        8      DBMS_OUTPUT.PUT_LINE('Length of v_empty: ' || LENGTH(v_empty));
                        9  END;
                       10  /
                      Length of v_unassigned:
                      Length of v_null:
                      Length of v_empty: 1
                      Other more serious examples usually brought up in this sort of debate are DECODE, CONCAT and || which treat nulls as empty strings, and multi-column unique constraints which by not counting null values treat them as all the same. In all these cases though, the existing behaviour seems overwhelmingly more useful than the theoretically pure one.
                      • 173. Re: Treatment of zero-length strings as NULLs?
                        491689
                        Here is an easily reproducable test-case as demonstration of
                        Oracle's inconsistency with Oracle itself.
                        DECLARE
                        zero_length_string CHAR(1) := '';
                        BEGIN
                        IF( zero_length_string IS NULL )
                        THEN
                        dbms_output.put_line( 'zero-length-string IS NULL' );
                        ELSE
                        dbms_output.put_line( 'zero-length-string IS NOT NULL' );
                        END IF;
                        END;
                        Well, I read this whole thread and I already saw this example. But let me repeat: Oracle consistently treats empty string as null, and the code above does not demonstrate any inconsistency. It only demonstrates the fact that Oracle blank-pads character strings when assigning them to CHAR variables in PL/SQL.

                        Consider:
                        SQL> var x char(1)
                        SQL>
                        SQL> column dmp format a100
                        SQL>
                        SQL> select dump(:x) dmp from dual;

                        DMP
                        -----------------------------------------
                        NULL

                        SQL> exec :x := ''

                        PL/SQL procedure successfully completed.

                        SQL> select dump(:x) dmp from dual;

                        DMP
                        -----------------------------------------
                        Typ=96 Len=1: 32

                        SQL>
                        As it can be easily seen, variable X was initially NULL and after we assigned '' to it, Oracle blank-padded it to its full length.
                        And yes, it's obvious that after such an assignment X will contain one blank character (and thus it won't be null):
                        SQL> select 'A' || :x || 'B' Result from dual;

                        RESULT
                        ----------------------------------
                        A B
                        Now please, consider the following:
                        SQL> exec :x := null

                        PL/SQL procedure successfully completed.

                        SQL> select dump(:x) dmp from dual;

                        DMP
                        -----------------------------------------
                        NULL

                        SQL>
                        As we can see, Oracle does not do blank-padding when assigning NULL constant to CHAR variables. Yes, I agree, this is a bit tricky behavior, it may confuse people (or may not) - but it's not an inconsistency. It just works this way. Moreover, as William Robertson noted above, this behavior is documented (see "PL/SQL User's Guide and Reference").


                        By the way, four years ago the similar question was asked and answered on AskTom, I hope you'll find it useful:
                        http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

                        In my opinion, there could be two possible suggestions to overcome this tricky "empty-strings-and-CHARs" behavior:
                        1. Do not initialize CHAR variables with '', use NULL constant instead.
                        2. Use VARCHAR2 instead of CHAR. After all, why should we bother ourselves with blank-padded CHARs? Do we really need them?

                        Regards.

                        Message was edited: some minor corrections were made.
                        • 174. Re: Treatment of zero-length strings as NULLs?
                          537882
                          No, the NULL pointer is something else again.

                          It is a mark signifying that a variable pointer isn't pointing to anything, and as such is actually a value.

                          You can compare two null pointers and agree that the address they point at is the same (nothing).

                          The NULL in *ptr=NULL is not the SQL NULL.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                          • 175. Re: Treatment of zero-length strings as NULLs?
                            27876
                            The NULL in *ptr=NULL is not the SQL NULL.
                            Yes, I know they are different. What I'm saying there is that the pointer itself has a valid location whereas the value it points to is non-existent. An SQL Object exists but the elements it contains do not yet point to something existing or valid.

                            A pointer could be NULL or it could be a valid pointer with the value it points to being NULL. Similarly, an SQL Object could be NULL or it could exist but just that the values of its elements are not known yet (NULL).

                            Maybe the example below will help:
                            SQL> create type obj as object (n number) ;
                              2  /

                            Type created.

                            SQL> declare
                              2    o obj ;
                              3  begin
                              4    if (o is null) /* Object instance o is not initialzed yet, it is null */
                              5    then
                              6     dbms_output.put_line('Object is null') ;
                              7    end if ;
                              8  end ;
                              9  /
                            Object is null

                            PL/SQL procedure successfully completed.

                            SQL> declare
                              2    o obj ;
                              3  begin
                              4    o := NEW obj(NULL) ; /* Object instance o is now initialized, it is not null */
                              5    if (o is null)
                              6    then
                              7     dbms_output.put_line('Object is null') ;
                              8    end if ;
                              9    if (o.n is null) /* its attribute however is NULL */
                            10    then
                            11     dbms_output.put_line('Object attribute is null') ;
                            12    end if ;
                            13  end ;
                            14  /
                            Object attribute is null

                            PL/SQL procedure successfully completed.

                            SQL>
                            Message was edited by:
                            Kamal Kishore
                            • 176. Re: Treatment of zero-length strings as NULLs?
                              551707
                              Dear Mr. Andrew,

                              Congratulations! You've just disproved Aristoteles two-way logic princip "tertium non datur", showing us that
                              ( ( A = TRUE ) AND ( A = FALSE ) ) = FALSE
                              can also be
                              ( ( A = TRUE ) AND ( A = FALSE ) ) = TRUE
                              if we employ logical acrobatics and Oracle's own thinking logic (two-heads double-logic). Brilliantly. My compliments.

                              Kamal Kishore, responding to one of my posts, wrote: "Well, you can take as many left and right turns in your discussion...". I like the phrase. I like Kamal also. He is an honest man and proficient Oracle's guru.

                              Following Kamal's phrase (but not Kamal himself), I could say that all your "disprovals" and enclosed examples as "arguments" for "disprovals" are only piffles and nonsenses, unworthy of serious consideration (Pithecanthropus Erectus).

                              So, my answer is as follows:

                              I didn't compare Oracle with Sybase, I didn't compare Oracle with MySQL, I didn't compare Oracle with DB2 or any other similar or non-similar product on the market. I compared Oracle with Oracle itself. The SAME THING (consistent or not) was compared with the SAME THAT THING in Oracle itself. Once again: The SAME THING, consistent or not, but the SAME THING. Is PL/SQL a third-party product? I considered it to be the constitutional part of Oracle system.

                              So, once again:

                              ORACLE IS INCONSISTENT WITH ORACLE ITSELF


                              Cheers

                              Albert
                              • 177. Re: Treatment of zero-length strings as NULLs?
                                27876
                                ORACLE IS INCONSISTENT WITH ORACLE ITSELF
                                I would suggest that at this point, you should file this as either a bug or an enhancement request with Oracle support to let your voice and view be heard in the appropriate forum and place.

                                Message was edited by:
                                Kamal Kishore
                                • 178. Re: Treatment of zero-length strings as NULLs?
                                  551707
                                  My guess is that in the case of
                                  INSERT INTO contradiction(zero_length_string)
                                  VALUES('');
                                  SQL is parsing the '' into a NULL which is standard
                                  behaviour and consistent with LENGTH('') etc.
                                  Right. Since Oracle took '' to be NULL, this is consistent. I'd call this - being consistent with inconsistency with the world outside Oracle.
                                  For example if you did this:
                                  INSERT INTO contradiction(zero_length_string,
                                  string_length) VALUES('', LENGTH(''));
                                  what would you expect to appear in the second
                                  column?
                                  SQL> set NULL 'NULL'
                                  SQL> select * from contradiction;

                                  Z        LEN
                                  - ----------
                                  N NULL
                                  U
                                  L
                                  L
                                  Also consistent in the above sense.

                                  >
                                  However in the PL/SQL assignment the compiler knows
                                  it is dealing with a CHAR variable and initialises
                                  it, causing it to be blank-padded to its declared
                                  length. As
                                  [url=http://download-uk.oracle.com/docs/cd/B19306_01/a
                                  ppdev.102/b14261/datatypes.htm#LNPLS015]the
                                  documentation says,
                                  When you assign a character value to a CHAR
                                  variable, if the value is shorter than the declared
                                  length of the variable, PL/SQL blank-pads the value
                                  to the declared length.


                                  Possibly PL/SQL is wrong here, because the length of
                                  '' is unknown according to Oracle's standard
                                  semantics, not < 1. PL/SQL seems to be interpreting
                                  '' as Albert's empty string:
                                  Right. Although it's Albert-empty-string and I like it, it's inconsistency between server SQL and PL/SQL. Inconsistency with inconsistency that happens to be consistency with the rest of the world.

                                  >
                                  SQL> DECLARE
                                  2      v_unassigned CHAR(1);   
                                  3      v_null       CHAR(1) := NULL;
                                  4      v_empty      CHAR(1) := '';
                                  5  BEGIN
                                  6      DBMS_OUTPUT.PUT_LINE('Length of
                                  v_unassigned: ' || LENGTH(v_unassigned));
                                  7      DBMS_OUTPUT.PUT_LINE('Length of v_null: ' ||
                                  LENGTH(v_null));
                                  8      DBMS_OUTPUT.PUT_LINE('Length of v_empty: ' ||
                                  LENGTH(v_empty));
                                  9  END;
                                  10  /
                                  Length of v_unassigned:
                                  Length of v_null:
                                  Length of v_empty: 1
                                  Other more serious examples usually brought up in
                                  this sort of debate are DECODE, CONCAT and || which
                                  treat nulls as empty strings, and multi-column unique
                                  constraints which by not counting null values treat
                                  them as all the same. In all these cases though, the
                                  existing behaviour seems overwhelmingly more useful
                                  than the theoretically pure one.
                                  Alles klar.

                                  Regards

                                  Albert
                                  • 179. Re: Treatment of zero-length strings as NULLs?
                                    245482
                                    I'm not sure why you're claiming to quote Aristotle and then providing us with a bit of legalese in Latin. This is a bit like your reference to set theory -- in that it wasn't particularly relevant. It makes you come off as trying to distract folk. Careful there.

                                    Now, with regards to your point itself...

                                    Yes, it's confusing that Oracle handles null and an empty string as identical with respect to varchar2 but that it handles them differently with respect to char. That's historical dreck, and I'm sure that none of us like it. However, no one in their right mind uses char so it doesn't really bother many of us.
                                    1 10 11 12 13 14 Previous Next