sql server programming: I'm upgraded my SQL Server 7.0 to SQL 2000, but I was force to back to SQL 7,
because one (or more) T-SQL sentence generated wrong data.

It didn't generate error messages, so, I worked a week with wrong data.

The fail-response T-SQL sentence, is a complex, run-time-made sentence that
seek for no-existing Records.

Testing the sentence, I found a error in the Excecution Plan: a filter in
wrong place delete null registers, but needed ones.

This is a sentence (simplified):

select m.distribuid, m.pedido, D.producto
from(
-- Record to find
select distribuid, pedido, 'Prod' producto from d_PEDIDO
) m left join (
-- Existing Record
select d.distribuid, d.pedido, a.producto
from (select 'Prod' producto) a
LEFT join (
select distribuid, pedido, producto from d_pedido
) d on a.producto=d.producto
) d on d.distribuid=m.distribuid and m.pedido=d.pedido and
d.producto=m.producto
Where D.producto is Null

I already install SQL 2000 Service Pack3, I don't know how to correct it.

I don't found any reference about this problem, and I can't trust SQL 2000
work with my others applications.

There is a known bug in SQL Server 2000 that occurs when a view has a
constant column, and it participates in an OUTER JOIN with a filter on that
constant column, as in this simple example:

create view V
as select 1 as I
go

select V2.I
from V V1
left join V V2
on V1.I <> V2.I
where V2.I is not null
go

drop view V

I think your example is related to this bug, and I think you can avoid it.
It's usually possible to rewrite the query to check a different column for
NULL, or not to use the computed column.

If in your real query, if table d has a non-constant column [otherColumn],
try changing the WHERE clause to WHERE d.[otherColumn] IS NULL instead of
using the constant column. If table d has only one column, and it is
constant, then I don't think you need that table. You can change your query
to something like this:

select m.distribuid, m.pedido, D.producto
from (
-- Record to find
select distribuid, pedido, 'Prod' producto
from d_PEDIDO
) m left join (
-- Existing Record
select
case when producto <> 'Prod' then NULL else distribuid end as
distribuid,
case when producto <> 'Prod' then NULL else pedido end as pedido,
NULLIF(producto,'Prod') as producto
from d_pedido
) d
on d.distribuid=m.distribuid
and m.pedido=d.pedido
and d.producto=m.producto
Where D.producto is Null

I know this is frustrating, but I don't think there are many bugs left in
SQL Server, and you were very unlucky to find one right away.

Steve Kass
Drew University

[quoted text, click to view]