semprolbat wrote:

> I am, however, very interested in seeing a working version of your
> test case showing the problem you discovered with foreign keys.
>

Here's the log - first creating a two tables, then a view of one of the
tables, then two left outer join views, one between two tables and the
other using a the simple view as the left term. All works as expected.

Then add a third table and create a foreign key relationship between the
table in the outer

SQL> create database 'foo.fdb';
SQL> create table a (a1 integer, a2 integer);
SQL> create table b (b1 integer, b2 integer);
SQL> create view v_ab (va1, vb1) as
CON> select a.a1, b.b1 from a left join b on a.a2 = b.b2;
SQL> create view v_b (vb1, vb2) as
CON> select b.b1, b.b2 from b;
SQL> create view v_avb (vva1, vvb1) as
CON> select a.a1, v_b.vb1 from a left join v_b on a.a2 = v_b.vb2;
SQL>
SQL> insert into a (a1, a2) values (1, 5);
SQL> insert into b (b1, b2) values (2, 5);
SQL>
SQL> select * from v_ab where vb1 is null;
SQL> select * from v_ab;

VA1 VB1
============ ============

1 2

SQL> select * from v_avb;

VVA1 VVB1
============ ============

1 2

SQL> select * from v_avb where vvb1 is null;
SQL>

Now create and populate a third table

SQL> select * from v_avb where vvb1 is null;
SQL> create table c (c1 integer not null primary key);
SQL> insert into c (c1) values (5);

reconnect, and try again, getting the wrong answer when using the view
of b in the outer join view, but the right answer when using b in the
view directly

SQL> commit;
SQL> connect foo.fdb;
Database: foo.fdb
SQL> alter table b add constraint fk_bc foreign key (b2) references c (c1);
SQL> select * from v_avb where vvb1 is null;

VVA1 VVB1
============ ============

1 <null>

SQL> select * from v_ab where vb1 is null;
SQL>