"ben brugman" <b**@niethier.nl> wrote in message
news:%23aDTM8W%23JHA.1340@TK2MSFTNGP05.phx.gbl...
> -- Using SQLserver 2000,
> --
> -- The following code gives for me an unexpected result.
> --    Is this a bug ?
> --    just strange behaviour ?
> --    my misunderstanding ?
> --
> --
> CREATE TABLE dbo.show
> (
>   A varchar(200) NULL,
>    B varchar(200) NULL,
>    C varchar(200) NULL
> )
> ON [PRIMARY]
> GO
> CREATE VIEW [dbo].[View_show]
> AS
> SELECT
>   A, ISNULL(B, '2B V NOT 2B') AS B, C
> FROM
>   dbo.show
> GO
>
> -- There are no rows in Show, so there are no rows in show_view,
> -- what is the result of the following statement ?
> SELECT * FROM view_show
>
> --
> -- There are no rows in Show, so there are no rows in show_view,
> -- so there are no rows in the sub query ssss.
> -- what is the result of the following statement ?
> -- Before continuing, try to predit the following statement.
> --
> SELECT
>   ssss.a,ssss.b,ssss.c
> FROM
>   (
>      SELECT a,b,c FROM view_show
>   ) as ssss
> RIGHT OUTER JOIN
>   (
>      SELECT 'nothing' as NULL_column
>   )  as Single_row
> ON
>   1 = 1
>
>
> --
> -- Remove the created table and view.
> --
> drop table show
> drop view view_show
>
> -- The code gives the unexpected result in SQLserver 2000
> -- The code gives the expected result in SQLserver 2005
> --
> -- Is this a bug in SQLserver 2000 ?
> --
> --
> --
> -- Thanks for your time and attention,
> -- Greetings,
> -- Ben Brugman
>
>

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:O9AkfbX%23JHA.2120@TK2MSFTNGP02.phx.gbl...
> ben
> They have different execution plans. We see Computer Scalar iterator
> BEFORE JOIN  in SQL Server 2005  does below
>
> [[master].[dbo].[show].A] = Scalar Operator([master].[dbo].[show].[A]),
> [Expr1006] = Scalar Operator(isnull([master].[dbo].[show].[B],'2B V NOT
> 2B')), [[master].[dbo].[show].C] = Scalar
> Operator([master].[dbo].[show].[C])
>
>
> "ben brugman" <b**@niethier.nl> wrote in message
> news:%23aDTM8W%23JHA.1340@TK2MSFTNGP05.phx.gbl...
>> -- Using SQLserver 2000,
>> --
>> -- The following code gives for me an unexpected result.
>> --    Is this a bug ?
>> --    just strange behaviour ?
>> --    my misunderstanding ?
>> --
>> --
>> CREATE TABLE dbo.show
>> (
>>   A varchar(200) NULL,
>>    B varchar(200) NULL,
>>    C varchar(200) NULL
>> )
>> ON [PRIMARY]
>> GO
>> CREATE VIEW [dbo].[View_show]
>> AS
>> SELECT
>>   A, ISNULL(B, '2B V NOT 2B') AS B, C
>> FROM
>>   dbo.show
>> GO
>>
>> -- There are no rows in Show, so there are no rows in show_view,
>> -- what is the result of the following statement ?
>> SELECT * FROM view_show
>>
>> --
>> -- There are no rows in Show, so there are no rows in show_view,
>> -- so there are no rows in the sub query ssss.
>> -- what is the result of the following statement ?
>> -- Before continuing, try to predit the following statement.
>> --
>> SELECT
>>   ssss.a,ssss.b,ssss.c
>> FROM
>>   (
>>      SELECT a,b,c FROM view_show
>>   ) as ssss
>> RIGHT OUTER JOIN
>>   (
>>      SELECT 'nothing' as NULL_column
>>   )  as Single_row
>> ON
>>   1 = 1
>>
>>
>> --
>> -- Remove the created table and view.
>> --
>> drop table show
>> drop view view_show
>>
>> -- The code gives the unexpected result in SQLserver 2000
>> -- The code gives the expected result in SQLserver 2005
>> --
>> -- Is this a bug in SQLserver 2000 ?
>> --
>> --
>> --
>> -- Thanks for your time and attention,
>> -- Greetings,
>> -- Ben Brugman
>>
>>
>
>

"ben brugman" <b**@niethier.nl> wrote in message
news:e7OuY7Z%23JHA.4360@TK2MSFTNGP04.phx.gbl...
> Hello Uri,
>
> Thanks for your attention.
> I was working under SQL-2000 when I 'encoutered' this problem, although in
> a more complex situation.
> So I stripped down the problem ask here about it. When I had stripped it
> down and it still produced the effect, I did test it on a SQL-2005
> installation and saw that it did not happen there.
>
> For me the effect (of the more complex situation) was that I did not
> understand the results. So the problem could be me, for example not
> understanding the statement completely or that the statement is executed
> in the wrong order.
> If the simplified example is even simplified a bit more to :
> SELECT
>  a,b,c
> FROM
> --  (
> --     SELECT * FROM
>     view_show
> --   )
> as ssss
> RIGHT OUTER JOIN
>  (
>     SELECT 'nothing' as NULL_column
>  )  as Single_row
> ON
>  1 = 1
>
> The effect disappears, while I would think that it's still in essence the
> same statement. Adding the brackets and the select * from should not alter
> the statement, but it does. So to me this looks like a bug.
>
> So can anybody confirm this is a bug or just a different way of
> interpreting SQL ?
>
> Thanks for your time and attention,
> Ben Brugman
> (When something happens, which I do not expect or can not explain, I like
> to know, if the fault is mine or that something else is going on.).
>
>
>
> "Uri Dimant" <u***@iscar.co.il> wrote in message
> news:O9AkfbX%23JHA.2120@TK2MSFTNGP02.phx.gbl...
>> ben
>> They have different execution plans. We see Computer Scalar iterator
>> BEFORE JOIN  in SQL Server 2005  does below
>>
>> [[master].[dbo].[show].A] = Scalar Operator([master].[dbo].[show].[A]),
>> [Expr1006] = Scalar Operator(isnull([master].[dbo].[show].[B],'2B V NOT
>> 2B')), [[master].[dbo].[show].C] = Scalar
>> Operator([master].[dbo].[show].[C])
>>
>>
>> "ben brugman" <b**@niethier.nl> wrote in message
>> news:%23aDTM8W%23JHA.1340@TK2MSFTNGP05.phx.gbl...
>>> -- Using SQLserver 2000,
>>> --
>>> -- The following code gives for me an unexpected result.
>>> --    Is this a bug ?
>>> --    just strange behaviour ?
>>> --    my misunderstanding ?
>>> --
>>> --
>>> CREATE TABLE dbo.show
>>> (
>>>   A varchar(200) NULL,
>>>    B varchar(200) NULL,
>>>    C varchar(200) NULL
>>> )
>>> ON [PRIMARY]
>>> GO
>>> CREATE VIEW [dbo].[View_show]
>>> AS
>>> SELECT
>>>   A, ISNULL(B, '2B V NOT 2B') AS B, C
>>> FROM
>>>   dbo.show
>>> GO
>>>
>>> -- There are no rows in Show, so there are no rows in show_view,
>>> -- what is the result of the following statement ?
>>> SELECT * FROM view_show
>>>
>>> --
>>> -- There are no rows in Show, so there are no rows in show_view,
>>> -- so there are no rows in the sub query ssss.
>>> -- what is the result of the following statement ?
>>> -- Before continuing, try to predit the following statement.
>>> --
>>> SELECT
>>>   ssss.a,ssss.b,ssss.c
>>> FROM
>>>   (
>>>      SELECT a,b,c FROM view_show
>>>   ) as ssss
>>> RIGHT OUTER JOIN
>>>   (
>>>      SELECT 'nothing' as NULL_column
>>>   )  as Single_row
>>> ON
>>>   1 = 1
>>>
>>>
>>> --
>>> -- Remove the created table and view.
>>> --
>>> drop table show
>>> drop view view_show
>>>
>>> -- The code gives the unexpected result in SQLserver 2000
>>> -- The code gives the expected result in SQLserver 2005
>>> --
>>> -- Is this a bug in SQLserver 2000 ?
>>> --
>>> --
>>> --
>>> -- Thanks for your time and attention,
>>> -- Greetings,
>>> -- Ben Brugman
>>>
>>>
>>
>>
>
>