Thread: Re: [SQL] Wierd stuff
Mario Jorge Nunes Filipe wrote: > > Hi > > I'm developing a system with a large number of tables. > > One of the tables has (among others) the following fields: > Table = aluno > +----------------------------------+--------------------------------- > | Field | Type > +----------------------------------+--------------------------------- > | numero_aluno | int4 > | freguesia_naturalidade | int2 > | concelho_naturalidade | int2 > | nacionalidade | int2 > > another is like this: > > -- Table = concelho > +----------------------------------+--------------------------------- > | Field | Type > +----------------------------------+--------------------------------- > | codigo | int4 > | nome | text > > The field "concelho_naturalidade" on the first table has values taken > from the "codigo" field from the second. > > Now comes the problem: > > When i do : > sac=> select a.numero_aluno, a.concelho_naturalidade from aluno a where > a.numero_aluno=13792; > > i get: > > numero_aluno|concelho_naturalidade > ------------+--------------------- > 13792| 1006 > (1 row) > > and whith: > sac=> select * from concelho where codigo=1006; > > i get: > > codigo|nome > ------+---------------- > 1006|CALDAS DA RAINHA > (1 row) > > but with: > sac=> select c.nome from aluno a, concelho c where > a.concelho_naturalidade=c.codigo and a.numero_aluno=13792; > > i get: > > nome > ---- > (0 rows) > > If my SQL is worth anything I thought it would reply "Caldas da Rainha". > What is wrong here? And now... even wierder (?): sac=> select a.numero_aluno, a.concelho_naturalidade from aluno a sac-> where sac-> a.numero_aluno=15897; numero_aluno|concelho_naturalidade ------------+--------------------- 15897| 0 (1 row) sac=> select * from concelho where codigo=0; codigo|nome ------+----------- 0|A Preencher (1 row) sac=> select c.nome from aluno a, concelho c where sac-> a.concelho_naturalidade=c.codigo and a.numero_aluno=15897; nome ----------- A Preencher (1 row) this is the expected output... the first one isn't. So does anyone have a clue on what's going on? This is not particularly critical has i can change the third query fro the first two, only it becomes very inneficient. Thanks -- Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf
Mario Filipe wrote:
> > I'm developing a system with a large number of tables.
> >
> > One of the tables has (among others) the following fields:
> > Table = aluno
> > +----------------------------------+---------------------------------
> > | Field | Type
> > +----------------------------------+---------------------------------
> > | numero_aluno | int4
> > | freguesia_naturalidade | int2
> > | concelho_naturalidade | int2
> > | nacionalidade | int2
> >
> > another is like this:
> >
> > -- Table = concelho
> > +----------------------------------+---------------------------------
> > | Field | Type
> > +----------------------------------+---------------------------------
> > | codigo | int4
> > | nome | text
> >
> > 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.
> > Now comes the problem:
> > [...]
>
> And now... even wierder (?):
> [...]
>
> this is the expected output... the first one isn't. So does anyone have
> a clue on what's going on?
IIRC, corrupted indices can cause this type of misbehaviour.
If you have any indices defined on the two tables, try to
drop and recreate them.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Jan Wieck wrote: > > Mario Filipe wrote: > > > > I'm developing a system with a large number of tables. > > > > > > One of the tables has (among others) the following fields: > > > Table = aluno > > > +----------------------------------+--------------------------------- > > > | Field | Type > > > +----------------------------------+--------------------------------- > > > | numero_aluno | int4 > > > | freguesia_naturalidade | int2 > > > | concelho_naturalidade | int2 > > > | nacionalidade | int2 > > > > > > another is like this: > > > > > > -- Table = concelho > > > +----------------------------------+--------------------------------- > > > | Field | Type > > > +----------------------------------+--------------------------------- > > > | codigo | int4 > > > | nome | text > > > > > > 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. Uhmmm! I could try a reasonable reason here but i can't find one... It will be fixed! > > > Now comes the problem: > > > [...] > > > > And now... even wierder (?): > > [...] > > > > this is the expected output... the first one isn't. So does anyone have > > a clue on what's going on? > > IIRC, corrupted indices can cause this type of misbehaviour. > If you have any indices defined on the two tables, try to > drop and recreate them. I'll try this one out. Thanks -- Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf
Jan Wieck wrote: > IIRC, corrupted indices can cause this type of misbehaviour. > If you have any indices defined on the two tables, try to > drop and recreate them. It didn't sove the problem... any more hints are welcomed... -- Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf
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
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
Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt> writes:
> 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?
> 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)
Yup, that's what I suspected. Hashing 2-byte and 4-byte integers
doesn't deliver the same result --- at least not all the time, there
are special cases such as zero. So this explains all your observed
misbehaviors.
You can work around it for now by remaking the tables with consistent
data types.
regards, tom lane
Tom Lane wrote: > Yup, that's what I suspected. Hashing 2-byte and 4-byte integers > doesn't deliver the same result --- at least not all the time, there > are special cases such as zero. So this explains all your observed > misbehaviors. > > You can work around it for now by remaking the tables with consistent > data types. OK! Thanks... -- Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf