SQL Server 2000 NULL Bug When Left Outer Joining A Nested View

I found a very interesting behavior today with SQL Server 2000 SP4 that I'm most certain is a bug. I Googled for a while and found a couple old threads bringing up similar issues but they were without confirmation nor resolution. My actual use case was fairly complicated and confusing. I thought long and hard and came up with a simple example of the problem that still made a shred of sense.To set the stage, this anomaly has do with when you LEFT OUTER JOIN result set B to result set A. For each record in result set A for which no corresponding record in result set B is found, those columns are returned as having NULL values.

[code]DECLARE @A TABLE (a INT)

DECLARE @B TABLE (b INT)

INSERT @A VALUES (1)
INSERT @A VALUES (2)
INSERT @A VALUES (3)

SELECT a, b
FROM @A
LEFT OUTER JOIN @B ON a = b
[/code]

The result of such code is as follows: The scenario that produced unexpected results was similar, but my "B" table was a view of a view that contained a column computed with a CASE statement. Here is the example: Consider the following two tables that store information about people and their pets (if they have any)

[code]CREATE TABLE person
(personID int PRIMARY KEY,
personName varchar(20))

CREATE TABLE personPet
(personPetID int PRIMARY KEY,
personID int,
petType varchar(20),
petName varchar(20),
alive varchar(20))
[/code]

Let's put some data in our tables...

[code]INSERT person VALUES (1, 'Tom')
INSERT person VALUES (2, 'Bob')
INSERT person VALUES (3, 'George')

INSERT personPet VALUES (1, 1, 'Cat', 'Fluffy','Yes')
INSERT personPet VALUES (2, 2, 'Dog', 'Spot','Yes')
INSERT personPet VALUES (3, 3, 'Cat', 'Foo Foo','No')
[/code]

Now, let's create a view of our personPet table that includes a calculated column that determines whether or not the given pet is cool or not.

[code]CREATE VIEW vPersonPet AS
	
	SELECT personPetID,
		personID,
		petName,
		petType,
		alive,
		CASE WHEN petType = 'Dog' THEN 'Cool'
		ELSE 'Un-Cool' END AS petCoolness
	FROM personPet
[/code]

We can list out all people with their pets by joining the two tables like so. Notice the petCoolness column which is calculated by the view.

[code]SELECT p.personName,
	pp.petType,
	pp.petName,
	pp.petCoolness
FROM person p
INNER JOIN vPersonPet pp ON p.personID = pp.personID[/code]

Which give us: No problems so far. Now, let's create a view that selects from the first view, but only includes pets which are still alive. (I promise, we're getting close)

[code]CREATE VIEW vPersonPetLiving AS
	
	SELECT personPetID,
		personID,
		petName,
		petType,
		alive,
		petCoolness
	FROM vPersonPet
	WHERE alive = 'Yes'
[/code]

Now here's where the magic (?) happens. Let's write a select statement that returns all people and their living pets. I want each person to be returned by the query regardless of whether they have a matching record in the personPet table that meets our criteria, so I will LEFT OUTER JOIN from the person table to the vPersonPetLiving view and will expect NULLs to be returned for the pet information that does not exist.

[code]SELECT p.personName,
	pp.petType,
	pp.petName,
	pp.petCoolness
FROM person p
LEFT OUTER JOIN vPersonPetLiving pp ON p.personID = pp.personID
[/code]

Wait, look at that result set! You can see that even though George has no living pets and the petType and petName column returned NULL (as expected) the petCoolness column (which is calculated by a CASE statement) returned a value. This is unexpected. In my case I was wrapping an isnull() around the mysteriously non-null value to give it a default value, but the isnull() was not affecting anything. If we examine the execution plan, we can see that the case statement is executed AFTER the LEFT OUTER JOIN is performed and therefore applied to all rows. I only get this behavior for a view nested inside of another view. If I combine the logic of both views into a single one and LEFT OUTER JOIN on that, the result is as expected (George's petCoolness is NULL for his non-existent pet) and the execution plan shows the CASE statement being executed prior to the join. From what I have read, newer versions of SQL Server do not suffer from this "feature". My server with 2005 is down right now so I can't try it. I am including the SQL script as a download so maybe someone can give it a try to confirm. Regardless, tell what you think of this bug (?) and if you have ever heard of it or a resolution.