SQL query returning incorrect result. Bug?!
Hello,
The following query yields an incorrect result (in OnLine 6.0) :
(this query should give us the no. and name of the composer(s) of
which we have at least 3 symphonies)
select compno, name
from composer
where compno =
(select compno from work --> returns 1 value, i.e. 2
where type = 'SYMPHONY'
group by compno
having count(distinct workno) >=3)
The result is :
compno name
18 ESPAGNOLE
in stead of :
compno name
2 BEETHOVEN
The contents of the tables involved are as follows :
COMPOSER :
compno name
1 HAENDEL
2 BEETHOVEN
3 BACH
.. .........
18 ESPAGNOLE
WORK :
workno compno type
1 1 CONCERT
14 1 CONCERT
15 1 CONCERT
31 2 CONCERT
2 2 SYMPHONY
3 2 SYMPHONY
27 2 SYMPHONY
35 2 SYMPHONY
13 2 VIOLIN MUSIC
.. .. ........
47 18 SYMPHONY
(compno and workno are defined as serials)
If the WHERE-clause of the query is replaced by
"where compno IN (....)"
then the correct result is obtained.
In fact, "IN" should be used here in stead of "=" because the subquery -
which uses grouping - has the potential of returning multiple values
(and generally will). In this case however, the subquery returns only a
single value.
In our opinion, if the database server cannot handle such queries
correctly,it should return an error in stead of an incorrect result.
Is this a known bug in OnLine 6 (and previous versions) ?
Thanks in advance,
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