Well, either our expectations are wrong or your Firebird versions (I
certainly agree with you on the first discrepancy between your
expectations and Firebird, I didn't check the rest of them). Also, I
think your problem description seems good. Now, check if the latest
Firebird 1.5 release has the problem (if you haven't already done so)
and if so, go to
http://sourceforge.net/tracker/?group_id=9028&atid=109028, check if
the bug is already reported (I thought I saw something similar, but
not with that clear a description and without a solution) and add your
report.

A perfect Firebird is - of course - the aim, but seeing a bug isolated
is also quite nice! Well done, Semp!

Set

--- In firebird-support@yahoogroups.com, "semprolbat" wrote:
>
> 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;