Incorrect Results Left Join View

Hi, I have what I believe to be a bug in Sql Server 2000.
The symptoms are as per described in KB 321541, although that particular bug
was fixed in SP3. I have installed SP4 beta and still have wrong result.
My query is quite complex, and when I have tried to create a simple example
I cannot reproduce the behaviour.
To describe the situation, I have a left join between a table and a view,
where that view is based on another view that is based on a third view. In
this deepest view, one of the SELECTed columns contains a (case when sum(xyz
)
is null the 'Some Text' else sum(xyz) end) expression.
This expression appears to be getting executed at the end of the execution
plan, rather than before the join is performed. I would expect all fields
from the view to be null if the join conditions are not met, in a left join.
Hence I am getting fields that are non null being returned from the view. i.
e.
tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
23 'Test' NULL 'Some Text' NULL
Looking in the execution plan, the compute scalar for that calculated field
is being performed last, AFTER the left join. This to me seems very wrong.
As I said, I have been unable to recreate with a simple join.
This problem is not stopping my query from working, it is just seems to be
an annoying bug which COULD potentially have serious consequences if you
weren't aware of.
Any ideas?> My query is quite complex, and when I have tried to create a simple
example
> I cannot reproduce the behaviour.
> To describe the situation
Nobody here is going to be able to reproduce your cenario or confirm this
bug unless you can provide an actual repro (see my signature).
Regardless of how complex the query is, if you can go to a new database,
create the required set of tables with a small amount of data, and run the
query and reproduce the problem, then you can post that here and other
people can try to reproduce the same issue. You don't have to worry that
people have to be able to inspect your query and completely understand it at
first glance... the point is that they can copy and paste the code into
their own database, run it, and see what happens. (Being able to compare it
against what you *expect* to happen is a big help, too.)
It's possible that the bug is in your query, and the only way anyone is
going to be able to find it (complexity only increases the amount of time
required to find the problem, it doesn't preclude it), is if we can actually
see your query. Describing the query doesn't help us at all.
It's also possible that the bug wasn't completely eradicated with SP3.
But without the above, all we can do is speculate and guess.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I'm obviously not looking for someone to reproduce it at this stage, just a
general description that someone may relate to as something they have
encountered.
If I don't get any useful responses I will endeavour to create an example
(not easy on employer's time), although as I stated it is not a bug that is
critical for me at this stage.
You would agree that a left join between a table and a view where no columns
match should result in all NULL values from the view?
"Aaron [SQL Server MVP]" wrote:

> example
> Nobody here is going to be able to reproduce your cenario or confirm this
> bug unless you can provide an actual repro (see my signature).
> Regardless of how complex the query is, if you can go to a new database,
> create the required set of tables with a small amount of data, and run the
> query and reproduce the problem, then you can post that here and other
> people can try to reproduce the same issue. You don't have to worry that
> people have to be able to inspect your query and completely understand it
at
> first glance... the point is that they can copy and paste the code into
> their own database, run it, and see what happens. (Being able to compare
it
> against what you *expect* to happen is a big help, too.)
> It's possible that the bug is in your query, and the only way anyone is
> going to be able to find it (complexity only increases the amount of time
> required to find the problem, it doesn't preclude it), is if we can actual
ly
> see your query. Describing the query doesn't help us at all.
> It's also possible that the bug wasn't completely eradicated with SP3.
> But without the above, all we can do is speculate and guess.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||Lachlan,
If your view does contain the expression
case when sum(xyz) is null the 'Some Text' else sum(xyz) end
then I suspect something is wrong with the view. If that expression
is evaluated when sum(xyz) is null, it will raise an error, since the type
of the expression is a number type, and 'Some Text' can't be converted
into any number type.
Even without posting a repro script, is it possible you could verify
that this is the kind of expression you have in the view or not?
Steve Kass
Drew University
Lachlan wrote:

>Hi, I have what I believe to be a bug in Sql Server 2000.
>The symptoms are as per described in KB 321541, although that particular bu
g
>was fixed in SP3. I have installed SP4 beta and still have wrong result.
>My query is quite complex, and when I have tried to create a simple example
>I cannot reproduce the behaviour.
>To describe the situation, I have a left join between a table and a view,
>where that view is based on another view that is based on a third view. In
>this deepest view, one of the SELECTed columns contains a (case when sum(xy
z)
>is null the 'Some Text' else sum(xyz) end) expression.
>This expression appears to be getting executed at the end of the execution
>plan, rather than before the join is performed. I would expect all fields
>from the view to be null if the join conditions are not met, in a left join
.
>Hence I am getting fields that are non null being returned from the view. i
.e.
>tbl1.fld1 tbl1.fld2 view1.fld1 view1.fld2 view1.fld3
>23 'Test' NULL 'Some Text' NULL
>Looking in the execution plan, the compute scalar for that calculated field
>is being performed last, AFTER the left join. This to me seems very wrong.
>As I said, I have been unable to recreate with a simple join.
>This problem is not stopping my query from working, it is just seems to be
>an annoying bug which COULD potentially have serious consequences if you
>weren't aware of.
>Any ideas?
>|||My apologies I actually have something like:
case when sum(xyz) is null then 'No Score' else str(sum(xyz)) end
I am now trying to break this down to the simplest form that will cause the
error, so I can post an example, I am determined to get an answer to this...
The query I have runs fine and returns results, I just think that some
values should be Null when they are not...
Anyway, if all goes well I will attempt to post some script in the next hour
or two.
"Steve Kass" wrote:

> Lachlan,
> If your view does contain the expression
> case when sum(xyz) is null the 'Some Text' else sum(xyz) end
> then I suspect something is wrong with the view. If that expression
> is evaluated when sum(xyz) is null, it will raise an error, since the ty
pe
> of the expression is a number type, and 'Some Text' can't be converted
> into any number type.
> Even without posting a repro script, is it possible you could verify
> that this is the kind of expression you have in the view or not?
> Steve Kass
> Drew University
> Lachlan wrote:
>
>|||OK here is some script:
Execute all this to build the structures, then run stored procedure SP1.
To me, the two result sets returned should be identical... (preferably the
latter).
- Lachlan
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]
GO
CREATE TABLE [dbo].[Table2] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table1] (
[fldID] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW2
AS
SELECT fldID,
case when fldID is null then 'Null ID' else str(fldID) end as strFldID
FROM dbo.Table2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.VIEW1
AS
SELECT fldID, strFldID
FROM view2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SP1 AS
SELECT *
FROM table1 left join view1 on table1.fldID = view1.fldID
SELECT *
FROM table1 left join view2 on table1.fldID = view2.fldID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
INSERT INTO table1 Values (1)
INSERT INTO table1 Values (2)
INSERT INTO table1 Values (3)
INSERT INTO table1 Values (4)
INSERT INTO table1 Values (5)
INSERT INTO table1 Values (6)
INSERT INTO table2 Values (1)
INSERT INTO table2 Values (2)
INSERT INTO table2 Values (3)|||Sure looks like a bug to me. The repro is very helpful, and I'll
pass it on to Microsoft.
If I get any quick feedback, I'll add to this thread, and if
you want me to get back to you later if there's nothing immediate,
send me an e-mail address.
Thanks,
SK
Lachlan wrote:

>OK here is some script:
>Execute all this to build the structures, then run stored procedure SP1.
>To me, the two result sets returned should be identical... (preferably the
>latter).
>- Lachlan
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
>and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[SP1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW1]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
>drop view [dbo].[VIEW2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[Table2]
>GO
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[table1]
>GO
>CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW2
>AS
>SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
>FROM dbo.Table2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE VIEW dbo.VIEW1
>AS
>SELECT fldID, strFldID
>FROM view2
>
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS OFF
>GO
>CREATE PROCEDURE SP1 AS
>SELECT *
>FROM table1 left join view1 on table1.fldID = view1.fldID
>SELECT *
>FROM table1 left join view2 on table1.fldID = view2.fldID
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>INSERT INTO table1 Values (1)
>INSERT INTO table1 Values (2)
>INSERT INTO table1 Values (3)
>INSERT INTO table1 Values (4)
>INSERT INTO table1 Values (5)
>INSERT INTO table1 Values (6)
>INSERT INTO table2 Values (1)
>INSERT INTO table2 Values (2)
>INSERT INTO table2 Values (3)
>|||This query shows the same behavior, Using Derived queries instead of Views,
and does in fact seem inconsistent with expected behavior:
Select * FROM table1
Left Join
(Select fldID, strFldID From
(Select fldID,
case when fldID is null
then 'Null ID'
else str(fldID)
end strFldID
From Table2) W) Z
On table1.fldID = Z.fldID
"Lachlan" wrote:

> OK here is some script:
> Execute all this to build the structures, then run stored procedure SP1.
> To me, the two result sets returned should be identical... (preferably the
> latter).
> - Lachlan
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[SP1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[VIEW2]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[VIEW2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Table2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[table1]
> GO
> CREATE TABLE [dbo].[Table2] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[table1] (
> [fldID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW2
> AS
> SELECT fldID,
> case when fldID is null then 'Null ID' else str(fldID) end as strFldID
> FROM dbo.Table2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW dbo.VIEW1
> AS
> SELECT fldID, strFldID
> FROM view2
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE SP1 AS
> SELECT *
> FROM table1 left join view1 on table1.fldID = view1.fldID
> SELECT *
> FROM table1 left join view2 on table1.fldID = view2.fldID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> INSERT INTO table1 Values (1)
> INSERT INTO table1 Values (2)
> INSERT INTO table1 Values (3)
> INSERT INTO table1 Values (4)
> INSERT INTO table1 Values (5)
> INSERT INTO table1 Values (6)
> INSERT INTO table2 Values (1)
> INSERT INTO table2 Values (2)
> INSERT INTO table2 Values (3)|||The result is definitely wrong. If you trick the query processor
into thinking 'Null ID' is not a constant, you should
get the right answer. There have been several bugs
of this nature, all of which come from the assumption
that if T.x has a constant value, that value can go anywhere
T.x appears. Oops - except the inner side of an outer
join, which someone forgot to worry about.
I bet replacing 'Null ID' with 'Null ID' +
substring('',1,coalesce(fldID%2+1,1))
will eliminate the bug (and so will lots of simpler things).
SK
CBretana wrote:
>This query shows the same behavior, Using Derived queries instead of Views,
>and does in fact seem inconsistent with expected behavior:
> Select * FROM table1
> Left Join
> (Select fldID, strFldID From
> (Select fldID,
> case when fldID is null
> then 'Null ID'
> else str(fldID)
> end strFldID
> From Table2) W) Z
> On table1.fldID = Z.fldID
>
>"Lachlan" wrote:
>
>