Is this a bug in IsNull?

I think I uncovered a bug in IsNull. Could someone check to see If I am
missing something here?
My view has the following code in it.
SELECT
v.QuoteId,
v.QuoteLineID,
pmc.BadLine AS BadLine1,
pic.BadLine AS BadLine2,
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
(CASE
WHEN pmc.BadLine IS NULL
THEN IsNull(pmc.BadLine, 0)
ELSE pmc.BadLine
END) +
(CASE
WHEN pic.BadLine IS NULL
THEN IsNull(pic.BadLine, 0)
ELSE pic.BadLine
END) InvalidPackage2
FROM QuoteLineValues v
LEFT JOIN PackageInvalidComponents pic
ON v.QuoteID = pic.QuoteID
AND v.QuoteLineID = pic.QuoteLineID
LEFT JOIN PackageMissingComponents pmc
ON v.QuoteID = pmc.QuoteID
AND v.QuoteLineID = pmc.QuoteLineID
When I select from this view I get
BadLine1 = NULL
BadLine2 = NULL
InvalidPackage = 2
InvalidPackage2 = 0
InvalidPackage should have the same exact value as InvalidPackage2. But it
doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
evalutate to 1 when pmc.BadLine by itself is NULL.
Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
missing something?
JeremySELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
Jens.
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>I think I uncovered a bug in IsNull. Could someone check to see If I am
> missing something here?
> My view has the following code in it.
> SELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
> FROM QuoteLineValues v
> LEFT JOIN PackageInvalidComponents pic
> ON v.QuoteID = pic.QuoteID
> AND v.QuoteLineID = pic.QuoteLineID
> LEFT JOIN PackageMissingComponents pmc
> ON v.QuoteID = pmc.QuoteID
> AND v.QuoteLineID = pmc.QuoteLineID
> When I select from this view I get
> BadLine1 = NULL
> BadLine2 = NULL
> InvalidPackage = 2
> InvalidPackage2 = 0
> InvalidPackage should have the same exact value as InvalidPackage2. But it
> doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
> evalutate to 1 when pmc.BadLine by itself is NULL.
> Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
> missing something?
> Jeremy|||Jens,
You are right in saying that there is no reason to check it again since I
already checked for null in the case statement. I was trying to exagerate th
e
absurdity of the values that IsNull returns. e.g. Inside a case statement
IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
IsNull(pic.BadLine, 0) returns 1.
Does that make any sense?
Jeremy
"Jens Sü?meyer" wrote:

> SELECT
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> Jens.
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>
>|||Try COALESCE().
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
> the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Smeyer" wrote:
>|||> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
computer?
What data type is pic.BadLine?
AMB
"Jeremy Lubich" wrote:
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Sü?meyer" wrote:
>|||Mike, et al.,
I tried COALESCE and got an interesting result. Here is what is returned by
QA.
pic.BadLine = NULL
IsNull(pic.BadLine, 0) = 1
COALEASCE(pic.BadLine, 0) = 0
Your workaround works. But, I am still stuck with the fact that IsNull looks
like it has a bug. I am worried about other parts of our database where we
also use IsNull.
I would think both of these values would return 0. Does it make any sense
why the IsNull and COALEASCE funtions do not return the same value when the
first argument is NULL?
If someone replies to this thread and says, "yup, that looks like a bug to
me", then I'll contact Microsoft to have it investigated. I just didn't want
to be presumptious in my hypothesis before I threw this out into the
community discussion.
Jeremy
"Michael C#" wrote:

> Try COALESCE().
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
>
>|||What data type is BadLine?
AMB
"Jeremy Lubich" wrote:
> Mike, et al.,
> I tried COALESCE and got an interesting result. Here is what is returned b
y
> QA.
> pic.BadLine = NULL
> IsNull(pic.BadLine, 0) = 1
> COALEASCE(pic.BadLine, 0) = 0
> Your workaround works. But, I am still stuck with the fact that IsNull loo
ks
> like it has a bug. I am worried about other parts of our database where we
> also use IsNull.
> I would think both of these values would return 0. Does it make any sense
> why the IsNull and COALEASCE funtions do not return the same value when th
e
> first argument is NULL?
> If someone replies to this thread and says, "yup, that looks like a bug to
> me", then I'll contact Microsoft to have it investigated. I just didn't wa
nt
> to be presumptious in my hypothesis before I threw this out into the
> community discussion.
> Jeremy
> "Michael C#" wrote:
>|||AMB,
Here is the DDL and statements you will need. I can reproduce it on any
database.
CREATE TABLE [dbo].[Packages] (
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Quantity] [int] NOT NULL ,
[Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[QuoteLines] (
[QuoteId] [int] NOT NULL ,
[QuoteLineId] [int] NOT NULL ,
[ParentId] [int] NOT NULL ,
[LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO QuoteLines
(QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
VALUES
(1234, 1, 0, 'S', 1, 'item-x')
GO
CREATE VIEW dbo.pkgComponents
AS
SELECT l1.QuoteID,
l1.QuoteLineID,
l1.StyleNumber,
l2.StyleNumber ComponentStyle,
l2.Quantity / l1.Quantity Quantity
FROM QuoteLines l1
JOIN QuoteLines l2
ON l1.QuoteID = l2.QuoteID
AND l1.QuoteLineID = l2.ParentID
GO
CREATE VIEW dbo.pkgMissingComponents
AS
--Find Quote Lines with Invalid Components
SELECT
1 BadLine,
l.QuoteID,
l.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
LEFT JOIN
pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for conditions that can make a package in config invalid:
--1. A Component has been added to the package definition, but not in Quote
Lines
WHERE lpack.ComponentStyle IS NULL
GROUP BY
l.QuoteID,
l.QuoteLineID
GO
CREATE VIEW dbo.pkgInvalidComponents
AS
--Find Quote Lines with Wrong Package Definitions
SELECT
1 BadLine,
lpack.QuoteID,
lpack.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
RIGHT JOIN pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for two conditions that can make a package in QuoteLines invalid:
--1. Quantities on a component don't match package definition
--2. A Component remianing in the QuoteLines has been removed from the
master package definition
WHERE
p.Quantity <> lpack.Quantity
OR p.ComponentStyle IS NULL
GROUP BY
lpack.QuoteID,
lpack.QuoteLineID
GO
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
Jeremy
"Alejandro Mesa" wrote:
> Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
> computer?
> What data type is pic.BadLine?
>
> AMB
> "Jeremy Lubich" wrote:
>|||Jeremy,
I do not know what it is wrong, but the problem is not ISNULL. If you cast
the values of pic.BadLine and pmc.BadLine then you will see 1 also.
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
IsNull(pmc.BadLine, 0) as colA,
pmc.BadLine,
cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
cast(pmc.BadLine as int) as [pmc_BadLine_int],
IsNull(pic.BadLine, 0),
pic.BadLine,
cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
cast(pic.BadLine as int) as [pic.BadLine_int]
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
AMB
"Jeremy Lubich" wrote:
> AMB,
> Here is the DDL and statements you will need. I can reproduce it on any
> database.
> CREATE TABLE [dbo].[Packages] (
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Quantity] [int] NOT NULL ,
> [Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[QuoteLines] (
> [QuoteId] [int] NOT NULL ,
> [QuoteLineId] [int] NOT NULL ,
> [ParentId] [int] NOT NULL ,
> [LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [int] NOT NULL ,
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QuoteLines
> (QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
> VALUES
> (1234, 1, 0, 'S', 1, 'item-x')
> GO
> CREATE VIEW dbo.pkgComponents
> AS
> SELECT l1.QuoteID,
> l1.QuoteLineID,
> l1.StyleNumber,
> l2.StyleNumber ComponentStyle,
> l2.Quantity / l1.Quantity Quantity
> FROM QuoteLines l1
> JOIN QuoteLines l2
> ON l1.QuoteID = l2.QuoteID
> AND l1.QuoteLineID = l2.ParentID
> GO
> CREATE VIEW dbo.pkgMissingComponents
> AS
> --Find Quote Lines with Invalid Components
> SELECT
> 1 BadLine,
> l.QuoteID,
> l.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> LEFT JOIN
> pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for conditions that can make a package in config invalid:
> --1. A Component has been added to the package definition, but not in Quot
e
> Lines
> WHERE lpack.ComponentStyle IS NULL
> GROUP BY
> l.QuoteID,
> l.QuoteLineID
> GO
> CREATE VIEW dbo.pkgInvalidComponents
> AS
> --Find Quote Lines with Wrong Package Definitions
> SELECT
> 1 BadLine,
> lpack.QuoteID,
> lpack.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> RIGHT JOIN pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for two conditions that can make a package in QuoteLines invalid:
> --1. Quantities on a component don't match package definition
> --2. A Component remianing in the QuoteLines has been removed from the
> master package definition
> WHERE
> p.Quantity <> lpack.Quantity
> OR p.ComponentStyle IS NULL
> GROUP BY
> lpack.QuoteID,
> lpack.QuoteLineID
> GO
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> Jeremy
> "Alejandro Mesa" wrote:
>|||AMB,
I have no experience with reporting bugs to Microsoft. It looks to me if I
try and email them this problem it will cost me $99. Do you know a way of
getting Microsofts attention without it costing $. How would you have this
investigated?
Jeremy
"Alejandro Mesa" wrote:
> Jeremy,
> I do not know what it is wrong, but the problem is not ISNULL. If you cast
> the values of pic.BadLine and pmc.BadLine then you will see 1 also.
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
> IsNull(pmc.BadLine, 0) as colA,
> pmc.BadLine,
> cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
> cast(pmc.BadLine as int) as [pmc_BadLine_int],
> IsNull(pic.BadLine, 0),
> pic.BadLine,
> cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
> cast(pic.BadLine as int) as [pic.BadLine_int]
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> AMB
> "Jeremy Lubich" wrote:
>