Hi,

> Is this a bug??

Confirmed, it's indeed a bug :-(

The IS NULL in then where clause seems to be the problem here.

> 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

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81