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:[EMAIL PROTECTED] Sent: Wednesday, December 10, 2008 7:45 PM To: [EMAIL PROTECTED] 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
