Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 NULL and equality

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 Inequality
FROM
#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 = null
declare @foo int = null
declare @bar int = 3
declare @hap int = 3
declare @hhh int = 9


select 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

Go to Top of Page

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.aspx

But 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 11:29:12
AS per boolean algebra

NOT (a.val = b.val OR COALESCE(a.val,b.val) IS NULL )
is equivalent to

NOT(a.val=b.val) AND NOT(COALESCE(a.val,b.val) IS NULL )

so for (1,1) and (1,NULL) set it translates to

NOT(1=NULL) AND NOT(1 IS NULL)
UNKNOWN AND TRUE

as as per BOL under default (ANSI NULL) settings its as follows

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN

so 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 0

http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 operator

CASE
WHEN
a.val = b.val OR COALESCE(a.val,b.val) IS NULL
THEN 0 ELSE 1 END Inequality,


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -