1 11 12 13 14 15 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 180. Re: Treatment of zero-length strings as NULLs?
        551707
        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.
        It's already done by my employer two or more years ago. But one voice is obviously not enough. I expected the version 10g fixed it. The main reason for my posting this thread was to encourage all members of this forum to post a collective request. But I am afraid, I failed.

        Regards

        Albert
        • 181. Re: Treatment of zero-length strings as NULLs?
          551707
          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.
          To distract folk? To distract folk's attention from other matters? What matters? Everyone knows Aristoteles princip: AA' = FALSE. Sorry, you've got it wrong. Please take into consideration that English is not my native language, so...

          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.
          Then, let's make a collective feature (bug fixing) request from this forum. Oracle MUST fix it.

          Regards

          Albert
          • 182. Re: Treatment of zero-length strings as NULLs?
            27876
            This most likely will go as a enhancement request (if at all) not as a bug since there is no broken system solely due to this behavior and also since empty string and NULL handling is documented.
            But one voice is obviously not enough.
            Enhancements are for "as and when time permits".
            The main reason for my posting this thread was to encourage all members of this forum to post a collective request. But I am afraid, I failed.
            Since the behavior is documented and many folks here know how the system behaves and works, they code the applications accordingly. Obviously, you cannot show a broken production system based on a behavior that is documented, it may not draw much interest aside from having someone filing a enhancement request.
            • 183. Re: Treatment of zero-length strings as NULLs?
              551707
              Obviously, you cannot show a broken production system based on a behavior
              that is documented, it may not draw much interest aside from having someone
              filing a enhancement request.
              No, I really cannot show a broken production system, that's true. But it's a stone in everybody's shoes.

              Albert
              • 184. Re: Treatment of zero-length strings as NULLs?
                245482
                To distract folk? To distract folk's attention from
                other matters? What matters? Everyone knows
                Aristoteles princip: AA' = FALSE. Sorry, you've got
                it wrong. Please take into consideration that English
                is not my native language, so...
                I'm not trying to pick on your English, my Spanish is miserable, my French worse. :) I was just pointing out that a claimed quote like -- "Tertium non datur" (Aristoteles, Metaphysik) -- doesn't come off very well since Aristotle wrote in Greek rather than Latin. Further, we're talking about ANSI SQL's NULL -- which is all about 3-value logic rather than two.
                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.
                Then, let's make a collective feature (bug fixing)
                request from this forum. Oracle MUST fix it.
                If Oracle fixed it, then it would likely break code somewhere or other in a client's application. Given how insignificant an issue this is (since nowadays folk use varchar rather than char) I can't imagine that Oracle is going to change the behavior. In fact, with almost any technology that's been around long enough to matter there are a few things that could/should have been done better/differently. The choice then becomes whether to break existing applications that rely on the spotty behavior or let it be.
                • 185. Re: Treatment of zero-length strings as NULLs?
                  551707
                  I'm not trying to pick on your English, my Spanish is miserable, my French worse. :) I was just
                  pointing out that a claimed quote like -- "Tertium non datur" (Aristoteles, Metaphysik) -- doesn't
                  come off very well since Aristotle wrote in Greek rather than Latin. Further, we're talking about ANSI
                  SQL's NULL -- which is all about 3-value logic rather than two.
                  Just to clarify. It's true that Aristotle wrote in Greek rather than Latin, but his principle "Low of Excluded Middle" is most frequently given in Latin as "tertium non datur" ("a third is not given") that is almost an international term. In our case of proving a contradiction, "a third is not given" doesn't refer to "unknown" (in the sense "excluded 'unknown'"), since AA' is always FALSE if A is a known value (true or false). So, the principle is perfecly valid in 3VL either.

                  AND       |   TRUE      FALSE     UNKNOWN
                  -----------------------------------------
                  TRUE      |   TRUE      FALSE     UNKNOWN
                  FALSE     |   FALSE     FALSE     FALSE
                  UNKNOWN   |   UNKNOWN   FALSE     UNKNOWN



                           NOT
                  ----------------
                  TRUE     FALSE
                  FALSE    TRUE
                  UNKNOWN  UNKNOWN
                  Albert
                  • 186. Re: Treatment of zero-length strings as NULLs?
                    castorp
                    It's already done by my employer two or more years
                    ago. But one voice is obviously not enough. I
                    expected the version 10g fixed it. The main reason
                    for my posting this thread was to encourage all
                    members of this forum to post a collective request.
                    But I am afraid, I failed.
                    Did you file an enhancement request to support distinguishing DATE and NUMBER columns between "user refuses to disclose the information" and "not touched" as well?

                    As I said I still don't understand why you only complain about this with regards to VARCHAR2, but not to DATE or NUMBER

                    Taking a column called "BIRTHDAY", with the datatype DATE. You cannot implement the following either.

                    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. Can you please explain why you feel that VARCHAR2 columns should offer more "features" than other datatypes?
                    • 187. Re: Treatment of zero-length strings as NULLs?
                      Billy~Verreynne
                      > You are deeply in Orwell. That might be the problem.
                      <p>
                      You think? I was again thinking my problem was more related to baloney being sold as The Sole Truth On Ham According To Albert around here.
                      <p>
                      > Apart from being deeply immersed in George Orwell's<br>
                      world, you have a big problem with<br>
                      datatypes (understanding the nature of various<br>
                      datatypes).
                      <p>
                      You reckon? I mean, having worked in over 15 different programming language through the years, having worked with numerous RDBMS since the mainframe days, surely then did not teach me the first darn thing on data types. Damn! What an idiot I must be. So much then for experience I guess...
                      <p>
                      > Billy, I cannot help you. And, I'd say, nobody here<br>
                      can help you. Your problem is not<br>
                      solvable inside this forum. Sorry. Try with C.J.<br>
                      Date.
                      <p>
                      Ah.. the "put-your-head-in-the-sand-and-you-will-not-be-questioned" approach.
                      <p>
                      Whatever works for you Albert. Just remember that an arse get easier kicked when the head is in the sand.
                      <p>
                      Find it rather interesting though that "the problem" only seems to be a problem for you and not for the majority of us. Just how important is the NULL problem?
                      <p>
                      Verse 5.3 from Book 5 of the "The Tao of Programming"
                      <p>
                      <font color="blue">
                      A novice programmer was once assigned to code a simple financial package.
                      The novice worked furiously for many days, but when his master reviewed his program, he discovered that it contained a screen editor, a set of generalized graphics routines, an artificial intelligence interface, but not the slightest mention of anything financial.
                      When the master asked about this, the novice became indignant. ``Don't be so impatient,'' he said, ``I'll put in the financial stuff eventually.''
                      </font>
                      • 188. Re: Treatment of zero-length strings as NULLs?
                        William Robertson
                        So your point is that Aristotle and the law Of The Excluded Middle imply that Oracle needs to represent "No middle name" by some special convention, but not "No marriage date"? Seems a bit of a leap to me.

                        What true/false statement are you referring to?
                        • 189. Re: Treatment of zero-length strings as NULLs?
                          537882
                          > 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".


                          This is not correct. Excluded Middle, or "tertium non datur" refer to absence of "unknown". Everything you've said following based on this is similarly not correct.

                          The argument is now splintering in an incoherent way into confusion of all of following.

                          (1) law of excluded middle apply to relational model, not SQL.

                          (2) SQL NULL imply 3VL. Hence (1)

                          (3) OP actually wants 4VL to be represented for VARCHAR2 predicates only, by either {TRUE,FALSE,Unknown(NULL),Missing('')} where '' necessarily signifies value is known to be missing. Seems he has a special meaning of Unknown(NULL) implied. This is that Unknown can never mean Unknown and possibly Missing.

                          I don't think there is any debate about (1) or (2). This is fact.
                          • 190. Re: Treatment of zero-length strings as NULLs?
                            551707
                            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.
                            Ah.. the "put-your-head-in-the-sand-and-you-will-not-be-questioned"
                            approach.
                            Whatever works for you Albert. Just remember that an arse get easier
                            kicked when the head is in the sand.
                            Find it rather interesting though that "the problem" only seems to be a problem
                            for you and not for the majority of us. Just how important is the NULL problem?
                            Hi Billy,

                            maybe I really didn't quite realised that Ted Codd, C.J. Date, Joe Celko and others were
                            notorious fools just because Billy The Verreynne, a very smart guy, said that.

                            Oh yes, it might be a good thing for us to wait until Armageddon (Apocalypse) to get
                            Oracle doing well. We wish Oracle had even more flaws to keep us constantly bussy,
                            to keep us important, wanted, very useful guys. We'll enjoy finding workarounds, writing books
                            about it, sharing our "expert" knowledge with our fellow DBA friends. Who care for nulls
                            and zero-length-strings. Only jerks.


                            Sincerely

                            Albert
                            • 191. Re: Treatment of zero-length strings as NULLs?
                              491689
                              I could say that all your "disprovals" and enclosed examples as "arguments" for "disprovals"
                              are only piffles and nonsenses, unworthy of serious consideration ...
                              Arghhh... Unbelievable. I just don't know what else to say.

                              Albert,

                              do you actually read what others reply?
                              If you don't, why did you start this thread then?
                              If you are so sure that Oracle database is fundamentally flawed about nulls and empty strings - why do you ask other people for their opinions???

                              Once again, about your demonstration of "Oracle's inconsistency with Oracle itself". My last attempt.

                              This is your test case:
                              >> 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;
                              As I was trying to explain to you, this only demonstrates the fact that Oracle blank-pads character strings when assigning them to PL/SQL CHAR variables. William Robertson kindly gave us an appropriate quote from documentation.

                              Actually your code prints misleading messages which may confuse other people. They MUST be changed as follows:
                              DECLARE
                                zero_length_string CHAR(1) := '';
                              BEGIN
                              -- At this point, we already know that our local variable
                              -- contains blank character. So let's be fair about messages to print.

                                IF( zero_length_string IS NULL )
                                THEN
                              dbms_output.put_line( 'BLANK CHARACTER IS NULL' );
                                ELSE
                              dbms_output.put_line( 'BLANK CHARACTER IS NOT NULL' );
                                END IF;
                              END;
                              Run this code and enjoy its obvious output...

                              So no, your example does not demonstrate any inconsistency. It demonstrates well-known and documented behaviour.

                              Wait, if you still think that my every post to this thread contains "nonsense" that can not be taken into "serious consideration", how about opinions of Oracle experts? For example, would you like to hear an opinion of Tom Kyte?
                              I already gave you this link but anyway:

                              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372

                              Hope this helps. Otherwise - I just give up.

                              Regards,
                              Andrew.
                              • 192. Re: Treatment of zero-length strings as NULLs?
                                551707
                                Absolutely right. Aristoteles would claim: "Tertium non datur"
                                (Aristoteles, Metaphysik), not knowing for "unknown".
                                This is not correct. Excluded Middle, or "tertium non datur" refer
                                to absence of "unknown". Everything you've said following based on
                                this is similarly not correct.
                                The argument is now splintering in an incoherent way into confusion
                                of all of following.
                                Holly Mary! I could swear: this is for the first time in my life I made
                                someone confused with "tertium non datur". I've never had the problem with the
                                term in Germany, Italy, Greece, Switzerland, Belgium, Netherlands, France,
                                Spain, Portugal, Poland, UK, Norway, Finland, Sweden, Russia, Israel,
                                Australia, Japan, Brazil, Argentina, Canada and USA (California and New York).
                                Beyond me. It's international term. Everyone who is familiar with formal
                                logics cannot be confused with it. It just means: (A XOR A' = TRUE), or
                                (A AND A' = FALSE).

                                Note, however, that I was not the first who put this principle on the thread.
                                I've just mentioned its "academic" name. Remember:

                                [Andrew Max]
                                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).
                                3VL "version" of the principle is only the extended 2VL "version" of
                                the principle. Extended with "unknown". Any law of classical two-way logic is
                                also valid in three-way logic, but opposite is not true.

                                Let's prove that Aristotle's principle is perfectly valid in 3VL (don't be
                                confused with "a third is not given", it's only its "ancient" name).

                                Consider the following:
                                AND       |   TRUE      FALSE     UNKNOWN
                                -----------------------------------------
                                TRUE      |   TRUE      FALSE     UNKNOWN
                                FALSE     |   FALSE     FALSE     FALSE
                                UNKNOWN   |   UNKNOWN   FALSE     UNKNOWN


                                         NOT
                                ----------------
                                TRUE     FALSE
                                FALSE    TRUE
                                UNKNOWN  UNKNOWN
                                Note that "unknown" has domain {true, false}, so it really means
                                "TRUE XOR FALSE", but we don't know the exact value.

                                Now, let's prove that logic expression (Aristotle principle)
                                [ ( AA' = FALSE ) = TRUE ]
                                is tautology, i.e. that is valid (TRUE) for all logic values of proposition A
                                (true, false, unknown).
                                ----------------------------------------------------
                                1.  ( A = TRUE ) => ( A' = FALSE ) => ( AA' = FALSE )

                                2.  ( A = FALSE) => ( A' = TRUE  ) => ( AA' = FALSE )

                                3.  ( A IS UNKNOWN )        =>        ( AA' = FALSE )
                                            |
                                            |--> (A = TRUE  ) see 1.
                                            |--> (A = FALSE ) see 2.
                                ----------------------------------------------------
                                Herewith the Aristotle principle has been proved as a valid 3VL principle.


                                Regards

                                Albert
                                • 193. Re: Treatment of zero-length strings as NULLs?
                                  William Robertson
                                  Yes, I think we are all familiar with the idea of things being either true, false or unknown. What we are all waiting for is a link to the original question of somebody not having a marriage date or a phone number or whatever it was.
                                  • 194. Re: Treatment of zero-length strings as NULLs?
                                    6363
                                    a: 
                                    I need the cell phone number of Ed Clarke in accounts.
                                    b: 
                                    Okay, hold on one second.
                                    b: 
                                    I'm sorry we don't have a mobile number for Ed.
                                    a: 
                                    Is it that he doesn't have a mobile, or we just don't know the number?
                                    b: 
                                    Well, it's just showing a blank.
                                    a: 
                                    Do you have Aristotle's cell number? I really need to contact Ed urgently and possibly escalating this to a philosophical level may help us to determine whether this may theoretically be possible?
                                    b: 
                                    Okay, hold on one second.
                                    1 11 12 13 14 15 Previous Next