April 30, 2013 at 7:28 am
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false. How could it return unknown? It is very clearly known that @ToCreate is not 1.
I believe he's in agreement with you, but indicating that the documentation says otherwise.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
April 30, 2013 at 7:32 am
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false.
SQL uses three-valued logic, not true Boolean logic. Any comparison with a NULL (except when using IS [NOT] NULL) will not result in True or False, but in Unknown.
For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.
For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.
April 30, 2013 at 7:48 am
Hugo Kornelis (4/30/2013)
Sean Lange (4/30/2013)
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false.SQL uses three-valued logic, not true Boolean logic. Any comparison with a NULL (except when using IS [NOT] NULL) will not result in True or False, but in Unknown.
For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.
For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.
I understand the way NULL is evaluated when doing comparisons.
select * from table where MyNullColumn = MyNonColumn.
I understand that returns NULL.
However for the boolean condition in this question, the TRUE condition is not returned. Therefore it will fall to the else. Maybe I am missing the semantics here. Maybe Tom is just splitting hairs about the verbiage of BOL. Pretty sure we are all on the same page.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2013 at 8:01 am
Danny Ocean (4/29/2013)
Good question with important information.
I'll second that... then again my standards are to do any and all declares at the beginning of my SQL Batches/Stored Procs/Functions so I've never run into this... but it is good to know.
April 30, 2013 at 8:12 am
Good question, learned something new today.
Thanks,
Lon
April 30, 2013 at 8:25 am
Sean Lange (4/30/2013)
I understand the way NULL is evaluated when doing comparisons.
select * from table where MyNullColumn = MyNonColumn.
I understand that returns NULL.
<nitpicking>
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
</nitpicking>
However for the boolean condition in this question, the TRUE condition is not returned. Therefore it will fall to the else. Maybe I am missing the semantics here. Maybe Tom is just splitting hairs about the verbiage of BOL. Pretty sure we are all on the same page.
I'm not sure if I'd call it splitting hairs, but Tom is indeed commenting on the BOL verbiage. BOL says that the first statement or block is executed if the expression evaluates to True, and the second (following ELSE) is it evaluates to False. That would imply that neither is executed when it evaluates to Unknown. The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".
April 30, 2013 at 8:39 am
Hugo Kornelis (4/30/2013)
<nitpicking>
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
</nitpicking>
My example was a bad one for sure. I am well aware of the three way "boolean" logic in sql.
The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".
I agree. They should just make it "Otherwise". This is actually what happens. For any reason that the condition is NOT true the ELSE block will execute.
For me, no matter how many ways I try to read the BOL entry it is clear (even given that the reason it is not true is rather ambiguous). Removing the word FALSE in this case would actually add clarity. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2013 at 8:57 am
Nice!
April 30, 2013 at 9:09 am
Cool question. Thanks.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 30, 2013 at 9:12 am
Hugo Kornelis (4/30/2013)
For WHERE and IF, a logic result of True qualifies, and False and Unknown do not qualify.For CHECK constraints, a logic result of True or Unknown qualifies and False doesn't.
Thanks for pointing this out, Hugo. I wonder why MS would inflict this on the unwary:
A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table.
As Hugo pointed out, SQL Server uses 3-part Boolean logic, where it can't be proven categorically that (NOT FALSE) = TRUE.
Jason Wolfkill
April 30, 2013 at 10:19 am
Thanks for the question
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
April 30, 2013 at 10:25 am
Sean Lange (4/30/2013)
Hugo Kornelis (4/30/2013)
<nitpicking>
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
</nitpicking>
My example was a bad one for sure. I am well aware of the three way "boolean" logic in sql.
The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".
I agree. They should just make it "Otherwise". This is actually what happens. For any reason that the condition is NOT true the ELSE block will execute.
For me, no matter how many ways I try to read the BOL entry it is clear (even given that the reason it is not true is rather ambiguous). Removing the word FALSE in this case would actually add clarity. 😉
Hugo,
Question if you have time.
<furthernitpicking>
Not NULL - Unknown. Is unknown also called uninitialized?
</furthernitpicking>
Just wondering?
Thanks
Not all gray hairs are Dinosaurs!
April 30, 2013 at 11:17 am
Miles Neale (4/30/2013)
Hugo,Question if you have time.
<furthernitpicking>
Not NULL - Unknown. Is unknown also called uninitialized?
</furthernitpicking>
Just wondering?
This will cost you more time than it'll cost me! 😛
Short answer: no.
Long answer:
1. NULL - The database's black hole
2. The logic of three-valued logic
3. Dr. Unknown, or how I learned to stop worrying and love the NULL
4. What if null if null is null null null is null?
Enjoy! 😉
April 30, 2013 at 12:26 pm
Thanks for the question, Sergiy!
And thanks to Hugo for his insightful comments!
April 30, 2013 at 3:03 pm
For those struggling with the difference between 'False' and 'Unknown', and how the IF statement works, consider what happens if we change the example code a little bit (I think this could have added a couple of wrong answers to the results 🙂 )
DECLARE @ToCreate bit;
IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';
SELECT * FROM @Table;
It now looks like the DECLARE should be executed, as @ToCreate is clearly not 1, but it isn't, as the comparison evaluates to 'Unknown'. This forces the ELSE part of the IF statement.
Hope this helps to clarify why Tom's whinge on BOL is important!
And, on the subject of missing information on BOL, I couldn't find any reference to when variables are created on the link provided...
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply