is null

SQL 2k sp4
Since I can't really share my ddl, I'm hoping someone has seen a situation
like this who knows the answer to my question.
Why would a query that contains a WHERE clause that says Field1 IS NULL
return records where there is data in Field1? My query has a couple of
JOINs, but is really as simple as:
SELECT EmployeeID FROM Northwind.dbo.Employees where EmployeeID IS NULL
The strange thing is that my results have records where EmployeeID has data.
I've never seen anything like this before and can't figure it out. Could it
be some sort of corruption?
Thanks in advance, Andre> Since I can't really share my ddl,
Simplify it and rename your columns/tables etc. so that we can reproduce the
result without having access to your ancient chinese secrets.|||It *could* be some sort of corruption but it seems far more likely that
there's a flaw in your query logic. If you choose not to post the actual
query we'll probably never know.
David Portas
SQL Server MVP
--|||On Mon, 1 Aug 2005 13:41:34 -0700, Andre wrote:

>SQL 2k sp4
>Since I can't really share my ddl, I'm hoping someone has seen a situation
>like this who knows the answer to my question.
>Why would a query that contains a WHERE clause that says Field1 IS NULL
>return records where there is data in Field1? My query has a couple of
>JOINs, but is really as simple as:
>SELECT EmployeeID FROM Northwind.dbo.Employees where EmployeeID IS NULL
>The strange thing is that my results have records where EmployeeID has data
.
>I've never seen anything like this before and can't figure it out. Could i
t
>be some sort of corruption?
>Thanks in advance, Andre
>
Hi Andre,
Some wild guesses, based on a query that does not replicate the problem
and does not match the description "has a couple of JOINs" either.
- Are you actually querying a table, or a view?
- If a VIEW, is it declared with SELECT column list or SELECT *?
- If SELECT *, has the table recently been ALTER'ed or DROP'ped and
reCREATE'd?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, are any of the JOINs using OUTER JOIN?
Again, without more details then this, we probably won't be able to offer
more useful advise.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4h9te1ht6n49e7lrf6s0q4nv3d531rkmsb@.
4ax.com...
> On Mon, 1 Aug 2005 13:41:34 -0700, Andre wrote:
>
> Hi Andre,
> Some wild guesses, based on a query that does not replicate the problem
> and does not match the description "has a couple of JOINs" either.
> - Are you actually querying a table, or a view?
> - If a VIEW, is it declared with SELECT column list or SELECT *?
> - If SELECT *, has the table recently been ALTER'ed or DROP'ped and
> reCREATE'd?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Thanks for all the replies.
I've extracted the columns from my tables that the query uses, and have atta
ched them. My original query is also attached. I appreciate any sense that
anyone can make out of why my query returns records where PrimaryInventoryI
D has data, when the query clearly specifies I only want rows where it "is n
ull"
Thanks, Andre|||On Thu, 4 Aug 2005 13:47:24 -0700, Andre wrote:

>Thanks for all the replies.
>I've extracted the columns from my tables that the query uses, and have attached th
em. My original query is also attached. I appreciate any sense that anyone can mak
e out of why my query returns records where PrimaryInventoryID has data, when the qu
ery
clearly specifies I only want rows where it "is null"
>Thanks, Andre
Hi Andre,
Looks straightforward enough, indeed. I'm afraind I can't offer a
logical explanation of the results you're seeing.
I notice an index on the PrimaryInventoryID column. Can you check the
execution plan to see if this index is being used? If so, try if
dropping and recreating the index solve the problem. (If it does, the
conclusion would have to be that this index got corrupted).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Andre,
I have two ideas, but I suspect you did not cut-and-paste your
query, so it's hard to tell. These are longshots, but they could
explain the behavior.
(Actually, three ideas - idea zero is that you aren't really on SP4.
Now and then, someone is certain they are, until they run SELECT @.@.VERSION.
Can you run that and paste the output here?)
1. A non-standard carriage return in the query text. This is a little
like a rare disease - I see it maybe once or twice a year, but it's
crazymakin.
If you have a comment you aren't showing us, i.e., something like
select ...
where <thisCondition> -- A comment here
and c IS NULL
*and* (I said it was a longshot), there is a non-standard newline, such
as a char(13) without a char(10), after "A comment here", the IS NULL
condition will appear to you to be part of the query, but to the
processor, it
will appear to be part of the comment.
2. Case sensitivity. Your query should return rows for which
tblInventories.primaryinventoryid IS NULL. However, your query
returns tblInventories.primaryInventoryid. Perhaps there are rows
in tblInventories for which the primaryinventoryid column IS NULL,
but where the primaryInventoryid column is not null
If your database uses a case-sensitive collation, then primaryinventoryid
and primaryInventoryid are different columns. You could also have multiple
tables (or views) with similar names, too. The query selects from
tblinventoryreport
but you show the DDL for tblInventoryReport.
This is really a longshot, because your table doesn't have either a
primaryInventoryid or primaryinventoryid column, but a
primaryInventoryID column
If any of what you provided here is retyped, as opposed to cut-and-paste,
let us know, and if possible, give us the cut-and-paste version.
Other than these, only when there are views in the picture have I seen
this sort of thing due to a bug in SQL Server (most of these are fixed,
also).
Steve Kass
Drew University
Andre wrote:

>Thanks for all the replies.
>I've extracted the columns from my tables that the query uses, and have attached th
em. My original query is also attached. I appreciate any sense that anyone can mak
e out of why my query returns records where PrimaryInventoryID has data, when the qu
ery
clearly specifies I only want rows where it "is null"
>Thanks, Andre
>
>---
>/*
>select i.inventoryid, i.primaryInventoryid, isv.coverageID
>from tblInventories i
>inner join tblInventoryServices isv on i.inventoryID = isv.inventoryID
>inner join tblinventoryreport ir on i.inventoryid = ir.inventoryid
>where assetendDate between getdate() and '2005-12-31'
>and i.primaryinventoryid is null
>
>
>
>CREATE TABLE [dbo].[tblInventories] (
>[InventoryID] [bigint] IDENTITY (1, 1) NOT NULL ,
>[PrimaryInventoryID] [bigint] NULL ,
>[ImportStatusID] [bigint] NOT NULL ,
>[LastModifiedOn] [datetime] NOT NULL ,
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tblInventories] WITH NOCHECK ADD
> CONSTRAINT [tblInventories_PK] PRIMARY KEY CLUSTERED
> (
> [InventoryID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tblInventories] ADD
> CONSTRAINT [DF_tblInventories_ImportStatusID] DEFAULT (1) FOR [ImportStatusID],
> CONSTRAINT [DF_tblInventories_LastModifiedOn] DEFAULT (getdate()) FOR [LastModifiedOn]
>GO
> CREATE INDEX [PrimaryINventoryID_IX] ON [dbo].[tblInventories]([PrimaryInventoryID]) ON [PRIMARY]
>GO
>
>CREATE TABLE [dbo].[tblInventoryServices] (
>[InventoryServiceID] [bigint] IDENTITY (1, 1) NOT NULL ,
>[CoverageID] [bigint] NOT NULL ,
>[InventoryID] [bigint] NOT NULL ,
>[LastModifiedOn] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tblInventoryServices] WITH NOCHECK ADD
> CONSTRAINT [tblInventoryServices_PK] PRIMARY KEY CLUSTERED
> (
> [InventoryServiceID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tblInventoryServices] ADD
> CONSTRAINT [DF_tblInventoryServices_LastModifiedOn]
DEFAULT (getdate()) FOR [LastModifiedO
n]
>GO
>CREATE TABLE [dbo].[tblInventoryReport] (
> [InventoryID] [bigint] NOT NULL ,
> [InventoryServiceID] [bigint] NOT NULL ,
> [AssetEndDate] [datetime] NULL ,
> ) ON [PRIMARY]
>GO
> CREATE UNIQUE CLUSTERED INDEX [TblInventoryReport_ISVC_IX] ON [dbo]. [tblInventoryReport]([InventoryServiceID
]) WITH FILLFACT
OR = 90 ON [PRIMARY]
>GO
>
>*/
>|||Steve,
Thanks for the reply. As I've said before, you're an asset to these groups.
First I need to clarify one thing. This isn't actually my db but a friend's
whom I'm helping sort this out. That will at least explain why I thought
they were on sp4 when in reality they are on sp3a. Are you aware of this
issue occuring in sp3a? I'm going to recommend they get on sp4 ASAP but I'm
not sure when that will be able to happen.
The only thing I removed from the query was the db names. The query is a
cross-db query, but I'm not sure what difference that would make. Below is
the actual query I'm running. We're running case-insensitive, as confirmed
by sp_helpsort. There are no views involved; the 3 tables listed below are
indeed tables.
Thanks again, Andre
select i.inventoryid,
i.primaryInventoryid,
isv.coverageID
from mnetSource..tblInventories i
inner join mnetSource..tblInventoryServices isv on i.inventoryID =
isv.inventoryID
inner join MnetReport..tblinventoryreport ir on i.inventoryid =
ir.inventoryid
where ir.assetendDate between getdate() and '2005-12-31'
and i.primaryInventoryid is null