1 16 17 18 19 20 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 255. Re: Treatment of zero-length strings as NULLs?
        551707
        Right now I am completely and utterly lost by what you are trying to say. Unless I am
        completely unable to see, and all concepts of reality have left my immediate vacinity,
        these are the same right. If not, why not?
        Sorry for your being lost. It was not my intention. In the meantime, William has already solved the problem:
        1. Oracle treats a zero-length string as null
        2. ...except in some cases such as concatenation,
        In short:

        1. In the first CASE block Oracle treats zero-length-string as NULL

        2. In the second CASE block Oracle changes his mind and treats zero-length-string as a string value (NOT NULL anymore), just to make possible concatenation operation.

        It happens inside the same select statement. What a workaround! I call this "double-head" logic.
        The fact that this behaviour (contradiction) is well documented doesn't tell me that it is not contradiction.

        Albert
        • 256. Re: Treatment of zero-length strings as NULLs?
          6363
          2. In the second CASE block Oracle changes his mind and treats zero-length-string
          as a string value (NOT NULL anymore), just to make possible concatenation operation.
          No it doesn't. Oracle concatenates zero length strings and NULLS, consistently in the same way.
          • 257. Re: Treatment of zero-length strings as NULLs?
            551707
            2. In the second CASE block Oracle changes his mind
            and treats zero-length-string
            as a string value (NOT NULL anymore), just to make
            possible concatenation operation.

            No it doesn't. Oracle concatenates zero length
            strings and NULLS, consistently in the same way.
            ('ABC' || UNKNOWN_STRING) IS UNKNOWN_STRING
            If '' is UNKNOWN then

            ('ABC' || '') IS UNKNOWN
            • 258. Re: Treatment of zero-length strings as NULLs?
              27876
              It happens inside the same select statement. What a workaround! I call this "double-head" logic.
              You can play ignorant (but don't play for so long so people get tired) to prove your point but || is a concatenation operator and this is one of the properties of this operator. This is how this operator has been designed to work when it encounters a NULL value.

              You cannot argue the behaviour of an opertor as designed to be an inconsitency just because you think the other way. When the operator is introduced, it is documented to behave in a certain way (like all the other operators do) and that is exactly what this operatror is doing. it has nothing to do with handling NULL in different ways at different times.
              • 259. Re: Treatment of zero-length strings as NULLs?
                27876
                ('ABC' || UNKNOWN_STRING) IS UNKNOWN_STRING
                You do not get to choose to define the rules covering the || the concatenation operator the way you hoped it should have behaved.

                It behaves a certain way as designed by the people who created this operator for the Oracle database.

                Much like the other functions. What would you expect for the SUM to give you when some values are NULL and what if all values are NULL ?
                SQL> set null null
                SQL>
                SQL> select comm from scott.emp ;

                      COMM
                ----------
                null
                       300
                       500
                null
                      1400
                null
                null
                null
                null
                         0
                null
                null
                null
                null

                14 rows selected.

                SQL> select sum(comm) from scott.emp ;

                SUM(COMM)
                ----------
                      2200

                1 row selected.

                SQL> select avg(comm) from scott.emp ;

                AVG(COMM)
                ----------
                       550

                1 row selected.

                SQL>
                Message was edited by:
                Kamal Kishore
                • 260. Re: Treatment of zero-length strings as NULLs?
                  6363
                  2. In the second CASE block Oracle changes his
                  mind
                  and treats zero-length-string
                  as a string value (NOT NULL anymore), just to
                  make
                  possible concatenation operation.

                  No it doesn't. Oracle concatenates zero length
                  strings and NULLS, consistently in the same way.
                  > ('ABC' || UNKNOWN_STRING) IS
                  
                  UNKNOWN_STRING

                  If '' is UNKNOWN then

                  ('ABC' || '') IS UNKNOWN
                  You say Oracle is being inconsistent and changes it's mind with respect to the concatenation operator and NULLs and zero length strings.

                  So let us stick with the way Oracle behaves and works when talking about consistency instead of the way Albert would like some bits of Oracle to work at times, and how Oracle actually works at others. Maybe some common agreement here will shorten this discussion, or perhaps, as I suspect, you are just a Troll.

                  So in Oracle
                  > > ('ABC' || UNKNOWN_STRING) IS UNKNOWN_STRING
                  Is not the case, whether you would like it to be or not.
                  SQL> select 'test' || null from dual;

                  'TES
                  ----
                  test

                  SQL> select 'test' || '' from dual;

                  'TES
                  ----
                  test
                  This is entirely consistent behavior in Oracle.
                  • 261. Re: Treatment of zero-length strings as NULLs?
                    553292
                    Ok so let me create a nice example on Ingres...

                    INGRES TERMINAL MONITOR Copyright 2003 Computer Associates Intl, Inc.
                    Ingres Microsoft Windows 32-bit Version II 2.6/0305 (int.w32/00) login


                    Ingres! Say, I haven't upgraded my old system since version 6.4, but does it still distinguish between dates that are NULL and those that are empty strings? My old Ingres/OpenROAD code is littered with if this_date = '' then this_date is NULL endif; because someone foolishly touched the field on the form....
                    • 262. Re: Treatment of zero-length strings as NULLs?
                      John Spencer
                      "In short:

                      1. In the first CASE block Oracle treats zero-length-string as NULL

                      2. In the second CASE block Oracle changes his mind and treats zero-length-string as a string value (NOT NULL anymore), just to make possible concatenation operation."

                      No, Oracle treats a zero length string as NULL in both cases. As David pointed out, the difference is that you seem to expect to get a NULL from concatenating NULL to a non-zero length string.

                      In what David lablled "Your first case block" the zero length string evaluates as NULL, exactly as it evaluatexs the NULL in what David labelled "My first case block". In what David labelled "Your second case block", concatenating a zero length string to a non-zero length string resulted in a non-NULL value, exactly as it did in the case David labelled "My second case block" when concatenating a NULL to a non-zero length string.

                      John

                      P.S.

                      I though carefully about adding to this already unwieldy, and apparently futile discussion, but decided to do it anyway.

                      However, I would like to point out that there have been more than the usual number of examples of rather boorish behaviour from several posters on this thread. In fact, I might go so far as to say that some are behaving rather like little Hitlers (thereby bringing yet another historical character into the discussion and hopefully invoking Godwin's Law) :-)

                      JWs
                      • 263. Re: Treatment of zero-length strings as NULLs?
                        27876
                        I was just wondering if this huge problematic behaviour will put Oracle in an Code Class or an Identifier class?

                        A new database design should rectify this problem as well while tackling the problem of identifier names.
                        • 264. Re: Treatment of zero-length strings as NULLs?
                          551707
                          What would you expect for the SUM to give you when some values are NULL and what
                          if all values are NULL ?
                          The same. If you want to sum non-null values only then
                          select sum(comm) from scott.emp ;
                          must be written as
                          select sum(comm) from scott.emp where comm is not null
                          .

                          All databases on the market, however, uses SUM aggregate to sum only non-null values. Seems it is a convention.
                          • 265. Re: Treatment of zero-length strings as NULLs?
                            6363
                            select sum(comm) from scott.emp ;
                            must be written as
                            > select sum(comm) from scott.emp where comm is not null
                            So what would be the select statement if I had 10 nullable numeric columns, say num01 to num10 that I wanted to sum?
                            All databases on the market, however, uses SUM aggregate to sum only non-null
                            values. Seems it is a convention.
                            And for that we are thankful.
                            • 266. Re: Treatment of zero-length strings as NULLs?
                              APC
                              You were the one who brought up Aristotle, presumably to look clever,...
                              I wasn't the one who brought up Aristotle, William...
                              .... Andrew Max was the one who brought up Aristotle.
                              Spookily enough, pulp sci fi A E Van Vogt wrote a book based on an allegedly non-Aristoleian philosphy, General Semantics, called The Worlds of NULL-A. Not that that has much to do with anything but then this thread long gave up relevance.

                              Anyway, as any fule kno, NULL is SQL's biggest mistake and any database system which permits NULLs is seriously flawed.

                              Cheers, APC
                              • 267. Re: Treatment of zero-length strings as NULLs?
                                245482
                                Hmm. The "law of the excluded middle" says that for a truth proposition P that

                                P V ~P

                                is always true. We will disprove this by counter-example. Consider the following proposition:

                                x = 4

                                If x is null then we obtain

                                (x=4) V ~(x=4)

                                (unknown) V ~(unknown)

                                (unknown) V (unknown)

                                (unknown)

                                Because unknown is not true the law of the excluded middle does not hold. Now would you please stop with the distractions. You're not exactly portraying yourself well here.
                                • 268. Re: Treatment of zero-length strings as NULLs?
                                  William Robertson
                                  > In the meantime, William has already solved the problem:

                                  That isn't what I said at all. I was granting that the concatenation operator treats both null and '' (which it handles identically, probably by converting one to the other before anything else) as empty strings, whereas arguably if null means unknown then the result of concatenation should also be unknown, as apparently is the case in some other RDBMSs, heaven help them. Whether this is what Codd or the Greek philosophers of old had in mind I really couldn't say. However from a practical point of view || is just an operator that works the way it is documented to work, and thankfully Oracle chose a sensible implementation.
                                  • 269. Re: Treatment of zero-length strings as NULLs?
                                    William Robertson
                                    > Anyway, as any fule kno, NULL is SQL's biggest mistake and any database system which permits NULLs is seriously flawed.

                                    Nice to see a Molesworth reference. If only Sigismund the Mad Maths Master were here. He'd know what to do.
                                    1 16 17 18 19 20 Previous Next