Hi,Svein Erling.

Thanks for replying.

I guess you meant:

create view v_a (
id,
mgrid,
SempTestUniqueName
)
as
select a.id, a.mgrid, b.empid
from t_a a
left outer join t_c c on a.mgrid = c.mgrid
join t_b b on b.empid = c.empid
;

Unfortunately, the results stay the same.

Playing around a bit with different view approaches and types of
joins, it seems the OUTER join is the problem.

If I either create the view with the last join as "join" and
not "left outer join", the view works as expected.

So, the fact is that as long as I want to use (left) outer joins, I
have to inner join the result with a table whos fields I may not even
need, in order for the view to return the correct values? Sounds
buggy to me.


The fun part is that with the broken view, I get results like:

select mgrid, empid from v_a where id = 1;
MGRID EMPID
============ ============

871172 10000

select mgrid, empid from v_a where id = 1 and empid is null;
MGRID EMPID
============ ============

871172 <null>


"What's your name, girl?"
"Whatever you want it to be!"

<g>

Regards,
Semp

>
> Hi Semp!
>
> One thing that far too often creates confusion, is if two fields

share

> the same name. That you cannot reproduce your findings in a test

case

> - whereas you consistently observes it in your real program -
> indicates that the problem is something in your code that does

things

> differently from what you assume.
>
> Try changing to
>
> create view v_a as
> select a.id, a.mgrid, b.empid as SempTestUniqueName
> from t_a a
> left outer join t_c c on a.mgrid = c.mgrid
> join t_b b on b.empid = c.empid
>
> I don't think you need the to make the last join an outer join, it

is

> an outer join already since the link is to an outer joined table
> (though I may be wrong, I've only ever assumed this without

checking).

>
> Then do
>
> select count(*) from v_a where v_a.SempTestUniqueName is null;
>
> and see if you get the same result.
>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "semprolbat" wrote:
> >
> > Hi.
> >
> > I have a view that is made up of three different tables joined
> > together.
> >
> > Stripped down to the essential fields, it looks a bit like:
> > create view v_a as
> > select a.id, a.mgrid, b.empid
> > from t_a a
> > left outer join t_c c on a.mgrid = c.mgrid
> > left outer join t_b b on b.empid = c.empid
> > ;
> >
> > In English: the view shows its base table a's values, and gets a
> > manager's employment ID from table b, based on mappings in table

c.

> >
> > What's funny is that with my production tables, I get results

like

> > this:
> >
> > select count(*) from v_a;
> > COUNT
> > ============
> >
> > 21004
> >
> > select count(*) from v_a where empid is null;
> > COUNT
> > ============
> >
> > 21004
> >
> > select count(*) from v_a where empid is not null;
> > COUNT
> > ============
> >
> > 21004
> >
> >
> > The plan is:
> > PLAN JOIN (JOIN (V_A T INDEX (RDB$FOREIGN40),V_A O T NATURAL),
> > V_A A T NATURAL)
> >
> > Am I doing something wrong, or is this behaviour documented
> > somewhere? Is the index messed up?
> >
> > I tried reproducing this with a test case, but in the test case
> > everything works as expected.
> >
> > Semp