Thread
-
Re: [SQL] Wierd stuff
Jan Wieck <wieck@debis.com> — 1999-12-03T16:33:32Z
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) # -
Re: [SQL] Wierd stuff
Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt> — 1999-12-03T16:46:37Z
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 -
Re: [SQL] Wierd stuff
Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt> — 1999-12-03T17:23:04Z
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 -
Re: [SQL] Wierd stuff
Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt> — 1999-12-03T17:28:23Z
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