Is there a bug in PostgreSQL ?

Hello,

I'm writing a query with a left join to a view, and the server is giving me
a wrong result.

SELECT emp_id,institution from sip_carriere where emp_id = 342 and
institution = 1;

emp_id | institution

--------+-------------

342 | 1

(1 row)

SELECT * from sip_demissionaire where emp_id = 342;

emp_id | demission_date

--------+----------------

(0 rows)

IF I COMBINE THE TWO QUERIES WITH A LEFT JOIN QUERY AND PUT ONLY ONE
CONDITION, IT WOKS JUST FINE :

SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 ;

emp_id | institution | emp_id | demission_date

--------+-------------+--------+----------------

342 | 1 | |

342 | 63 | |

342 | 85 | |

(3 rows)

SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.institution = 1;

emp_id | institution | emp_id | demission_date

--------+-------------+--------+----------------

342 | 1 | |

... | ... | ...| ...

BUT IF I PUT BOTH CONDITIONS

SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 and
c.institution = 1;

emp_id | institution | emp_id | demission_date

--------+-------------+--------+----------------

(0 rows)

What's the problem ?

I'm sure that the problem is with the view "sip_demissionaire" cause when I
copied its content to a temp table, the query returned a result.

SELECT * into temp foo from sip_demissionaire ;

SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join foo
d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution = 1;

emp_id | institution | emp_id | demission_date

--------+-------------+--------+----------------

342 | 1 | |

(1 row)

Here's the description of the view "sip_demissionaire" in case you need it

CREATE VIEW sip_demissionaire AS

(

SELECT t1.* from

(

SELECT emp_id,max(demission_date) as demission_date

from sip_carriere_dates

where demission_date is not null

group by emp_id

) as t1

left join

(

select emp_id

from sip_carriere_dates

where demission_date is null

) as t2 on t1.emp_id = t2.emp_id

where t2.emp_id is null

);

I know it's a long mail, but I'd appreciate any help

Thx in advance

Pascal