"NOT IN" Operator and Three Valued Logic
-
Comments posted to this topic are about the item "NOT IN" Operator and Three Valued Logic
-
🙂
excellent QotD , things aren't so straight as they seems right 😛
thanks for sharing
-
Great QotD - made me think and slow down before answering ( also ran the code to check what I thought MAY be the answer )
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Excellent QOTD Chirag. 🙂 T.G.I.F.
-
This was removed by the editor as SPAM
-
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.
Just because you're right doesn't mean everybody else is wrong. -
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.The man does have a point....
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
Could have been a good question, but seems to have got a bit muddled somehow. The muddle is a pity, as there is at least one important lesson buried in there.
As Rune Bivrin pointed, 0 isn't actually a possible result from such a query. In addition, without even attempting to evaluate the where clause it is obvious that the first option isn't a possible result from such a query, because it is a set of one column rows and the query selects two columns, not one. So the only answer which isn't ruled out by elementary logic is the third answer, but the third answer is wrong because the SQL syntax is fine and there's nothing there that could cause an error to be raised. I guess the majority of people (58% or reponses to date) understand 3-valued logic and correctly worked out that the query would return no rows if the logic conformed to the 3-value logic rules as specified in the ANSI and ISO standards and assumed that "0" meant not a row containg "0" but "0 rows"; that's how I came to pick 0 as the answer, but it's not really satisfactory to be forced to guess what the option was intended to be in order to guess which anser will be considered correct, but option 2 is perhaps the "least wrong" answer - certainly if ANSI ULLs is ON.
Even worse, if the errors in options 1 and 2 were fixed the only correct answer would still be "it depends": if ANSI NULLS are OFF, the first answer is nearer to correct than the second one, because it then returns the correct number of rows, and the correct firstnames; so even if the first anser were corrected to show both columns, and the second one corrected to say "0 rows", the question would be ambiguous because which of those two answers was correct would depend on whether ANSI NULLs was set ON or OFF.
Finally, I reckon there could be a case for assuming that since options 1 and 2 don't say what they mean neither does option 3: it doesn't mean that SQL Server would return an error, it means that the person who wrote the query committed an error in using a NOT IN clause when the domain of the elements of the set did not preclude nulls; in my opinion that's not 100% justifiable, because we don't know how the query is used, we don't have the context, and there may be a context in which getting no rows when there is at least one null creates no problem because the result required is the set of rows which are known not to match, but that's just my opinion and I don't think it unreasonable for people to take the opposite view. So there's a plausible argument that the third option is just as correct as the second (assuming ANSI NULL is on).
What's the buried lesson? That people need to understand how three-valued logic works, understand how the result of tests involving NULL can be a bit startling if you try to think in terms of two-valued logic, and understand how to write queries that will do what they expect in an environment with NULLs, unless they are able to design their data in such a way that every column in every table has a NOT NULL constraint.
Tom
-
Ford Fairlane (11/1/2013)
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.The man does have a point....
Agreed. The point of the lesson, however, was a good one.
-
L' Eomot Inversé (11/1/2013)
Could have been a good question, but seems to have got a bit muddled somehow. The muddle is a pity, as there is at least one important lesson buried in there. ...Agreed. Good inspiration for a question. Could have been implemented better. So, try again with another QotD.
A side note: I notice Tom has changed his avatar again. The downward glance while appearing to be walking makes you look like a member of the Peripatetic School. Beats sitting at a desk!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.QFT, 🙂 I stared at the answers for a second or two... essentially for this reason.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
I think something must have been somewhat screwed up with this question since the time some people answered it.
I see no actual question being asked. The code is all outside of the code boxes. And worst is that when I selected 0, I was told that I was incorrect and that the correct answer was 0. I think the question must have been edited and then correct answer option was changed while the explanation was unchanged.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (11/1/2013)
I think something must have been somewhat screwed up with this question since the time some people answered it.I see no actual question being asked. The code is all outside of the code boxes. And worst is that when I selected 0, I was told that I was incorrect and that the correct answer was 0. I think the question must have been edited and then correct answer option was changed while the explanation was unchanged.
The question is at t end of the first chunk of code, immediately above the second empty code box.
Tom
-
Thomas Abraham (11/1/2013)
A side note: I notice Tom has changed his avatar again. The downward glance while appearing to be walking makes you look like a member of the Peripatetic School. Beats sitting at a desk!The way I pace around when trying to get something straight in my mind drives my wife bats. So I guess I'm a peripatetic thinker. And you're certainly right that it beats sitting at a desk.
But your choice of "appearing" was right for that picture, as I wasn't walking but actually standing still - I was in the garden, looking at some very small aloe vera that I had split from a mature one and planted in a bare patch and wondering whether any of them would survive the summer while I was away in the UK and not giving them water (all of them did).
Tom
-
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.+1 - IMHO the correct answer should be '0 rows' or 'No rows'.
This could be why 44% got it wrong. 😎
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply