Re: Is there a bug in PostgreSQL ?

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(at)archonet(dot)com]
Sent: Wednesday, December 10, 2008 7:45 PM
To: ptufenkji(at)usj(dot)edu(dot)lb
Cc: pgsql-sql(at)postgresql(dot)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