pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub-select trouble: wrong SQL or PostgreSQL issue?
4+ messages / 3 participants
[nested] [flat]
* sub-select trouble: wrong SQL or PostgreSQL issue?
@ 2002-10-24 02:44 patrick <patrick+pgsql@boxsoft.com>
2002-10-24 02:59 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: patrick @ 2002-10-24 02:44 UTC (permalink / raw)
To: pgsql-sql
Greetings,
I have 3 tables. Two of which (ta and tb) are different
"attributes" the third table (tc) can have.
tc is allowed up to three of each kind of "attributes".
e.g., 3 ta values and 2 tb values.
By assigning ta and tb attributes to each entry in tc you
are also "matching" tb attributes with corresponding ta
ones.
e.g.,
c_id = 8
ta1 = 1 <-- match --> tb1 = 3
ta2 = 3 <-- match --> tb2 = 2
ta3 = 13 <-- match --> tb3 = 20
c_id = 9
ta1 = 2 <-- match --> tb1 = 3
ta2 = 5 <-- match --> tb2 = 3
ta3 = 9 <-- match --> tb3 = 2
c_id = 10
ta1 = 3 <-- match --> tb1 = 2
ta2 = 1 <-- match --> tb2 = 3
ta3 = 9 <-- match --> tb3 = 2
The task at hand is to figure out the number of ta
attributes each tb attribute corresponds with.
For above example I want a result set showing me:
tb.b_id ta.a_id count
--------------------------
2 2 -- matched w/ta.a_id = 3, 9
3 3 -- matched w/ta.a_id = 1, 2, 5
20 1 -- only matched w/ta.a_id = 13
-- tables
create table ta ( a_id int unique );
create table tb ( b_id int unique );
create table tc
(
c_id int unique,
ta1 int references ta( a_id ),
ta2 int references ta( a_id ),
ta3 int references ta( a_id ),
tb1 int references tb( b_id ),
tb2 int references tb( b_id ),
tb3 int references tb( b_id )
);
-- sequences
create sequence ta_seq;
create sequence tb_seq;
create sequence tc_seq;
-- populate tables with some data
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into ta values ( NEXTVAL( 'ta_seq' ) );
insert into tb values ( NEXTVAL( 'tb_seq' ) );
insert into tb values ( NEXTVAL( 'tb_seq' ) );
insert into tb values ( NEXTVAL( 'tb_seq' ) );
insert into tb values ( NEXTVAL( 'tb_seq' ) );
-- ...
insert into tb values ( NEXTVAL( 'tb_seq' ) );
insert into tb values ( NEXTVAL( 'tb_seq' ) );
Don't read too much into the data as i don't think the
values chosen have anything to do with the problem. I
am just using a set of data sequences from my live
database.
Though, i'm willing to be surprised if someone's willing
to show correlation between the problem and the values
used.
-- ta tb
-- 1 2 3 1 2 3
insert into tc values ( NEXTVAL( 'tc_seq' ), 1, 1, 1, 1, 3, 2 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 3, 3, 3, 2, 1, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 4, 4, 4, 2, 1, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 5, 5, 5, 1, 2, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 5, 5, 5, 1, 2, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 1, 1, 1, 1, 2, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 4, 4, 4, 2, 1, 3 );
insert into tc values ( NEXTVAL( 'tc_seq' ), 3, 3, 3, 2, 1, 3 );
/*
insert into tc values ( NEXTVAL( 'tc_seq' ), 1, null, null, 1, null, null );
*/
This last insert is to show it is possible to have null
values for either of the ta or tb attributes in table tc.
But it is omitted to simplify the problem for now.
I am no SQL expert, as in I don't do it for a living.
Therefore, I would not doubt that there exist more
efficient ways to do this but this is one way I came up
with and it is quite possible that a SQL mistake is
causing the strange outputs I am observing.
But let me describe in English what i am attempting to
do in the SQL.
As I said earlier the task is to determine the unique
count of ta entries each tb.b_id is associated with in
the tc table.
I'm selecting on the tb table and doing sub selects on
the tc table where I attempt to count unique counts of
tb to ta matches.
First sub-select (aa) does a distinct count of tc.ta1
where tc.tb1 equals tb.b_id and the corresponding tc.ta1
field is not null.
Second sub-select (bb) I'm again doing a count of
distinct tc.ta2 where tc.tb2 is equal to tb.b_id, tc.ta2
is not null and tc.ta2 was not counted in the aa
sub-select.
Next (cc), once again do a count of distinct tc.ta3
where tc.tb3 is equal to tb.b_id, tc.ta3 is not null and
not in either of the two previous sub-selects (aa, bb).
The sum of sub-selects aa, bb and cc should be the value
I'm interested in (not shown in SQL statement).
The problem:
The result of the select is not the expected counts of ta
to tb.
The twist:
If the select statement is provided a where-clause where
each tb.b_id is restricted to individual tb.b_id values
then the correct/expected count of ta to tb is obtained
(See examples below).
-- selects
select tb.b_id,
(
select count( distinct tc.ta1 )
from tc
where tc.tb1 = tb.b_id
and
tc.ta1 is not null
) as aa,
(
select count( distinct tc.ta2 )
from tc
where tc.tb2 = tb.b_id
and
tc.ta2 is not null
and
tc.ta2 not in
(
select distinct tc.ta1
from tc
where tc.tb1 = tb.b_id
and
tc.ta1 is not null
)
) as bb,
(
select count( distinct tc.ta3 )
from tc
where tc.tb3 = tb.b_id
and
tc.ta3 is not null
and
tc.ta3 not in
(
select distinct tc.ta1
from tc
where tc.tb1 = tb.b_id
and
tc.ta1 is not null
)
and
tc.ta3 not in
(
select distinct tc.ta2
from tc
where tc.tb2 = tb.b_id
and
tc.ta2 is not null
)
) as cc
from tb
;
-- results from selects
=# select * from tc order by c_id;
c_id | ta1 | ta2 | ta3 | tb1 | tb2 | tb3
------+-----+-----+-----+-----+-----+-----
1 | 1 | 1 | 1 | 1 | 3 | 2
2 | 3 | 3 | 3 | 2 | 1 | 3
3 | 4 | 4 | 4 | 2 | 1 | 3
4 | 5 | 5 | 5 | 1 | 2 | 3
5 | 5 | 5 | 5 | 1 | 2 | 3
6 | 1 | 1 | 1 | 1 | 2 | 3
7 | 4 | 4 | 4 | 2 | 1 | 3
8 | 3 | 3 | 3 | 2 | 1 | 3
(8 rows)
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id < 4;
b_id | aa | bb | cc
------+----+----+----
1 | 2 | 2 | 0
2 | 2 | 0 | 0
3 | 0 | 0 | 0
(3 rows)
Obviously not the correct result for rows 2 and 3.
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id = 1;
b_id | aa | bb | cc
------+----+----+----
1 | 2 | 2 | 0
(1 row)
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id = 2;
b_id | aa | bb | cc
------+----+----+----
2 | 2 | 2 | 0
(1 row)
This is what we should've gotten in the first query for
the second row.
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id = 3;
b_id | aa | bb | cc
------+----+----+----
3 | 0 | 1 | 3
(1 row)
This is what we should've gotten in the first query for
the third row.
Further strangeness:
I would think that the following two select statements
should yeild the same results.
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id = 1 or tb.b_id = 2 or tb.b_id = 3;
b_id | aa | bb | cc
------+----+----+----
1 | 2 | 2 | 0
2 | 2 | 0 | 0
3 | 0 | 0 | 0
(3 rows)
Incorrect output. Same as the very first query.
=# select tb.b_id, ( select count( distinct tc.ta1 ) from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) as aa, ( select count( distinct tc.ta2 ) from tc where tc.tb2 = tb.b_id and tc.ta2 is not null and tc.ta2 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null)) as bb, ( select count( distinct tc.ta3 ) from tc where tc.tb3 = tb.b_id and tc.ta3 is not null and tc.ta3 not in ( select distinct tc.ta1 from tc where tc.tb1 = tb.b_id and tc.ta1 is not null) and tc.ta3 not in ( select distinct tc.ta2 from tc where tc.tb2 = tb.b_id and tc.ta2 is not null)) as cc from tb where tb.b_id = 3 or tb.b_id = 2 or tb.b_id = 1;
b_id | aa | bb | cc
------+----+----+----
3 | 0 | 1 | 3
2 | 2 | 2 | 0
1 | 2 | 2 | 0
(3 rows)
Strangely, this is the result we were expecting from our
original query!
Is it possible that the sub-selects are somehow
affecting the result sets?
Seen on:
% postmaster --version
postmaster (PostgreSQL) 7.1.3
and
% postmaster --version
postmaster (PostgreSQL) 7.1.2
-- drops
drop sequence tc_seq;
drop sequence tb_seq;
drop sequence ta_seq;
drop table tc;
drop table tb;
drop table ta;
I'd appreciate any comments you may have regarding this
post :-)
sidster
--
They who would sacrifice freedom for security will have neither.
-Ben Franklin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub-select trouble: wrong SQL or PostgreSQL issue?
2002-10-24 02:44 sub-select trouble: wrong SQL or PostgreSQL issue? patrick <patrick+pgsql@boxsoft.com>
@ 2002-10-24 02:59 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-24 03:28 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-10-24 02:59 UTC (permalink / raw)
To: patrick <patrick+pgsql@boxsoft.com>; +Cc: pgsql-sql
> Strangely, this is the result we were expecting from our
> original query!
>
> Is it possible that the sub-selects are somehow
> affecting the result sets?
>
>
> Seen on:
> % postmaster --version
> postmaster (PostgreSQL) 7.1.3
>
> and
>
> % postmaster --version
> postmaster (PostgreSQL) 7.1.2
I think you want to upgrade, 7.3beta at least gives your
expected output on all the queries when I tried it. I don't
currently have a 7.2.* system to test on here to try it
with that series.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub-select trouble: wrong SQL or PostgreSQL issue?
2002-10-24 02:44 sub-select trouble: wrong SQL or PostgreSQL issue? patrick <patrick+pgsql@boxsoft.com>
2002-10-24 02:59 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-10-24 03:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-24 09:15 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? patrick <patrick+pgsql@boxsoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-10-24 03:28 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: patrick <patrick+pgsql@boxsoft.com>; pgsql-sql
>> Is it possible that the sub-selects are somehow
>> affecting the result sets?
> I think you want to upgrade, 7.3beta at least gives your
> expected output on all the queries when I tried it. I don't
> currently have a 7.2.* system to test on here to try it
> with that series.
I think this may be the same problem as bug #526,
http://archives.postgresql.org/pgsql-bugs/2001-11/msg00168.php
If so, the fix is in 7.2.*.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub-select trouble: wrong SQL or PostgreSQL issue?
2002-10-24 02:44 sub-select trouble: wrong SQL or PostgreSQL issue? patrick <patrick+pgsql@boxsoft.com>
2002-10-24 02:59 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-24 03:28 ` Re: sub-select trouble: wrong SQL or PostgreSQL issue? Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-10-24 09:15 ` patrick <patrick+pgsql@boxsoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: patrick @ 2002-10-24 09:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
Thank you both for your prompt responses. Bug #526 (as Tom
pointed out) does look very similar to my problem.
I'll attempt to upgrade my development PostgreSQL and
eventually my production servers if all goes well.
Thanks again,
On Wed, Oct 23, 2002 at 11:28:01PM -0400, Tom Lane wrote:
> >> Is it possible that the sub-selects are somehow
> >> affecting the result sets?
>
> > I think you want to upgrade, 7.3beta at least gives your
> > expected output on all the queries when I tried it. I don't
> > currently have a 7.2.* system to test on here to try it
> > with that series.
>
> I think this may be the same problem as bug #526,
> http://archives.postgresql.org/pgsql-bugs/2001-11/msg00168.php
> If so, the fix is in 7.2.*.
>
> regards, tom lane
sidster
--
They who would sacrifice freedom for security will have neither.
-Ben Franklin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-24 09:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-24 02:44 sub-select trouble: wrong SQL or PostgreSQL issue? patrick <patrick+pgsql@boxsoft.com>
2002-10-24 02:59 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-24 03:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-24 09:15 ` patrick <patrick+pgsql@boxsoft.com>
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