[Firebird-devel] [ firebird-Bugs-919713 ] Left joining views with null fails

[SourceForge.net <no...@so...>]

Bugs item #919713, was opened at 2004-03-19 21:52
Message generated for change (Settings changed) made by dimitr
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=919713&group_id=9028

Category: Core Engine
>Group: Fixed v1.5.x
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: John Sparrow (johnsparrowuk)
>Assigned to: Arno Brinkman (arnobrinkman)
Summary: Left joining views with null fails

Initial Comment:
The following fails:

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.



----------------------------------------------------------------------

Comment By: Sérgio Uetanabaro (uetanabaro)
Date: 2004-04-07 23:17

Message:
Logged In: YES 
user_id=261366

A have same problem but i have a table with this structure:
-------------------------------------------
CREATE TABLE TEST (
    ID      INTEGER NOT NULL,
    NAME    VARCHAR(50),
    PARENT  INTEGER
)
-------------------------------------------
PARENT is a auto-reference. When i execute
-------------------------------------------
select *
from TEST
  left outer join TEST T2
    on TEST.PARENT=T2.ID
-------------------------------------------
the resultset for alias T2 is not right ... BUT...
-------------------------------------------
select *
from TEST T1
  left outer join TEST T2
    on T1.PARENT=T2.ID
-------------------------------------------
its works...

My confs:
Dialect 3
Servier Version 1.5.0.4306

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=919713&group_id=9028