semprolbat wrote:

> I did not mention initially that the joins are based on views, which
> seems to be the source of the problem.

You're right. There does seem to be a problem, but I don't quite have
it nailed. For the casual reader, I've put my definitions and results
and your definitions below my signature.

It appears that the relevant source definitions are table t_a, table
t_c, and view v_c. You see the problems in a view composed of table t_a
and view v_c. There's no reference to t_b, or v_b except that t_c has a
foreign key reference to t_b.

My first thought was that the problem was with the two levels of c which
both use the same field names and alias, and in fact, my test case with
different field names and aliases does not show the error. So I went
back to your script and just took out the reference to table t_b.

That fixed the problem. Without the foreign key definition, I get the
expected results, even with tables and views that share names. Could
you verify that? When I add the foreign key constraint, I get the
unexpected results.



Thanks



Ann


My definitions:



create table t_a (
id integer not null unique,
mgrid integer not null );
create table t_c (
empid integer not null,
mgrid integer not null );
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_c values(1, 5);
commit;

select count(*) from v_a;

COUNT
============

1

select count(*) from v_a where empid is null;

COUNT
============

0

////
////
disconnect and reconnect here
///
///

create table c (c1 integer not null primary key);
alter table b add constraint fk_bc foreign key (b2) references c (c1);
commit;

select * from v_avb;

VA1 VVB1
======= ============

2 1

select * from v_avb where vvb1 is null;

VA1 VVB1
======= ============

2 <null>



Original test case.

> 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;
>
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>