August 23, 2013 at 6:11 am
Hugo Kornelis (8/23/2013)
...I've had to work with counters that corresponded to C# zero-based arrays. So "3" represents four elements, and if you combine two of those arrays, you have "7" (representing eight) elements. For that specific use case, it would have been practical if 3 + 3 would evaluate to 7. If someone would redefine addition to work that way, it would save me the effort of having to explicitly write "+1" to my additions. Okay, it may fly in the face of all mathematical theory - but it is SO PRACTICAL (in that specific situation).
Does the request to redefine addition this way sound absurd to you? Well, the request to redefine concatenation of NULL values is exactly like that.
...
Actually, I've been a developer for decades longer than I've been a DBA so zero-based counting does not fly in the face of all mathematical theory nor sound absurd to me. If I add two Hex numbers 7 + 7 = E.
No the mathematical universe did not just implode. It's perfectly correct when one understands the reference is Hex-based.
This is why I'm in complete agreement with the implementation of the new function. Combining "no data present" to any known value(s) = the known value(s) seems perfectly correct.
August 23, 2013 at 2:56 pm
L' Eomot Inversé (8/23/2013)
Hugo Kornelis (8/23/2013)
Now you're doing the same as you did in the start - simplyfying "it isn't known to the database" to "it isn't known". You are far from alone in that confusion....
....
And when I write "WHERE is_smoker IS NULL", I am indeed looking for people for whom we do not know whether they smoke or not.
And there you fall into exactly the simplification that I use. So if it is indeed a confusion, I have you for company.
No, there I apply my knowledge of the context. I know that in the context of the is_smoker column, a NULL represents unknown. I thought that was clear from the context of the message.
It's all very well to talk about the context; but often when you come to look at some SQL you don't have that context - you just have the code, including if you are lucky the scripts that generate the schema and all its constraints, and of course you have the data. (...)
(Shortened the quote down to the essence)
Or another way to put it: "In theory, there is no difference between theory and practice. In practice, there is".
And yet, when we work with a table and see the number 42 in a column, we can usually deduct from the limited context available (column name, existing code, other data in the same column) how to interpret it. The same applies for a NULL, In both cases, the deduction may not be correct all the time, but most of the time, we do get it right.
I'll leave it at this. It's late and I've worked way too hard today, so I need to relax and sleep. After I respond on one other forum topic.
August 23, 2013 at 4:56 pm
This is a good QOTD. Obviously brought out a lively discussion about Nulls.
Microsoft have done a good job of providing a Function that works the way most people would prefer to deal with Nulls i.e. by not having an everyday operation such as building someones Name out of its component Columns come to grief by Nulls in some of the Columns.
David
August 23, 2013 at 8:16 pm
Just to add my tiny experience here about Null, if I may.
I had those statistics Views created from many tables. Every source tables were loaded from different group of people, but the data were all related, so the group "A" has to create one reference so group "B" and "C" could enter theirs info.
As the group "A" may not has received all the information while creating the reference case, many columns would be "NULL", being entered later, but sometimes (for specific column) there could be some "really unknown" data (the data is impossible to get for any reason) and other time their could be no data to enter at all (the data is not applicable for the type of reference)
So, three cases of a completely different meaning for an empty column value. The solution for me (who had really not much SQL experience) was simple: write something of leave it "NULL" and create the Views with an "IsNull" for the specific colum (if is "Null", write 'not received yet' or write 'will be known next month', etc. depending of the specificity ot the View)
_ NULL value = data never was entered/received
_ Unk = Data was/will never be acquired (mostly because of human error)
_ N/A = Nothing as data is not applicable (nothing could be acquired for the type of that reference)
Surely, if the column would have been integer values, I would had to find an other solution, but my point here is that Null (for me) really means more than nothing, more than the emptyness of the space: it means the "nothingness of the emptyness" before its creation
So, it is logic to me that concatenating a Null to a value put "nothing" beside something ("Null" is not created so it does not exist), but adding (+) something to whatever is not created (Null) makes that something absorbed and becomes something to be created (like putting a planet in a black hole, but my comparaison is bad because a black hole is a compact mass of something, so...)
Hope I did not disturb anyone with my vision...
August 28, 2013 at 7:20 am
When I first saw this question, I hadn't used the CONCAT function so I read up on it. I should have gotten the question right based off of that information but I wanted to verify so I ran the query. COLLATION ERROR!! When I re-ran within the context of the tempdb, success. Unfortunately by then I had already failed the QotD. My only excuse: I sooo need to wait for my first cup of coffee to take effect before trying to think...
Great Question!
August 29, 2013 at 1:45 am
I have yet to work on SQL 2012. So it was very refreshing to learn about one new function through QOTD.
Only if life was that easy... :-):-):-)
October 1, 2013 at 3:20 am
same thing I do not work with SQL 2012 but was just reading about CONCAT on mssqlTips billet
so +1
and bravo to our two scribers it seems there are both right depending on the useability we need
October 1, 2013 at 8:07 am
I got an email suggesting somone had misinterpreted my comment here in a strange way, so I thought I should clafify.
L' Eomot Inversé (8/23/2013)
Hugo Kornelis
(Oh, one more thing - the example af the start of your post painfully reminded me of the fact that the current version of the ANSI standard for SQL actually defines a data type for truth values. They call it boolean (shudder!), and even explicitly specify that the truth value "unknown" and the situation where a truth value is missing should both be represented by the null mark (shudder even more!).I agree with your first shudder. Boolean logic is not 3-valued, so calling this type boolean is just plain silly. Your "shudder even more" I can't agree with - what else should one do when the value of a so-called Boolean is not known in the database? Apart from the name, there's nothing wrong with this type.
What I meant was not that the only unmarked values in the domain (type) should be 'TRUE'and 'FALSE'; that would be a genuine boolean type. A type representing a three valued logic should have 3 unmarked values: 'TRUE', 'FALSE', and 'NEITHER' (and preferably not be called BOOLEAN). I may have misunderstood Hugo, and misread his statement of what's in the current ANSI standard (of which I don't have a copy, so I didn't check there), and the message I received indicated that it's author though that Hugo was saying exactly that, and that the standard has only two non-null values. Looking at Hugo's words again, I see that it probably does mean that and not the meaning I commented on. If so he and I agree that the type can't represent a three valued logic, but disagree that it's inappropriately named - it's a clean representation of a Boolean two-valued logic; and the idea that it could be used to represent the truth values of a three-valued logic is most defintely a cause to shudder hard, an utterly horrible confusion of levels: just the opposite of what my original comment said. To say that I know that a truth value is known and is neither TRUE nor FALSE is very different from saying that I don't know what that truth value is, because in a three valued logic there is only one value that is neither TRUE nor FALSE; in fact the two statements contradict each other.
I'll have to see if my local library can get me a copy of the standard, so that I can know for sure whether ANSI was that incompetent or not. I think the reason I originally interpreted Hugo's words as I did was that I didn't imagine that ANSI could have got it that wrong - far worse than a naming error. It is the sort of error that would be amazing if committed by someone who had taken just the first couple of lectures on teh varieties of logic used in mathematics, or even just read a 1 sheet of A4 introduction to constructive mathematics.
Tom
November 12, 2013 at 2:41 pm
Would not + sign give error message?
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply