Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is there a bug in PostgreSQL ?

Is there a bug in PostgreSQL ?

From
"Pascal Tufenkji"
Date:
<div class="Section1"><p class="MsoNormal"><b><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">Hello,</span></font></b><p class="MsoNormal"><b><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana;font-weight:bold"> </span></font></b><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">I’m writing a query with a left join to a view, and the server is giving me a
wrongresult.</span></font></b><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">SELECT emp_id,institution from sip_carriere where emp_id = 342 and institution =
1;</span></font><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New""> emp_id | institution</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">--------+-------------</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">    342 |           1</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(1 row)</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">SELECT * from sip_demissionaire where emp_id = 342;</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New""> emp_id | demission_date</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">--------+----------------</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(0 rows)</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New""> </span></font><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">IF I COMBINE THE TWO QUERIES WITH A LEFT JOIN QUERY AND PUT ONLY ONE CONDITION,
ITWOKS JUST FINE :</span></font></b><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join sip_demissionaire d
ond.emp_id = c.emp_id where c.emp_id = 342 ;</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New""> emp_id | institution | emp_id | demission_date</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">--------+-------------+--------+----------------</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">    342 |           1 |        |</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">    342 |          63 |        |</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">    342 |          85 |        |</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(3 rows)</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join sip_demissionaire d
ond.emp_id = c.emp_id where c.institution = 1;</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">  emp_id | institution | emp_id | demission_date</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New""> --------+-------------+--------+----------------</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">     342 |           1 |        |</span></font><p class="MsoNormal"><font color="blue"
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span></font><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">     ... |        ... |     ...|  
        ...</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">BUT IF I PUT BOTH CONDITIONS</span></font></b><p class="MsoNormal"><font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red">SELECT distinct c.emp_id,c.institution,d.* from
sip_carrierec left join sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution =
1;</span></font><pclass="MsoNormal"><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red"> emp_id | institution | emp_id |
demission_date</span></font><pclass="MsoNormal"><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"Courier
New";color:red">--------+-------------+--------+----------------</span></font><pclass="MsoNormal"><font color="red"
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:red">(0 rows)</span></font><p
class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">What’s the problem ?</span></font></b><p class="MsoNormal"><b><font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana;font-weight:bold">I’m sure that the problem is with the view “sip_demissionaire” cause when I
copiedits content to a temp table, the query returned a result…</span></font></b><p class="MsoNormal"><font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">SELECT * into temp foo from sip_demissionaire ;</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join foo d on d.emp_id =
c.emp_idwhere c.emp_id = 342 and c.institution = 1;</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New""> emp_id | institution | emp_id | demission_date</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">--------+-------------+--------+----------------</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">    342 |           1 |        |</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(1 row)</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">Here’s the description of the view “sip_demissionaire” in case you need
it</span></font></b><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">CREATE VIEW sip_demissionaire AS </span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">      SELECT t1.* from </span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">      (</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">            SELECT emp_id,max(demission_date) as demission_date </span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">            from sip_carriere_dates </span></font><p class="MsoNormal"><font face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">            where demission_date is not null </span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">            group by emp_id</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">      ) as t1 </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">      left join </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">      (</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">            select emp_id</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">            from sip_carriere_dates </span></font><p class="MsoNormal"><font face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">            where demission_date is null</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      ) as t2 on t1.emp_id = t2.emp_id</span></font><p class="MsoNormal"><font face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      where t2.emp_id is null</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">);</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><b><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana;font-weight:bold">I know it’s a long mail, but I’d appreciate any help</span></font></b><p
class="MsoNormal"><b><fontface="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana;font-weight:bold">Thx in advance</span></font></b><p class="MsoNormal"><b><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana;font-weight:bold">Pascal </span></font></b></div>

Re: Is there a bug in PostgreSQL ?

From
Tom Lane
Date:
"Pascal Tufenkji" <ptufenkji@usj.edu.lb> writes:
> I'm writing a query with a left join to a view, and the server is giving me
> a wrong result.

What PG version?
        regards, tom lane

Re: Is there a bug in PostgreSQL ?

From
Richard Huxton
Date:
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

Re: Is there a bug in PostgreSQL ?

From
"Pascal Tufenkji"
Date:
<div class="Section1"><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Hello
again,</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">ActuallyI'm using PostgreSQL 8.2.4</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Bythe way, I tried reindexing the tables but the problem remains </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">REINDEX TABLE
sip_carriere_dates;</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">REINDEXTABLE sip_carriere;</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Ialso made a pg_dump of the database and then restored it in a test one, the queries run
perfectlywell but it gives an error when I add the condition with the operator is null, for example:</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">dragon_test=# select distinct c.emp_id, c.institution, d.* from sip_carriere c left
joinsip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 2700 ;</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New""> emp_id | institution | emp_id | demission_date</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">--------+-------------+--------+----------------</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">   2700 |          11 |        |</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">   2700 |          52 |        |</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">(2 rows)</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt;font-family:"Courier New""> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt;font-family:"Courier New"">dragon_test=# select distinct c.emp_id, c.institution, d.* from sip_carriere c left
joinsip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 2700 <b><font color="red"><span
style="color:red;font-weight:
bold">and d.emp_id is null</span></font></b>;</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt;font-family:"Courier New""> emp_id | institution | emp_id | demission_date</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">--------+-------------+--------+----------------</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt;font-family:"Courier New"">(0 rows)</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Also,when I run the following command to vacuum all the databases :
/usr/local/pgsql/bin/vacuumdb-a -f -z -v -U pascal</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">All the queries become busted again</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">Weird, isn’t it ????!!!!!</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">I’d appreciate any help</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt">Pascal </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">-----OriginalMessage-----<br /> From: Richard Huxton [mailto:dev@archonet.com] <br /> Sent:
Wednesday,December 10, 2008 7:45 PM<br /> To: ptufenkji@usj.edu.lb<br /> Cc: pgsql-sql@postgresql.org<br /> Subject:
Re:[SQL] Is there a bug in PostgreSQL ?</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">PascalTufenkji wrote:</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> sip_demissionaire d on d.emp_id =
c.emp_idwhere c.emp_id = 342 ;</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">[snip- rows]</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> sip_demissionaire d on d.emp_id =
c.emp_idwhere c.institution = 1;</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">[snip- rows]</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>BUT IF I PUT BOTH CONDITIONS</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> sip_demissionaire d on d.emp_id =
c.emp_idwhere c.emp_id = 342 and</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>c.institution = 1;</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">[snip - no rows]</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>What's the problem ?</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>I'm sure that the problem is with the view "sip_demissionaire" cause when
I</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">> copied its
contentto a temp table, the query returned a result.</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>SELECT * into temp foo from sip_demissionaire ;</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">> SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join
foo</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">> d on d.emp_id
=c.emp_id where c.emp_id = 342 and c.institution = 1;</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Goodtesting. It looks to me like you have a corrupted index. If you run</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">EXPLAIN ANALYSE SELECT ... for each of
yourqueries, you'll probably see</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">thatthe one that returns no rows is using a particular index that the</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">other queries aren't.</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Have you had any crashes / power
failures/ disk errors recently?</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Oh- and what version of PostgreSQL is this?</span></font><p class="MsoPlainText"><font
face="Verdana"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">-- </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> Richard Huxton</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> Archonet Ltd</span></font></div>

Re: Is there a bug in PostgreSQL ?

From
"Pascal Tufenkji"
Date:
<div class="Section1"><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">I can't,
it'san integer column ?!</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">-----OriginalMessage-----<br /> From: Andreas Kraftl [mailto:andreas.kraftl@kraftl.at] <br />
Sent:Thursday, December 11, 2008 11:47 AM<br /> To: ptufenkji@usj.edu.lb<br /> Subject: Re: [SQL] Is there a bug in
PostgreSQL?</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">AmDonnerstag, den 11.12.2008, 11:33 +0200 schrieb Pascal Tufenkji:</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> dragon_test=# select distinct
c.emp_id,c.institution, d.* from</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>sip_carriere c left join sip_demissionaire d on d.emp_id = c.emp_id</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> where c.emp_id = 2700 and
d.emp_idis null;</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">>
</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">>  emp_id |
institution| emp_id | demission_date</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">>--------+-------------+--------+----------------</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> (0 rows)</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">What happens, if you try instead of
"isnull" a =""?</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">selectdistinct c.emp_id, c.institution, d.* from sip_carriere c left</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">join sip_demissionaire d on d.emp_id =
c.emp_idwhere c.emp_id = 2700</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">andd.emp_id = "";</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Greetings</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">Andreas</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">--</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span
style="font-size:10.0pt">KraftlEDV - Dienstleistungen</span></font><p class="MsoPlainText"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt">Linux, Linuxschulungen, Webprogrammierung</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Autofabrikstraße 16/6</span></font><p
class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">1230 Wien</span></font></div>

Re: Is there a bug in PostgreSQL ?

From
Tom Lane
Date:
"Pascal Tufenkji" <ptufenkji@usj.edu.lb> writes:
> Actually I'm using PostgreSQL 8.2.4

Well, in that case the answer to $SUBJECT is "Yes".  Please update to
something reasonably current --- at least 8.2.7, which contains the most
recent fix for join planning logic according to a quick scan of the
CVS logs.  (8.2.11 is the most recent release in that branch.)

If you can still reproduce the problem on 8.2.latest then it would be
worth investigating further; but right at the moment I think it's a
good bet that this is the same as one of the already-identified bugs
in outer join planning.
        regards, tom lane

Re: Is there a bug in PostgreSQL ?

From
"Pascal Tufenkji"
Date:
You were exactly right
I installed PostgreSQL 8.2.11 and it works perfectly well
Thank you

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tom Lane
Sent: Thursday, December 11, 2008 4:40 PM
To: ptufenkji@usj.edu.lb
Cc: 'Richard Huxton'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Is there a bug in PostgreSQL ?

"Pascal Tufenkji" <ptufenkji@usj.edu.lb> writes:
> Actually I'm using PostgreSQL 8.2.4

Well, in that case the answer to $SUBJECT is "Yes".  Please update to
something reasonably current --- at least 8.2.7, which contains the most
recent fix for join planning logic according to a quick scan of the
CVS logs.  (8.2.11 is the most recent release in that branch.)

If you can still reproduce the problem on 8.2.latest then it would be
worth investigating further; but right at the moment I think it's a
good bet that this is the same as one of the already-identified bugs
in outer join planning.
        regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql