Обсуждение: 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

Hello again,

Actually I'm using PostgreSQL 8.2.4

By the way, I tried reindexing the tables but the problem remains

REINDEX TABLE sip_carriere_dates;

REINDEX TABLE sip_carriere;

I also made a pg_dump of the database and then restored it in a test one, the queries run perfectly well but it gives an error when I add the condition with the operator is null, for example:

dragon_test=# 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 = 2700 ;

 emp_id | institution | emp_id | demission_date

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

   2700 |          11 |        |

   2700 |          52 |        |

(2 rows)

dragon_test=# 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 = 2700 and d.emp_id is null;

 emp_id | institution | emp_id | demission_date

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

(0 rows)

Also, when I run the following command to vacuum all the databases : /usr/local/pgsql/bin/vacuumdb -a -f -z -v -U pascal

All the queries become busted again

Weird, isn’t it ????!!!!!

I’d appreciate any help

Pascal

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, December 10, 2008 7:45 PM
To: ptufenkji@usj.edu.lb
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Is there a bug in PostgreSQL ?

Pascal Tufenkji wrote:

>

> 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 ;

[snip - 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;

[snip - rows]

>

> 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;

[snip - no 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;

Good testing. It looks to me like you have a corrupted index. If you run

EXPLAIN ANALYSE SELECT ... for each of your queries, you'll probably see

that the one that returns no rows is using a particular index that the

other queries aren't.

Have you had any crashes / power failures / disk errors recently?

Oh - and what version of PostgreSQL is this?

--

  Richard Huxton

  Archonet Ltd

I can't, it's an integer column ?!

-----Original Message-----
From: Andreas Kraftl [mailto:andreas.kraftl@kraftl.at]
Sent: Thursday, December 11, 2008 11:47 AM
To: ptufenkji@usj.edu.lb
Subject: Re: [SQL] Is there a bug in PostgreSQL ?

Am Donnerstag, den 11.12.2008, 11:33 +0200 schrieb Pascal Tufenkji:

> dragon_test=# 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 = 2700 and d.emp_id is null;

>

>  emp_id | institution | emp_id | demission_date

>

> --------+-------------+--------+----------------

>

> (0 rows)

What happens, if you try instead of "is null" a =""?

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 = 2700

and d.emp_id = "";

Greetings

Andreas

--

Kraftl EDV - Dienstleistungen

Linux, Linuxschulungen, Webprogrammierung

Autofabrikstraße 16/6

1230 Wien