Is this a bug in IsNull?

[Jeremy Lubich]

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 Süßmeyer]

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

--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" <Jeremy...@discussions.microsoft.com> schrieb im
Newsbeitrag news:9AFA4D52-7BAC-4583...@microsoft.com...

[Jeremy Lubich]

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

[Michael C#]

Try COALESCE().

"Jeremy Lubich" <Jeremy...@discussions.microsoft.com> wrote in message
news:EC91E50A-6D5F-4F67...@microsoft.com...

[Alejandro Mesa]

> 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]

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

[Alejandro Mesa]

What data type is BadLine?

AMB

[Jeremy Lubich]

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]

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]

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

[Jeremy Lubich]

AMB,

I have no experience in reporting bugs to Microsoft. It looks to me that if
I email them this problem it will cost $99. Do you know of a better way to
have Microsoft look into this?

Jeremy

[Michael C#]

Is it just me, or does the following line exist in your code?

"SELECT 1 BadLine" ?

This SELECTS the constant Value "1" and Aliases it with the name "BadLine".

"Jeremy Lubich" <Jeremy...@discussions.microsoft.com> wrote in message

news:591DBCC9-7484-4172...@microsoft.com...

[Jeremy Lubich]

Mike,

Yes, I do select 1 and alias it with BadLine. If you do a select on
PackageMissingComponents and PackageInvalidComponents you will see that there
are no rows in those views. Thus when a left join to QuoteLines is performed
on them the values in BadLine should be NULL, right? Shouldn't IsNull handle
an aliased column?

Jeremy

[Aaron [SQL Server MVP]]

What is this?

SELECT
1 BadLine

Is that supposed to be TOP 1 ?

As Michael points out, this selects the constant value 1, and aliases it as
BadLine. I think this is why this is coming out as 1 -- you assigned it
that value (it isn't looking in the table).

--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

"Jeremy Lubich" <Jeremy...@discussions.microsoft.com> wrote in message
news:591DBCC9-7484-4172...@microsoft.com...

[Aaron [SQL Server MVP]]

> are no rows in those views. Thus when a left join to QuoteLines is
> performed
> on them the values in BadLine should be NULL, right?

Why? You're not telling it to get it from any table. You've assigned it a
constant value of 1.

Can you try it without the weird alias syntax please?

[Michael C#]

Theory is great, let's put it to practice. Try changing "SELECT 1 BadLine"
to "SELECT 2 BadLine". If your ISNULL statement then returns a "2" instead
of a "1", you've found your problem.

"Jeremy Lubich" <Jeremy...@discussions.microsoft.com> wrote in message

news:E3F8EAEE-FD21-452F...@microsoft.com...

[Jeremy Lubich]

Aaron,

I do select a constant value of 1. The view is very helpful when I need to
aggregate the number of component styles that do not match the master package
definition. However, the left join to the QuoteLines table should bring back
NULL since there are no rows that fulfill the join criteria. The item put
into the QuoteLines table in the DDL provided is not a package, and should
have a value of 0 in the InvalidPackage column of the select statement.

Have you run the query for yourself?

Jeremy

[Aaron [SQL Server MVP]]

> Have you run the query for yourself?

No, I'm actually trying to work, and help people solve their own problems on
the side.

Have you run the query without the alias?

[Aaron [SQL Server MVP]]

I still think this is observing the value of your constant instead of
looking at the table (which isn't even there due to the left join), but only
when addition is applied. Observe the result of this minor modification
(without the comments, which totally screw up the DDL as you try to run it):

SELECT TOP 1
ql.QuoteID,
ql.QuoteLineID,

pmc.BadLine,
pic.BadLine,

addition1 = IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0),
addition2 = COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0),
addition3 = pmc.BadLine + pic.BadLine,
addition4 = IsNull(pmc.BadLine + pic.BadLine, 0),
anotherCoalesce = COALESCE(pmc.BadLine, pic.BadLine, 0)

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

Anyway, it might be a bug, but with efforts focused on SQL Server 2005, and
the unlikelihood that this affects many customers, I wouldn't expect an
immediate, all-hands-on-deck hotfix. Just use COALESCE instead of ISNULL.

--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

[Aaron [SQL Server MVP]]

FWIW, not reproducible in SQL Server 2005. Here is a simpler repro (with an
added bonus: a view with an aggregate, which forces the read from the table
instead of the alias)...

CREATE TABLE dbo.Questions
(
QuestionID INT,
Status TINYINT
)
GO

SET NOCOUNT ON
INSERT dbo.Questions SELECT 1, 0
GO

CREATE VIEW dbo.Status
AS
SELECT QuestionID,
BadLine = 1
FROM dbo.Questions
WHERE status = 1
GO

SELECT q.QuestionID,
ISNULL(s.BadLine, 0),
COALESCE(s.BadLine, 0),
s.BadLine
FROM
dbo.Questions Q
LEFT JOIN dbo.Status s
ON Q.QuestionID = s.QuestionID
GO

CREATE VIEW dbo.Status2
AS
SELECT QuestionID,
BadLine = 1,
c = COUNT(*)
FROM dbo.Questions
WHERE status = 1
GROUP BY QuestionID
GO

SELECT q.QuestionID,
ISNULL(s.BadLine, 0),
COALESCE(s.BadLine, 0),
s.BadLine
FROM
dbo.Questions Q
LEFT JOIN dbo.Status2 s
ON Q.QuestionID = s.QuestionID
GO

DROP VIEW dbo.Status, dbo.Status2
DROP TABLE dbo.Questions
GO

--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

"Jeremy Lubich" <Jeremy...@discussions.microsoft.com> wrote in message

news:942D308F-7E7F-4F39...@microsoft.com...

[Razvan Socol]

Here is an more simpler repro:

CREATE TABLE MyTable (
X int PRIMARY KEY
)
GO
INSERT INTO MyTable VALUES (3)

GO
CREATE VIEW MyView as
SELECT X as Y, 1 AS A
FROM MyTable WHERE X=7

GO
SELECT *
FROM MyTable
LEFT JOIN MyView ON X=Y

On SQL Server 2000 SP4 beta (8.00.2026) it returns:

X Y A
----------- ----------- -----------
3 NULL 1

On SQL Server 2005 beta (9.00.1090) it returns:

X Y A
----------- ----------- -----------
3 NULL NULL

I will file a bug report for SQL Server 2000 SP4 beta.

Razvan