SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 NULL and equality
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 06/28/2013 :  10:03:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 06/28/2013 :  10:47:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 06/28/2013 :  10:57:43  Show Profile  Reply with Quote
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.

Edited by - James K on 06/28/2013 11:05:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/28/2013 :  11:29:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/28/2013 :  11:39:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 06/28/2013 :  12:26:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 06/28/2013 :  12:28:39  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/28/2013 :  14:00:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 06/28/2013 :  15:42:30  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/29/2013 :  14:38:45  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 08/19/2013 :  05:59:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000