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