Thread

1. Re: [SQL] Wierd stuff

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-12-03T23:00:32Z

   wieck@debis.com (Jan Wieck) writes:
   > Mario Filipe wrote:
   >>>> | concelho_naturalidade            | int2
   >>>> ...
   >>>> | codigo                           | int4
   >>>> 
   >>>> The field "concelho_naturalidade" on the first table has values taken
   >>>> from the "codigo" field from the second.
   
   >     First  of  all,  why  aren't  the two fields of the same data
   >     type? Shouldn't cause your problem,  but  it's  bad  database
   >     design anyway IMHO.
   
   Actually, I think that this may indeed be the root of the problem.
   I killed several bugs in cross-data-type joins a while back ... for
   example, IIRC pre-6.5 Postgres thought it could use hash joins for
   cross-data-type equality operators, but that doesn't work properly in
   general.  Mario, what does EXPLAIN show about how the join query is
   being done?
   
   If I'm right about this, then upgrading to 6.5.* should fix the problem.
   
   			regards, tom lane
   
   

2. Re: [SQL] Wierd stuff

   Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt> — 1999-12-06T13:49:38Z

   Tom Lane wrote:
   > Actually, I think that this may indeed be the root of the problem.
   > I killed several bugs in cross-data-type joins a while back ... for
   > example, IIRC pre-6.5 Postgres thought it could use hash joins for
   > cross-data-type equality operators, but that doesn't work properly in
   > general.  Mario, what does EXPLAIN show about how the join query is
   > being done?
   
   	First of all: sorry for the late reply, but with the weekend and all...
   
   	To awnser your question... EXPLAIN shows this:
   sac=> explain select c.nome from aluno a, concelho c where
   sac-> a.concelho_naturalidade=c.codigo and a.numero_aluno=13792;
   NOTICE:  QUERY PLAN:
   
   Hash Join  (cost=27.44 size=164 width=18)
     ->  Seq Scan on c  (cost=13.16 size=308 width=16)
     ->  Hash  (cost=0.00 size=0 width=0)
           ->  Index Scan on a  (cost=2.05 size=2 width=2)
   
   EXPLAIN
   
   	Upgrading at this point might be complicated, but sooner or later i
   will have to upgrade...
   
   -- 
           Mario Filipe 
           mjnf@uevora.pt
           http://neptuno.sc.uevora.pt/~mjnf