Left outer join bug on computed scalar columns?

[IntegrationArchitect]

We noticed this bug in more complex queries, but in essence it appears that
the query plan for SQL Server picks an incorrect path for computing a scalar
column. The scalar column value is computed too late in the plan. It is
computed AFTER it is filtered on, which is no good. I have an example. Run
T4 and T5 subqueries independently to see what is being joined and what the
results of the COMPUTE_SCALAR column should be and then see what SQL Server
puts there...

Anybody else ever seen this bug? Is there a patch?

SELECT T5.ID, T5.EXPECTED_RESULT, T4.COMPUTED_SCALAR
FROM
--
-- SQL Server is computing the scalar in the wrong place
--
(SELECT 1 AS ID, 'Computed scalar should be NULL' AS EXPECTED_RESULT
UNION ALL
SELECT 2 AS ID, 'Computed scalar should be "VALUE"' AS EXPECTED_RESULT) T5
--
-- There is no return row in this left outer join for ID = 1 from T4...
--
LEFT OUTER JOIN
--
-- This next statement does not have a value for id = 1 !!!!!!!!!!!!!!!!!!
--
(SELECT * FROM
(SELECT T1.ID, COALESCE(CASE WHEN T1.SCALAR = 'NO VALUE' THEN NULL ELSE
T1.SCALAR END, T2.SCALAR, 'NO VALUE') AS COMPUTED_SCALAR
FROM
(SELECT 1 AS ID, 'NO VALUE' AS SCALAR
UNION ALL
SELECT 2 AS ID, 'NO VALUE' AS SCALAR) T1
LEFT OUTER JOIN
(SELECT 2 AS ID, 'VALUE' AS SCALAR) T2
ON T1.ID = T2.ID) T3
WHERE COMPUTED_SCALAR <> 'NO VALUE' ) T4
--
-- So, joining T4 with T5 should return NULL for computed scalar, but it
does not! BUG!!!!!!!!!
--
-- SQL Server is computing the scalar in the WRONG location if you examine
the explain plan.
--
ON T5.ID = T4.ID

[Steve Kass]

This is a known bug, though I am not aware of a knowledge base article
about it. It occurs when a view or derived table on the "non-outer"
side of an OUTER JOIN contains a constant column, and that column
appears in the WHERE condition. The query processor wrongly assumes
that the column value after the outer join is the constant it's defined
to be in the derived table or view. I've always been able to use one of
the following 3 workarounds:

1. If the view or derived table X has a non-null, non-constant column
[other_column], replace the failing condition with (X.other_column is null).
2. Replace the constant column const_col in the where clause by an
equivalent value that is an expression, not a simple column reference,
by adding 0 or concatenating a space character.
3. If there is no non-constant column in the derived table or view, as
in your case, you don't need an outer join. Instead of an outer join
with (select 1 as a, 'this' as b), write the query using NOT EXISTS or
EXISTS or IN.

select blah
from T left outer join (
select 1 as a, 'this' as b, real_col from U
) V
on real_col = T.blah and T.other = a
where b <> 'this'

can probably be rewritten something like this

select blah
from T
where not exists (
select * from U
where real_col = T.blah
and T.other = 1
)

Steve Kass
Drew University