Svein Erling,

I do not know what is wrong; the database or my expectations. ;-)
I did not mention initially that the joins are based on views, which
seems to be the source of the problem.

Here is a test case:

Semp


/* test db */

create database "__test.fdb" user "sysdba" password "masterkey";

create table t_a (
id integer not null unique,
mgrid integer not null
);

create table t_b (
empid integer not null unique,
empname varchar(20) not null
);

create table t_c (
empid integer not null references t_b (empid),
mgrid integer not null
);

create view v_b as
select empid, empname
from t_b;

create view v_c as
select empid, mgrid
from t_c;

create view v_a as
select id, a.mgrid, c.empid
from t_a a
left outer join v_c c on a.mgrid = c.mgrid
;

create view v_a2 as
select id, a.mgrid, c.empid
from t_a a
left outer join t_c c on a.mgrid = c.mgrid
;

insert into t_a values(2, 5);
insert into t_b values(1, 'employee nr. 1');
insert into t_c values(1, 5);

commit;

/* test queries, view based view */


select count(*) from v_a;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

1
*/

select count(*) from v_a where empid is null;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

0
*/


select count(*) from v_a where empid is not null;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

1
*/

select * from v_a;
/*
Result
ID MGRID EMPID
============ ============ ============

2 5 1

Expected result:
ID MGRID EMPID
============ ============ ============

2 5 1
*/

select * from v_a where empid is null;
/*
Result
ID MGRID EMPID
============ ============ ============

2 5 <null>
Expected result:
<zero records>

*/


;

/* test queries, table based view */


select count(*) from v_a2;
select count(*) from v_a2 where empid is null;
select count(*) from v_a2 where empid is not null;
select * from v_a2;
select * from v_a2 where empid is null;