pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWierd stuff
9+ messages / 3 participants
[nested] [flat]
* Wierd stuff
@ 1999-12-03 15:24 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
0 siblings, 0 replies; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-03 15:24 UTC (permalink / raw)
To: pgsql-sql
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?
I'm using POstgresql 6.3.2 (yes i know: I should upgrade)
Thanks
--
Mario Filipe
mjnf@uevora.pt
http://neptuno.sc.uevora.pt/~mjnf
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-03 16:33 Jan Wieck <wieck@debis.com>
parent: Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
0 siblings, 3 replies; 9+ messages in thread
From: wieck@debis.com @ 1999-12-03 16:33 UTC (permalink / raw)
To: mjnf@uevora.pt; +Cc: pgsql-sql
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) #
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-03 16:46 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
0 siblings, 1 reply; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-03 16:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-03 17:23 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
parent: Jan Wieck <wieck@debis.com>
2 siblings, 0 replies; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-03 17:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-03 17:28 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
parent: Jan Wieck <wieck@debis.com>
2 siblings, 0 replies; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-03 17:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-03 23:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <wieck@debis.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 1999-12-03 23:00 UTC (permalink / raw)
To: wieck@debis.com; +Cc: mjnf@uevora.pt; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-06 13:49 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-06 13:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-06 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 1999-12-06 15:14 UTC (permalink / raw)
To: mjnf@uevora.pt; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Wierd stuff
@ 1999-12-07 09:53 Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Mario Jorge Nunes Filipe @ 1999-12-07 09:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~1999-12-07 09:53 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-03 15:24 Wierd stuff Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
1999-12-03 16:46 Re: [SQL] Wierd stuff Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
1999-12-03 16:33 ` Jan Wieck <wieck@debis.com>
1999-12-03 17:23 ` Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
1999-12-03 17:28 ` Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
1999-12-03 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-06 13:49 ` Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
1999-12-06 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-07 09:53 ` Mario Jorge Nunes Filipe <mjnf@neptuno.sc.uevora.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox