Re: Is there a bug in PostgreSQL ? - Mailing list pgsql-sql

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