As a workaround, doing:

select person from currentpeople where person not in (select person
from finishedpeople)

seems to work ok - so I least I've got my system up and running again
(obviously not real table names!)

John

--- In firebird-support@yahoogroups.com, "johnsparrowuk"
<jsparrow@e...> wrote:

> Is this a bug??
>
> ===============
>
> create table mytable (person varchar(20) not null, status integer

not

> null, primary key (person,status));
>
> create view currentpeople as select distinct person from mytable
> where status = 1;
> create view finishedpeople as select distinct person from mytable
> where status = 2;
>
>
> insert into mytable values ('john',1);
> insert into mytable values ('john',2);
> insert into mytable values ('fred',1);
>
>
>
> /* This works fine: fred-null, john-john */
> select * from currentpeople c left join finishedpeople f on

c.person

> = f.person
>
>
> /* This is as expected too: john-john */
> select * from currentpeople c left join finishedpeople f on

c.person

> = f.person
> where f.person = 'john'
>
> /* WHATS HAPPENING HERE????? fred-null, JOHN-NULL where does the

john-

> null come from???*/
> select * from currentpeople c left join finishedpeople f on

c.person

> = f.person
> where f.person is null
>
> ===============
>
> Firebird 1.5 SS, Windows 2000 and XP, dialect 3 db.
>
> Took me 3 hours of tracing through stored procedures to fine I

wasn't

> being stupid!
>
> Thanks,
>
> John