0

Ok, I am getting frustrated searching around so I will ask here. What I was researching is the CONCAT_NULL_YIELDS_NULL setting in MS sqlserver.

I was getting peculiar behavior in a query/stored procedure I had to write on sqlserver.

Now almost all of my experience in working with databases is in Oracle, so I finally tracked down that, to my amazement, MS was throwing away my data on purpose.

You see I was concatenating values together to form a string for logging/debugging, and every time anything interesting happens I didn't get anything but nulls.

So my question is actually twofold: a) Why would people to want to throw away all their data if one piece is null? b) do other DB engines also have this defect, or is it just MS?

I am not trying to be snide here, I honestly cannot comprehend why this is acceptable ever.

Seeds
  • 266
  • 1
  • 7
  • Getting `NULL` from input sets that contain `NULL` (e.g. `1+NULL+3` is a very common scenario in standard SQL and it's a design decision. Are you talking about some specific function where it isn't documented and/or doesn't make sense? – Álvaro González Jul 25 '14 at 16:00
  • It's the basic functionality of most databases. How can you add `NULL` (something with no type or value) to anything? What would the result of `NULL` and `Char(10)` be? An unknown added to a char would be unknown (like the result of impregnating a lab rat with the contents of a test tube labeled "sperm" without a species). A `NULL` and an `INTEGER` would result in an unknown value as well. It's basic logic: if you add an unknown and a known, you get an unknown value. – Ken White Jul 25 '14 at 16:03
  • This is required by the SQL standard: any expression involving NULL must return NULL. Don't know if SQL Server has it, but there is a `concat()` function in most DBMS thta treats `NULL` as an empty string. –  Jul 25 '14 at 16:04
  • This is probably a surprise when coming from the oddness that is Oracle and its [rather odd treatment of an empty string as being the same as NULL](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null) but it seems like expected (and sane) behaviour for SQL Server and other RDBMSes to me. NULL is not a string, and adding/concatenating NULL to other values will generally yield NULL. Should 3 + NULL be 3? Personally, I'm happy that it's NULL. – Matt Gibson Jul 25 '14 at 16:12
  • Oh wow, really? The standard calls for the DB engine to throw away my data. How disappointing. I guess I never even considered that a NULL would behave as anything except an empty string in concatenation. Does anyone know what the thought process was (or a link) behind that, I still do not understand.I always thought of DB operations in terms of sets, any operation involving a non empty set and the empty set (NULL) returns the non empty set. – Seeds Jul 25 '14 at 16:21
  • 2
    The standard calls for the DB engine to throw away data which you've "contaminated" by mixing it with a lack of data. It's possible that your background in Oracle has trained you to think of NULL and empty strings as more equivalent than they are in other, more standard systems. People often get tripped up going the other way, too, and are often very surprised when they can't tell the difference between a NULL and a zero-length string in Oracle. Either way, just use ISNULL or COALESCE to tell SQL Server that you want to convert NULLs to "". It won't take you long to adjust. – Matt Gibson Jul 25 '14 at 16:26
  • I think it is more my background in mathematics that trained me to think that adding the empty set to anything returns the thing unchanged. Oracle simply worked as expected. – Seeds Jul 25 '14 at 16:52
  • Except, of course, where it doesn't. I'm pretty sure that `3 + NULL` in Oracle is `NULL`. Numbers already have a value for what you're thinking of the empty set: it's 0. And strings have one, too: it's ''. It's more like Oracle has a weird case with strings where the absence of any set (NULL) is the same as an existing, empty set (''). – Matt Gibson Jul 25 '14 at 17:01
  • 1
    It is fine to think that adding empty set to anything returns the thing unchanged, the problem is that NULL is not anything nor it is empty set so your thinking about empty sets and anything does not involve NULL and that is the root of your problem... – Dean Kuga Jul 25 '14 at 17:19
  • Matt, An interesting thing to note is that `select (123||null)+4 from dual;` returns `127`. I have to disagree that NULL constitutes absence of any set, though, an empty STRING isn't necessarily the same as Ø but certainly a column with no assigned value is congruent to Ø. – Seeds Jul 25 '14 at 19:25
  • Well, yes, but again, that's because of Oracle's odd empty string/null equivalence, and this special case with concatenation: ["Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings"](http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm). Mostly, it's the results of a historical mess, rather than any consistency with logical design... – Matt Gibson Jul 25 '14 at 19:53

2 Answers2

2

You are concatenating strings together, and null is not a string. Null is null. It is its own type, and therefore trying to concatenate it to a string should require explicit conversion to a string type, as there is no implicit conversion.

The setting CONCAT_NULL_YIELDS_NULL is meant to give a consistent and expected response for the behavior of attempting to concatenate a null to a string, following the SQL standard.

Computed columns and indexes will fail in SQL Server if this setting is off, and MS is changing SQL Server in future versions to make it so that the setting cannot be turned off.

http://msdn.microsoft.com/en-us/library/ms176056.aspx

Nick Zimmerman
  • 1,471
  • 11
  • 11
  • As to your first paragraph, the other way of see null is that the null type is every type, or no type, and that any operation of adding nothing to something leaves the something unchanged. (i.e. in math, adding zero rather than multiplying by zero) – Seeds Jul 25 '14 at 16:43
  • 1
    I see your point, and some languages will handle it that way. It is simply a choice made in the definition of the SQL standard to determine that null is handled explicitly as being of type null and no value. – Nick Zimmerman Jul 25 '14 at 17:17
  • It seems like it would simplify things if null were treated more like Ø, I just need to remember new rules with different DBs. – Seeds Jul 25 '14 at 19:27
0

The reality is that NULL is like a black hole... NULL plus anything equals NULL. You cannot test values against NULL either, because NULL doesn't equal anything else (even another NULL). That's why there is the separate syntax "IS NULL" and "IS NOT NULL" that must be used.

In essence, the DB isn't "throwing away your data", it's doing exactly what you're telling it to do. As stated above in the comments "unknown" plus anything known results in an "unknown" value... i.e. NULL.

If this is a problem for you, be sure to use the COALESCE() or ISNULL() functions to translate nulls to empty strings. Problem solved.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33