Query using IS NULL returns different rows each time!
-
Hi,
Can anyone help me with the strange behaviour of this query?
select count(*) from BIGTABLE where CRID is null
The table, BIGTABLE has 96 million rows. I have a non-unique, non-clustered index on CRID. CRID is varchar(3) nullable and has the default database collation of Latin1_General_CI_AS.
When I run this query from a Query Analyser window several times in a row, I get different results! Sometimes 90,000,000 sometimes 94,000,000 sometimes 92,000,000 but every time a different number.
Now I know there are no other processes doing any work on this database. I checked by looking at all the rows in master..sysprocesses, and sure enough, there is only one row for my Query Analyser connection. I double-checked by running SQL Profiler against the database, and the only statements I see being executed are my SELECT statements.
I have tried dropping and recreating the index, but the strange behaviour remains. Interestingly, when I execute
select count(*) from BIGTABLE where isnull(CRID, -1) = -1
it seems to work ok - I get the result of 53,662,710 each and every time. Does this indicate some problem with the way IS NULL works differently to the ISNULL() function? Or do I have database corruption?
Another interesting thing is this: when I execute
select count(*) from BIGTABLE where CRID is null and CRID is not null
I actually get a result of 37,096,674 (it should be 0!!)
Any idea what is going on here?
-Steve
-
Nulls are tricky.
Depending on how you write your query, you will get different results. = null is not the same as is null. (why???)
And, are your ansi null settings on or off?
Best of luck,
Sara
-
I cannot suggest any explaination for the IS NULL behaviour, however since you say that CRID is varchar(3) and BOL states:
ISNULL ( check_expression , replacement_value )
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Your Use of isnull(CRID, -1) = -1 should be isnull(CRID, '1') = '1'
Andy
-
The way I look at it...
NULL means unknown or undefined.
Which is not nothing, Nothing means you know you should have something.
So how can you use '= NULL'? Or equal to unknown?
It does not make sense.
If you have 2 nullable columns, 1 integer, 1 text NULLS in each then this makes even less sense:
WHERE intcol = NULL AND textcol = NULL
because, to me, it implies that intcol can equal textcol, which is simply rubbish
So 'IS NULL' removes this anomaly.
Back to the issue at hand:
Not a clue.
I have seen odd behaviuor before with NULLS and outer joins, but not in a straight forward situation liek this
-
A couple of thoughts.
Is the column indexed? Are the stats up to date?
On a big table maybe out of date stats could give this sort of anomaly.
David
If it ain't broke, don't fix it...
-
Is your query parallellarisalized
? If so, are you on SP3 or prior / the latest SP ?Found this MS KB -
http://support.microsoft.com/default.aspx?scid=KB;en-us;Q814509
Sounds similar to a problem (wayyyy back) I remember with DB2 on a parallel hoojiwotsit box which couldn't figure out how to do null counts properly when gathering in results from multiple nodes, so returned incorrect results.
Jon
-
Yes, I just found this KB article in a discussion forum on http://www.alegsa.com.ar/Visitas/index4/Null%20in%20where%20clause%20problem%20sql.php
My SQL Server does have 4 CPUs and I do have parallelism enabled. When I execute the same queries but include OPTION(MAXDOP 1) then everything works fine.
I will now look into applying SP4 which resolves this issue.
-Steve
-
We were having exactly the same problem. It had us scratching our heads for a couple of days, but the MAXDOP hint solves it, and we'll be patching to .818 soon. I hope.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply