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 «««23456»»»

Table Variable Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 7:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:55 AM
Points: 877, Visits: 876
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
Post #1448024
Posted Tuesday, April 30, 2013 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 6,043, Visits: 8,324
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1448026
Posted Tuesday, April 30, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448037
Posted Tuesday, April 30, 2013 8:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1448045
Posted Tuesday, April 30, 2013 8:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:51 AM
Points: 1,452, Visits: 254
Good question, learned something new today.
Thanks,
Lon
Post #1448051
Posted Tuesday, April 30, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 6,043, Visits: 8,324
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".



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1448062
Posted Tuesday, April 30, 2013 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
Hugo Kornelis (4/30/2013)
[quote]
<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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448070
Posted Tuesday, April 30, 2013 8:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Nice!
Post #1448093
Posted Tuesday, April 30, 2013 9:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 2,389, Visits: 2,780
Cool question. Thanks.

-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1448097
Posted Tuesday, April 30, 2013 9:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 1,061, Visits: 2,574
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1448099
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse