|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > No rows returned in my query -- why?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
No rows returned in my query -- why?
|
|
| John Peterson 2002-11-22, 10:23 am |
| (SQL Server 2000, SP2)
Hello, all!
I'm sure that there's a simple explanation for why I'm not getting any rows
returned in my query, but it must be a Friday, because I can't seem to get
my brain around it and I was hoping you could help. :-)
Consider:
use tempdb
go
create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
go
insert into Main values (1, 'Test1')
insert into Main values (2, 'Test2')
go
create table A (AId int not NULL, Message ntext not NULL)
go
create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
go
create view BwithA
as
select b.BId,
b.MainId,
b.AId,
a.Message
from B as b
inner join A as a on a.AId = b.AId
go
select m.*,
b.Message
from Main as m
left join BwithA as b on b.MainId = m.MainId
go
This is all well and good, and seems to work as expected.
However, if I add a WHERE clause to my SELECT statement that starts to
reference BwithA.Message, I don't get any rows returned. For example, if I
change the query to:
select m.*,
b.Message
from Main as m
left join BwithA as b on b.MainId = m.MainId
where b.Message is NULL
Then no rows are returned. I'm not sure I understand *why*. If someone can
explain it to me, I'd be very grateful! :-)
John Peterson
| |
| Tibor Karaszi 2002-11-28, 6:23 am |
| Hi John!
I see your point here. To me, this is a bug in the optimizer (I think, I might be
misinterpreting the base table-view relations here). A view, according to the ANSI standard,
should behave like if you first materialize the data from the view and then run your query
against that materialized query. Here, it seems like SQL Server is pushing the WHERE clause into
the view definition (it actually flattens out the view), hence the need for restriction in the
FROM clause instead of the WHERE clause.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"John Peterson" <johnp@azstarnet.com> wrote in message news:OEaZ67jkCHA.2012@tkmsftngp11...
> (SQL Server 2000, SP2)
>
> Hello, all!
>
> I'm sure that there's a simple explanation for why I'm not getting any rows
> returned in my query, but it must be a Friday, because I can't seem to get
> my brain around it and I was hoping you could help. :-)
>
> Consider:
>
>
> use tempdb
> go
>
> create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
> go
>
> insert into Main values (1, 'Test1')
> insert into Main values (2, 'Test2')
> go
>
> create table A (AId int not NULL, Message ntext not NULL)
> go
>
> create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
> go
>
> create view BwithA
> as
> select b.BId,
> b.MainId,
> b.AId,
> a.Message
> from B as b
> inner join A as a on a.AId = b.AId
> go
>
> select m.*,
> b.Message
> from Main as m
> left join BwithA as b on b.MainId = m.MainId
> go
>
>
> This is all well and good, and seems to work as expected.
>
> However, if I add a WHERE clause to my SELECT statement that starts to
> reference BwithA.Message, I don't get any rows returned. For example, if I
> change the query to:
>
>
> select m.*,
> b.Message
> from Main as m
> left join BwithA as b on b.MainId = m.MainId
> where b.Message is NULL
>
>
> Then no rows are returned. I'm not sure I understand *why*. If someone can
> explain it to me, I'd be very grateful! :-)
>
> John Peterson
>
>
| |
| John Peterson 2002-11-29, 8:23 am |
| Hello, Tibor!
What seems weird to me, is that if I do a CONVERT on the field in question
(to the *same* datatype), then it behaves as expected (I'll cut-n-paste from
a response to harvinder in this thread):
select m.*,
b.Message
from Main as m
left join BwithA as b on b.MainId = m.MainId
where convert(ntext, b.Message) is NULL
Thanks for the information regarding the ANSI "definition" of a VIEW.
That's good to keep in mind. I *think* that the original SQL that I
presented is adhering to that definition, however, no?
Thanks again for your help! :-)
John Peterson
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:e730QitlCHA.1516@tkmsftngp04...
> Hi John!
>
> I see your point here. To me, this is a bug in the optimizer (I think, I
might be
> misinterpreting the base table-view relations here). A view, according to
the ANSI standard,
> should behave like if you first materialize the data from the view and
then run your query
> against that materialized query. Here, it seems like SQL Server is pushing
the WHERE clause into
> the view definition (it actually flattens out the view), hence the need
for restriction in the
> FROM clause instead of the WHERE clause.
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "John Peterson" <johnp@azstarnet.com> wrote in message
news:OEaZ67jkCHA.2012@tkmsftngp11...
> > (SQL Server 2000, SP2)
> >
> > Hello, all!
> >
> > I'm sure that there's a simple explanation for why I'm not getting any
rows
> > returned in my query, but it must be a Friday, because I can't seem to
get
> > my brain around it and I was hoping you could help. :-)
> >
> > Consider:
> >
> >
> > use tempdb
> > go
> >
> > create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
> > go
> >
> > insert into Main values (1, 'Test1')
> > insert into Main values (2, 'Test2')
> > go
> >
> > create table A (AId int not NULL, Message ntext not NULL)
> > go
> >
> > create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
> > go
> >
> > create view BwithA
> > as
> > select b.BId,
> > b.MainId,
> > b.AId,
> > a.Message
> > from B as b
> > inner join A as a on a.AId = b.AId
> > go
> >
> > select m.*,
> > b.Message
> > from Main as m
> > left join BwithA as b on b.MainId = m.MainId
> > go
> >
> >
> > This is all well and good, and seems to work as expected.
> >
> > However, if I add a WHERE clause to my SELECT statement that starts to
> > reference BwithA.Message, I don't get any rows returned. For example,
if I
> > change the query to:
> >
> >
> > select m.*,
> > b.Message
> > from Main as m
> > left join BwithA as b on b.MainId = m.MainId
> > where b.Message is NULL
> >
> >
> > Then no rows are returned. I'm not sure I understand *why*. If someone
can
> > explain it to me, I'd be very grateful! :-)
> >
> > John Peterson
> >
> >
>
>
|
|
|
|
|