Re: problems with nested correlated queries
Ria Vandenberghe (rvdbergh@egidius.rug.ac.be) wrote:
: Has anyone of you ever experienced problems with queries with multiple
: levels of nesting, where an (inner) subquery is correlated to an (outer)
: subquery which is 2 (or more) levels of nesting above it?
: We found that some of these queries yield an incorrect result (more
: precisely, a superset of the correct set of rows).
: We are using OnLine 6.0 under Solaris 2.3 on a Sun SparcServer 10/512.
Below, I have included an example of such a query, returning an incorrect
result.
TABLES USED :
activity : (activities which are organized)
--------
actno date .....
A001 04/09/93
A002 05/09/93
A003 11/09/93
A004 11/09/93
A005 19/09/93
A006 19/09/93
A007 19/09/93
A008 19/09/93
A009 25/09/93
A010 26/09/93
A011 26/09/93
A012 26/09/93
A013 03/10/93
group : (groups of members: one or more groups in each age category)
-----
groupno age .....
G1 6
G2 8
G3 8
G4 10
G5 10
G6 12
G7 15
planned : (which activities are planned for which groups)
-------
actno groupno
A001 G7
A002 G1
A002 G2
A002 G3
A002 G4
A002 G5
A002 G6
A002 G7
A003 G1
A003 G2
A003 G3
A004 G4
A004 G5
A004 G6
A005 G1
A006 G2
A006 G3
A007 G4
A007 G5
A007 G6
A008 G6
A008 G7
A009 G6
A009 G7
A010 G5 <--
A011 G4 <--
A012 G1
A012 G2
A012 G3
A013 G2
A013 G3
A013 G4
A013 G5
QUERY :
select actno
from activity
where not exists
(select groupno
from group G
where groupno in
(select groupno
from planned
where actno = activity.actno)
and exists
(select groupno
from group
where age = G.age
and groupno <> G.groupno
and groupno not in
(select groupno
from planned
where actno = activity.actno)))
(The purpose of this query is to give the actno of the activities which
were organized only for (one or more) complete age categories)
The above query yields a list with the actno of *all* the activities
organized, i.e. A001, A002, .... A013.
However, it is clear that A010 and A011 should not be included in this
list (since these activities are planned for just one group of an age
category, as indicated) !
Now, if the where-condition :
actno = 'A010' and ...
or
actno = 'A011' and ...
is added to the outer query, strangely enough, the query returns an emty
result, as it should!?
Ria.
-----------------------------------------------------------
Ria Vandenberghe Ria.Vandenberghe@rug.ac.be
UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory
Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM
tel: +32/9/264.55.10 fax: +32/9/264.58.42