Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

CONCAT 1 Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 2:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:19 AM
Points: 2,616, Visits: 575
+1 Thank you for this easy one. I've been educating people about the news in SQL server 2012 and I really like this function. Not becase the possibility to concat null values, but to be able to concatenate strings with numeric values. Of course, concatenating strings are often used for presentation and that should be handled in the presentation layer and not in the database.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #1487518
Posted Thursday, August 22, 2013 2:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
Dave62 (8/22/2013)
I can see why this creates confusion with concatenation. People in the "unknown" camp will have difficulty concatenating an unknown value with a known value. In contrast, it should be very easy to concatenate "no data present" with any known value. The result is simply the known value.

I am sorry, but I (again) have to disagree.

Let's use an analogy. You know my first name: Hugo. You also know my last name: Kornelis. Now imagine the next wanna-be Don Corleone breaking into your house, pointing the biggest gun you've ever seen to your forehead, and daring you to utter my full name - get it wrong, he pulls the trigger. What would you do? (Apart from breaking down and crying, and probably wetting youyself, that is) I imagine you'd say that you can do that, if he is willing to first disclose my middle name (if any). His response is silence. After a while, you gather all your courage and asks again - and now Mr Wannabe Corleone tells you that he heard the question, but deliberately refused to answer. (See what I did there? His not answering your question about my middle name represents NULL - you asked for a data value (my middle name), you got nothing (the absence of a data value - NULL).
The big guy is still standing there, gun still pointed at your head. You're trying to think of a plan. Than, just as the guy clears his throat and repeats the question about my full name, the proverbial lightbulb above your head switches on. If he can do it, then so can you, right? So you, politely (of course - he's got a gun) inform the man that you heard the question - but deliberately refuse to answer. He pockets his guns, pats you on the shoulder (you cringe - even with the gun gone, he's still at least twice as big and thrice as strong as you), smiles and compliments you: "smart move - I never demanded that you HAD to answer". And then he leaves your room.

Congratulations! You not only escaped a life-threatening situation that is so absurd that even the Monty Pythons would have ditched the idea, you also did what any proper DBMS should do when asked to concatenate a string to a missing value. Analog to the "garbage in garbage out" principle, you used the "nothing in, nothing out" principle. Also known as NULL propagation. And the reason that this works is that in a database, NULL is a valid "value" (I still prefer "marker") for every data type. So whatever the datatype of the return expression is, the database always has the option of not supplying an answer, if an input that it needs to ensure the answer is valid is missing. The database should not risk returning a value that could be incorrect - and since any assumption about missing values can be incorrect, the database should not return a value when the expression involves a NULL input.


So where does all the confusion with "unknown" come from? (To my surprise, I see that even Tom, a man I normally agree with on most points, is confused by this. Understandable!)
Let's stretch the already weird analogy even further. Next day, Mr Corleone returns. Again with that impressive looking and probably extremely dangerous gun. But with a different question. "Quick", he bellows, "you need to tell me if Hugo has 'John' as his middle name. You know the drill, answer wrong and I'll pull this trigger. And don't try that trick you pulled yesterday, this time I do want an answer - no answer, I also pull the trigger". Ouch! This gets scary. While spoiling yet another perfect pair of trousers, you quickly go through your options. You can take a gamble. Statistically, the chance of me, or anyone, having 'John' as middle name is below 50% - so the answer "no" gives you a better chance of surviving than the answer "yes". But it's your life at stake. I think 10% chance of losing is too much when gambling a fistful of dollars, for my life, I need more, much more margin than that!
You keep pondering your options until Mr Big gets impatient and reminds you that no answer means death as well. Then, you break down.You cry like a baby, and between your tears you whimper "please, please, please don't shoot me. please spare me. You're asking the wrong man. I don't know. That Dutch idiot never told me his middle name, you didn't tell me, how can I know if it's John? I don't know, okay? Please don't shoot me!".
He grimaces, pockets his gun, and pats your shoulder (you notice that you start to get used to this part). "You did it again. You gave me an answer. I never told you that only 'yes' or 'no' are valid answers. 'I don't know' is just as valid - and absolutely right. Congratulations, you live. Now go take a shower and find some clean clothes, you stink".

This analogy represents a database evaulating a query with a WHERE clause. The clause here being WHERE MiddleName = 'John'. When coming across my data, the database does not have the data it needs to evaluate the question. But unlike before (when the problem was in the SELECT clause), the database now does not have the option to simply not answer at all. The query processor needs to decide whether or not to include this row, and it can't simply sit there and wait for someone to come tell it my middle name. The only options appear to be yes (true) or no (false). An incorrect answer is not an option - lifes could be at stake (unlikely with a query on middle name, much more likely with a query on, e.g., allergies - but work with me, okay?). What should the query processor do? And then -bingo!- you came along and supplied (with a bit of help from my maffia friend) the solution. A third option - "I don't know" - aka the truth value "unknown". Yes, there it is, finally, the U-word!

Instead of the traditional boolean logic (based on the two truth values true and false), we now have defined a logic system that uses a third truth value - and is therefor called three-valued logic (3VL). In 3VL, the three possible truth values are true, unknown, and false. The logic operators AND, OR, and NOT operate as one would intuitively expect (e.g. true AND unknown yields unknown, but true OR unknown yields true - if I ask you "is my first name Hugo AND my middle name John", you wouldn't know, but if I ask you "is my first name Hugo OR my middle name John", you'd say yes).

There's only one thing left to do. The poor query processor is still facing a problem. Okay, it now did get an answer when trying to evaluate "WHERE MiddleName = 'John'" - but that answer is unknown, so should the row be returned or not? To handle that issue, the designers of the relational model simply made a judgement call. They agreed among each other that rows would only be returned if the predicate evaluates to true; both [false and unknown would result in the row being rejected. They could just as well have decided to handle unknown differently (i.e., to include it in the results_ - but they didn't. Deal with it.


So, bottom line:
* NULL in a column means "no data value here". It does not mean "Unknown". In a specific context, it could mean "unknown" (just as in a specific context, 42 could mean "just large enough for only the smallest women") - but that is context. NULL itself means only "no data value here".
* When using NULL in expressions (other than those specifically designed to handle NULLs), the result has to be NULL - you don't want to utter "Hugo Kornelis" as my full name if that could result in a bullet being inserted in your brain in case it's incorrect.
* When using NULL in predicates (comparisons) (other than those specifically designed to handle NULLs), the result of the predicate is not a traditional boolean value, but the three-valued truth value ]unknown.
* Please, please, please do not confuse the evaluation of a condition on a NULL value to unknown with the value itself being or representing "unknown". If you do, I'll have no other option but to send that very tall (and very armed) Corleone type your way yet another time (we still have your address). That would be the third time - and as we all know: third time, you're out.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1487529
Posted Thursday, August 22, 2013 4:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,113, Visits: 2,126
That's ok Hugo. I don't mind if you disagree because that was a very entertaining story.

But apparently whoever added the new CONCAT function to SQL Server 2012 is in agreement with my interpretation because the result is the known values.

Enjoy!
Post #1487561
Posted Thursday, August 22, 2013 6:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 7,814, Visits: 9,562
Hugo Kornelis (8/22/2013)
So where does all the confusion with "unknown" come from? (To my surprise, I see that even Tom, a man I normally agree with on most points, is confused by this. Understandable!)

I'm not at all confused by this, but I have the impression that you are.

Let's have a database of the truth values of certain propositions at about midday each day for which a test is scheduled for the corresponding proposition. One such proposition may be "XYZ Ordinary Shares have a higher value in the London stock market than ABC Ordinary Shares"; lets call that proposition "Prop1"; we have a table created like this:
CREATE TABLE Props(
Propname varchar(16) NOT NULL,
Testdate date NOT NULL,
tv varchar(8) NULL check(tv IS NULL or TV in ('True', 'False' ,'Unknown'),
constraint PK_Props primary key (Testdate, Propname)
);

Now if we run the query
SELECT tv FROM Props WHERE Propname = 'Prop1' and date = 20130821'';

there are five things it can do, three of which involve returning a value from the domain varchar(0) NOT NULL check(tv IS NULL or TV in ('True', 'False' ,'Unknown'), the fourth returns the additional value in the extended domain varchar(8) NULL check(tv IS NULL or TV in ('True', 'False' ,'Unknown'), and the fifth returns nothing at all.
1) return 'True'
2) return 'False'
3) return 'Unknown' because when that proposition was tested on that date one (or both) one (or both) of the values was not present in the database
4) return NULL because the record with that primary key has NULL in the tv column, perhaps because the test was not, for some reason, carried out that day although it was scheduled.
5) return nothing because no row has the primary key specified in the where clause (that proposition was not scheduled to be tested that day).

In case 4, the database does not know what the truth value is; it's not known. However, it is NOT 'Unknown'.
Your exposition further on in the message containing the above quotation claims to prove that NULL can't mean unknown because it isn't the truth value "Unknown". That is just a confusion of two very different concepts, an unjustifiable conflation of data with metadata. The truth value unknown is not a possible value of any attribute of an entity unless the domain of that attribute includes the truth values of a non-classical logic which includes unknown; most domains used in databases don't include those values, so it is very unusual for that truth value to be a possible result and even when it is possible NULL does not indicate that truth value. However, it is certainly possible that the real world value of some attribute is not known, and this is indicated by marking its database representation NULL. And if a value in the database is marked NULL, it is quite clear that so far as the database is concerned the value of the real world attribute isn't known - and "it isn't known" is just another way of saying "it is unknown".

I suspect that your attempt to say that a NULL mark doesn't mean the value is unknown arises from a desire to stop people confusing the truth value unknown with the meaning of the NULL mark - but unless the domain of the value includes that truth value, nothing in the domain can be that truth value; the database representation of an attribute as NULL certainly does mean that so far as the database is concerned we don't know what value the attribute has, because we haven't recorded it. I believe it would be easier to stop people confusing the truth value and the significance of the mark if people stopped claiming that the mark doesn't imply a lack of knowledge (in the database) of the value of the real-world attribute that it marks, and explained instead the difference between the truth value's significance and the mark's significance without making such claims.

Incidentally, I reckon Date's "proof" that going to 2 sorts of NULL forced you to 3, 4 and so on ad infinitum was complete hogwash, so that appears to be something else we disagree on; but I think Codd was wrong in advocating two different NULLs in some of his papers, so that is something we agree on.


Tom
Post #1487598
Posted Thursday, August 22, 2013 7:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
Nice reading you guys!
Hugo, you should write a book!
Thanks Ron, that was a nice and an entertaining question!
Post #1487608
Posted Thursday, August 22, 2013 7:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 181, Visits: 373
Love the story.. I never knew database concepts could be so entertaining..

Have you written any books yet? I would love to read it!

edit - lol you beat me to it
Post #1487610
Posted Friday, August 23, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
L' Eomot Inversé (8/22/2013)
Hugo Kornelis (8/22/2013)
So where does all the confusion with "unknown" come from? (To my surprise, I see that even Tom, a man I normally agree with on most points, is confused by this. Understandable!)

I'm not at all confused by this, but I have the impression that you are.

Now why am I not surprised?


Thanks for your well thought out reply and the interesting example. I'll respond to a few points.
(...)
4) return NULL because the record with that primary key has NULL in the tv column, perhaps because the test was not, for some reason, carried out that day although it was scheduled.
(...)
In case 4, the database does not know what the truth value is; it's not known. However, it is NOT 'Unknown'.


That's three statements in a single line, and they are true, false, and true. :)
- "the database does not know what the truth value is" - true, the database does not know what it is, because it is missing. I prefer to stress the "missing" part and leave the "the database doesn't know part" to the imagination of the reader, because that tends to cause confusion.
- "it's not known" - false, you can reword "the database does not know what the truth value is" to "it's not known to the database", but you cannot simplfy that to "it's not known". It is missing, and I have no idea why. I could speculate about the reason why it's missing - but any such speculation would include knowledge of the context where this NULL is found. I'll expand on this "context" idea later.
- "However, it is NOT 'Unknown'." - obviously true, and I don't think anyone here needs an explanation.


Your exposition further on in the message containing the above quotation claims to prove that NULL can't mean unknown because it isn't the truth value "Unknown".

No, you are misunderstanding me. I never said that NULL can't mean unknown. This is where that "context" thing comes in.
Would you say that the number 42 can't mean "just the right size for large women"? I hope not, because you would be wrong - it can mean exactly that - within a specific context (US standard confection sizes for women's blouses). But without the context, you do not know whether that specific 42 does actually mean that. It might, or it might mean something completely else. So outside of all context, you would not say "42 means 'just the right size for large women'", nor "42 can't mean 'just the right size for large women'"; you would say "42 might mean 'just the right size for large women', or it might mean something else, depending on the context where it's used".

So I don't say that NULL can't mean unknown. I only say that NULL might mean unknown, or it might mean something else, depending on the context where it's used.

However, it is certainly possible that the real world value of some attribute is not known, and this is indicated by marking its database representation NULL.

Oh yeah, definitely. Just as it is also certainly possible that there is an attribute is not applicable, and this is indicated by NULL (birthdate of a company). Or that the real world value of an attribute is "indefinitely", and this in indicated by NULL (ValidTo date of a price). Or that the real world value is "no matching row found", and this is indicated by a NULL (result of an outer join). Or that the real world value is "you have no access to this data", and this is indicated by a NULL (possible result of some types of row-level security implementation). NULL can mean all those things, and many more.

And if a value in the database is marked NULL, it is quite clear that so far as the database is concerned the value of the real world attribute isn't known - and "it isn't known" is just another way of saying "it is unknown".

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 (it is, in fact, one of the two major reasons why so many people insist that NULL means unknown; the confusion with the truth value unknown being the other major reason).

The value is missing. There is no data value at that point in the database. So yeah, you are right that if the database has to do something with that value, it doesn't know what value to use - so I'll accept "unknown to the database" - even though that formulation is risky, because it tends to lead to confusion. But simplifying that to just "unknown" is NOT correct - that is exactly the confusion I am so wary of.


Now one important point I do need to add is that all the queries we write operate in a context. So the context I so vehemently stripped off in this message is very much present in the queries, stored procedures, and other database code. That is true for 42, and it is true for NULL. If there are two orders for 42 units of the same article, our query logic will add them up and request the manufacturer to send us 84 units. But if there are two orders for a size 42 shirt, the query will NOT add those two numbers together and send a request for a size 84 shirt to the factory. When writing the query, we know that the 42 in one column is a quantity that can be added, and the 42 in the other quantity is a confection size unit that does not lend itself to arithmetic.
And again, it's the same for NULL. When I write "WHERE ValidFrom >= @OrderDate AND (ValidTo <= @OrderDate OR ValidTo IS NULL)", I'm not querying for prizes with an unknown ValidTo date, but for prizes that are "indefinitely" valid. When I wrote "FROM OrderDetails AS od LEFT OUTER JOIN Orders AS o ON o.OrderID = od.OrderID WHERE o.OrderID IS NULL", I am not trying to find details of orders with an unknown OrderID value; I'm looking for orphaned OrderDetails (though I would in reality code that in a different way - just sayin'). 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. All because I know the context that applies to the NULL values in the rows and columns I am querying.

The context-less meaning of NULL (or of 42) is relevant for how the database handles such values. The result of an expression should always be the same, regardless of context. When I add two values of 42, the result will always be 84 - even when I am foolish enough to do this to values from the "ShirtSize" column. The database engine does not know the context and provides a well-defined treatment of the value 42 stripped of any context - a treatment that works well in most contexts, but might need special treatment in specific contexts.
Similarly, when I concatenate 'Hugo' and NULL, the result will always be NULL - even when I am foolish enough to do this in a context where I know that NULL means 'is a nutter'. The database engine does not know the context and provides a well-defined treatment of the NULL marker stripped of any context - a treatment that works well in most contexts, but might need special treatment in specific contexts.


I suspect that your attempt to say that a NULL mark doesn't mean the value is unknown arises from a desire to stop people confusing the truth value unknown with the meaning of the NULL mark


No, it's way more than just that. The false notion that NULL means unknown simply causes too much trouble. I've seen people invent their own methods to handle missing values that were not unknown but missing for another reason, because they are convinced that NULL means unknown. I have seen people expect logic on NULL to return values that would be appropriate if NULL actually means unknown, and then complain that NULLs are not handled correctly and should be avoided. And I've seen people that simply struggle with how NULLs are handled and how 3VL works. I am pretty sure that Dave will no longer suffer from that - he only needs to think back of that night when my Uncle Luigi (sorry, Italian readers - don't blame me for the stereotype, blame Hollywood) disrupted his peace twice in a single evening and he'll immediately see the logic of how NULL is handled. (Well, mostly - not all NULL handling is logical, for some situations the designers of the SQL standard simply had to make a choice and no logic could favor one choice over the other).


Incidentally, I reckon Date's "proof" that going to 2 sorts of NULL forced you to 3, 4 and so on ad infinitum was complete hogwash, so that appears to be something else we disagree on; but I think Codd was wrong in advocating two different NULLs in some of his papers, so that is something we agree on.

Just for the record, I may not have called Date's argument "hogwash", but I did call it a fallacy - so apart from you using a stronger word than me, we do not disagree on that. And as far as I know, the only reason Codd embraced the notion of two types of NULL is because he fell for Date's fallacy. I am convinced that if he had lived longer, he would eventually have seen the fallacy and retracted his writing that include the two NULL types. SO we agree on that too.



(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 am sure that there are situations where this can be used in a very practical way (just as NULL_CONCAT_YIELDS_NULL can be practical in some situations) - but from the point of relational theory, it just makes me want to cry).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1487658
Posted Friday, August 23, 2013 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 6,056, Visits: 8,342
Dave62 (8/22/2013)
But apparently whoever added the new CONCAT function to SQL Server 2012 is in agreement with my interpretation because the result is the known values.


I think it's more like one Microsoft employee ripping out CONCAT_NULL_YIELDS_NULL because it violates all relational theory, and the guy at the next desk putting in CONCAT because it's easier for some cases.

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.



tilew-948340 (8/22/2013)
Hugo, you should write a book!

Already done. Okay, not a whole book. But I did contribute a chapter for Adam Machanics "Expert SQL Server 2005 Development", two chapters for "SQL Server MVP Deep Dives, volume 1", and one chapter for "SQL Server MVP Deep Dives volume 2".
(I guess I should invest the time to add links to these books on Amazon.com, but I should really start on my work now")



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1487664
Posted Friday, August 23, 2013 3:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:57 AM
Points: 12, Visits: 22
I was caught by thinking CONCAT_NULL_YIELDS_NULL would impact the CONCAT and cast my answer, apparently it is not impacted.
Post #1487700
Posted Friday, August 23, 2013 5:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 7,814, Visits: 9,562
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.

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. Given the general behaviour of developers, if you have any documentation of the code and/or the schema, the documentation will describe a version that never worked, in fact never even succeeded in its unit tests, not the version that finally emerged from development through QA, so any context the documentation gives you is as likely to mislead as to guide you to the correct conclusion. Yes, people like you and me will often provide decent documentation; but the people who maintain and enhance our code probably won't. So the only reasonable view of the SQL language is that when we say "this is unknown" or "that is known" we are talking about the database content, not about something that we individually know because that's the way we designed it - except in the rare case that the system we are looking at is our own design and has not been altered so that the context we believe in is no longer the actual context in which the database and the data in it exist.

Yes, in an ideal world we would always know the context - for example table definitions with nullable columns would be commented to explain why the column is nullable, table definitions for child or auxiliary tables would be commented to say why a key in the parent or main table would not be referenced from any row in the child or auxiliary, and we know exactly the significance of every null. But in the real world all we can safely say is that NULL means the data isn't present, so ithe value of the data is unknown in the database, and lacking the non-available higher level context to say why the value is unknown in the database it's reasonable when talking about the database and the data in it, since when talking about "something" we are talking in the context of that something, to cut the excess verbiage and just say "the value is unknown".


edit: I think I should also comment on this:
(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.

Of course the name misuse just followed a long tradition of screwing up; back in the early days, there were some Fortran compilers which provided a bool type with 64 possible values, and other Fortran compilers that provided the same type with 256 values; you probably know that these were single character values on machines with 6 bit chararacters or 8 bit characters; usually the Boolean operators operated bitwise, so that an array of bools was a viable way to represent a string that you wanted to manipulate as opposed just to have a literal string that you wanted to output (the only viable way, in those early versions of Fortran). But the logical if statement only recognised two vales, and some compilers had 0 for false and non-zero for true, at least one had 255 for true and any other value for false, in another even numbers were false and off true - a complete nightmare. ANS Fortran IV cleared up that mess, but got much else wrong.


Tom
Post #1487739
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse