Checking NULL in WHERE clause outputs Non NULL values in the resultset

Question

Post reply

Checking NULL in WHERE clause outputs Non NULL values in the resultset

Kaushik Majumder

Ten Centuries

Points: 1009
More actions
April 29, 2009 at 4:58 am

#100620

Hi,
Facing some issues with the following query....
Select e_ina11,E_Create_Date,e_Org_FileName From Element
Where e_Ina11 Is Null
AND E_Create_Date > '2007-12-31'
And e_Org_FileName Like '%].xls%'
The reslutset,however, includes non null values for column e_Ina11 (which has been categorically specified for NULL in the WHERE clause)
Following is an example...
e_ina11 E_Create_Date e_Org_FileName
037189502 2008-02-06 12:08:39.117 [Tidy].xls
NULL 2008-05-02 12:11:40.980 Usage[1].xls
038737220 2008-06-26 10:37:35.060 Tracker [Tidy].xls
039661844 2008-09-15 10:39:47.740 [AT 30.06.08].xls
NULL 2008-01-15 15:47:13.253 Risks[1].xls
039661844 2008-09-15 10:39:43.363 [AT 30.06.08].xls
039700264 2008-09-17 14:51:17.540 [Appendix No. 9].xls
Ideally, the 2nd and 4th records would only have featured in the final resultset, while the rest should have been weeded out (as e_ina11 column is not null)....
The query plan generated as follows....
StmtText
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH: ([Bmk1000])=([Bmk1000]), RESIDUAL: ([Bmk1000]=[Bmk1000]))
|--Bitmap(HASH: ([Bmk1000]), DEFINE: ([Bitmap1003]))
| |--Hash Match(Inner Join, HASH: ([Bmk1000])=([Bmk1000]), RESIDUAL: ([Bmk1000]=[Bmk1000]))
| |--Bitmap(HASH: ([Bmk1000]), DEFINE: ([Bitmap1002]))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS: ([Bmk1000]))
| | |--Index Scan(OBJECT: ([DMProdDoc].[dbo].[ELEMENT].[I256_4]), WHERE: (like([ELEMENT].[E_ORG_FILENAME], '%].xls%', NULL)))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS: ([Bmk1000]), WHERE: (PROBE([Bitmap1002])=TRUE))
| |--Index Seek(OBJECT: ([DMProdDoc].[dbo].[ELEMENT].[I256_2]), SEEK: ([ELEMENT].[E_CREATE_DATE] > 'Dec 31 2007 12:00AM') ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION COLUMNS: ([Bmk1000]), WHERE: (PROBE([Bitmap1003])=TRUE))
|--Index Seek(OBJECT: ([DMProdDoc].[dbo].[ELEMENT].[E_INA11]), SEEK: ([ELEMENT].[E_INA11]=NULL) ORDERED FORWARD)
The strange point is some times the resultant output is perfect, (ie. the e_ina11 field contains only nulls).. I tried to reshuffle the WHERE conditions....the query plan remains the same.......the results are mostly erratic, most of the times they include the Non NULL values, however, sometimes they work as desired...
The table is quite large (10 Million Records approx) and all the columns e_ina11, E_Create_Date and e_Org_FileName have index defined on them.
Would much appreciate if you could provide any direction.
Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19252 More actions · Answer 1

If you replace 'Where e_Ina11 Is Null' with:

WHERE ISNULL(e_Ina11,0) = 0

does it run as you expect?

I try to avoid Nulls wherever possible as they can cause confusion.

A good article on the fun to be had with nulls is at:

[/url]

Steve Hall
Linkedin
Blog Site

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 2

Thanks for such quick response ! I tried to use the ISNULL function, but did not continue as it was taking too long....I will have a try and update.

...And thanks again for such interesting article.

Regads

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 3

Hi,

Using ISNULL seems to address the problem but the same absorbes more than an hour....

I was more interested to know what is the root cause of such behaviour, Any light on this matter please?

Regards

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Kaushik Majumder (4/29/2009)
Hi,
Facing some issues with the following query....
Select e_ina11,E_Create_Date,e_Org_FileName From Element
Where e_Ina11 Is Null
AND E_Create_Date > '2007-12-31'
And e_Org_FileName Like '%].xls%'
The reslutset,however, includes non null values for column e_Ina11 (which has been categorically specified for NULL in the WHERE clause)

I can see 2 possibilities:

1. You don't show the whole query.

2. It's time to run DBCC CHECKDB. At least to think about it.

_____________
Code for TallyGenerator

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 5

You can also try running the command

SET ANSI_NULLS OFF

at the beginning of your query. See http://msdn.microsoft.com/en-us/library/ms188048.aspx for more details....

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 6

I'd also try using coalesce instead of isnull. I've seen some posts about performance being better with coalesce.

Select e_ina11,E_Create_Date,e_Org_FileName From Element

Where coalesce(e_Ina11,1) = 1

AND E_Create_Date > '2007-12-31'

And e_Org_FileName Like '%].xls%'

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Bob Griffin (4/29/2009)
I'd also try using coalesce instead of isnull. I've seen some posts about performance being better with coalesce.
Select e_ina11,E_Create_Date,e_Org_FileName From Element
Where coalesce(e_Ina11,1) = 1
AND E_Create_Date > '2007-12-31'
And e_Org_FileName Like '%].xls%'

NO INSULL AND NO COALESCE!!!

Using any of functions in WHERE clause means no use of indexes or any kind of statistics.

If you run such query on 10 mil rows table your server will die under the pressure.

I believe OP has already indicated this.

P.S. ISNULL performs better then COALESCE.

_____________
Code for TallyGenerator

Kaushik Majumder Ten Centuries Points: 1009 More actions · Answer 8

Thanks for all your valuable advices..... Apology, am a bit late in getting back...(it was night at this part of globe....), I will try as suggested and update at the soonest.

Regards.