Postgres Pro
Downloads
Home   >   mailing lists

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

From Pascal Tufenkji
Subject Is there a bug in PostgreSQL ?
Date
Msg-id 008701c95ad4$38870870$150fa8c0@interne.usj.edu.lb
Whole thread Raw
Responses Re: Is there a bug in PostgreSQL ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Is there a bug in PostgreSQL ?  (Richard Huxton <dev@archonet.com>)
List pgsql-sql
Tree view 
<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>

pgsql-sql by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: inconsistent automatic casting between psql and function
Next
From: "Filip Rembiałkowski"
Date:
Subject: Re: Collapsing (select) row values into single text field.