Author |
Topic |
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 10:03:07
|
Can anyone help me understand why the Inequality column in the following is the way it is? I would have expected it to be the opposite of the Equality column. It is almost as though SQL Server is ignoring my brackets and doing its own three-valued logic thing.CREATE TABLE #t1 (id INT, val INT ); CREATE TABLE #t2 (id INT, val INT);INSERT INTO #t1 VALUES (1,NULL),(2,2),(3,3),(4,NULL), (5,7);INSERT INTO #t2 VALUES (1,1),(2,NULL),(3,3),(4,NULL), (5,8);SELECT a.*,b.*, -- which columns are equal (consider NULL=NULL) CASE WHEN a.val = b.val OR COALESCE(a.val,b.val) IS NULL THEN 1 ELSE 0 END Equality, -- negate the above logic CASE WHEN NOT (a.val = b.val OR COALESCE(a.val,b.val) IS NULL ) THEN 1 ELSE 0 END InequalityFROM #t1 a INNER JOIN #t2 b ON a.id = b.id; DROP TABLE #t1; DROP TABLE #t2; For now I wrapped the case expression in another case expression, and that works as I would expect. |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-06-28 : 10:47:02
|
NULL does not equal NULL, nor does NULL NOT equal NULL. Does that make sense? declare @yak int = nulldeclare @foo int = nulldeclare @bar int = 3declare @hap int = 3declare @hhh int = 9select case when not @yak = @foo then 1 else 0 end ,case when (@yak = @foo) then 1 else 0 end ,case when not @bar = @hap then 1 else 0 end ,case when (@bar = @hap) then 1 else 0 end ,case when not @bar = @hhh then 1 else 0 end ,case when (@bar = @hhh) then 1 else 0 end ,case when not @bar = @yak then 1 else 0 end ,case when (@bar = @yak) then 1 else 0 end How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 10:57:43
|
Don, I get that about NULL, that it is neither equal nor unequal. My assumption was the following:1. the result of the logical expression within the red brackets is a true or false. (It has to be because in the expression for the Equality column, I am using exactly that for the WHEN clause which requires something that evaluates to TRUE or FALSE)2. If my statement above is correct, then when I wrap that logical expression with the red brackets I have gotten past all the 3-valued issues with NULLs, and I should be getting a boolean result of TRUE or FALSE.3. That boolean result is now fed into the NOT operator. According to MSDN, a NOT operator simply takes a boolean expression and negates it.http://msdn.microsoft.com/en-us/library/ms189455.aspxBut what it seems like teh NOT operator is doing is to reach into the inner expression and doing something which it has no business in.I do see what you are saying, that doesn't mean I have to like that behavior of SQL server. I guess what I would like to have had is a nullable boolean result of some sort. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 11:29:12
|
AS per boolean algebraNOT (a.val = b.val OR COALESCE(a.val,b.val) IS NULL )is equivalent toNOT(a.val=b.val) AND NOT(COALESCE(a.val,b.val) IS NULL )so for (1,1) and (1,NULL) set it translates toNOT(1=NULL) AND NOT(1 IS NULL)UNKNOWN AND TRUEas as per BOL under default (ANSI NULL) settings its as followsWhen SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWNso result will be UNKNOWN and NOT over it never returns TRUE (it will be UNKNOWN) hence it will fail WHEN clause and goes to ELSE part giving you 0http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-28 : 11:39:07
|
James, I think your confusion is in the assumption that the red parentheses (in this case) would isolate the expression and the NOT would reverse it but that is not true. the parentheses is part of the expression and simply would/could affect the precedence order. Something like this would work as you intended: (which is similar to what you said you did "For now I wrapped the case expression in another case expression") -- negate the above logic convert(bit, CASE WHEN a.val = b.val OR COALESCE(a.val,b.val) IS NULL THEN 1 ELSE 0 END ^ 1 ) I find this boolean logic mind bending. If possible I will always try to find a method that I (or anyone) can understand without getting a headache.Be One with the OptimizerTG |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 12:26:24
|
TG, indeed, the incorrect assumption that I had was that the presence of the brackets would isolate the expressions within it from the outside. But it doesn't and so the three-valued monster gets to the NOT operator as well.What I was trying to do was to compare two tables to see if any columns had changed based on the primary key, and make the comparison expression as short as possible. I ended up hashing the columns. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 12:28:39
|
Visakh, what you posted logically explains it. SQL Server is indeed doing what I am asking it to do - i.e., it takes the inner expression and evaluates it and then applies NOT operator to it. The thing I had missed was what Don had pointed out - that the result of the inner expression is neither true nor false when there is a null involved. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 14:00:01
|
quote: Originally posted by James K Visakh, what you posted logically explains it. SQL Server is indeed doing what I am asking it to do - i.e., it takes the inner expression and evaluates it and then applies NOT operator to it. The thing I had missed was what Don had pointed out - that the result of the inner expression is neither true nor false when there is a null involved.
Yep...I got it Which is why I posted the link whichh explains on ANSI NULL settings and behaviour of comparisons with NULL based on it ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-06-28 : 15:42:30
|
I meant to post what visakh posted, but couldn't find the right words So i settled for the super simple approach.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-29 : 14:38:45
|
quote: Originally posted by DonAtWork I meant to post what visakh posted, but couldn't find the right words So i settled for the super simple approach.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Yep..it indeed was a simpler illustration of my explanation ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-08-19 : 05:59:45
|
I would also prefer swapping 1 and 0 over using NOT or bitwise operatorCASE WHEN a.val = b.val OR COALESCE(a.val,b.val) IS NULL THEN 0 ELSE 1 END Inequality, MadhivananFailing to plan is Planning to fail |
|
|
|