Skip site navigation (1) Skip section navigation (2)

Is there a bug in PostgreSQL ?

From: "Pascal Tufenkji" <ptufenkji(at)usj(dot)edu(dot)lb>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Is there a bug in PostgreSQL ?
Date: 2008-12-10 14:33:07
Message-ID: 008701c95ad4$38870870$150fa8c0@interne.usj.edu.lb (view raw or flat)
Thread:
Lists: pgsql-sql
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 

Responses

pgsql-sql by date

Next:From: Filip RembiaƂkowskiDate: 2008-12-10 14:45:36
Subject: Re: Collapsing (select) row values into single text field.
Previous:From: Bruce MomjianDate: 2008-12-10 13:33:29
Subject: Re: inconsistent automatic casting between psql and function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group