Re: SQL query returning inco

Reply to: RE>SQL query returning incorrect result. Bug?! Ria, I recreated your tables using Informix-SE 5.01. I yielded the correct result using your exact query (even though it seems you would want to use the "where compno in" clause to avoid multiple record problems in the future). This is perplexing and disconcerting since we are moving to online 6.0 in the near future. I will be curious to see what other online users say about your problem. Dave Mascorro The University of Texas Health Science Center at San Antonio dave@oncology.uthscsa.edu -------------------------------------- Date: 12/6/94 5:13 AM To: David Mascorro From: Ria Vandenberghe Received: by stimpy.oncology.uthscsa.edu with SMTP;6 Dec 1994 05:12:10 -0700 Received: from rmy.rmy.emory.edu by pythia.oncology.uthscsa.edu (4.1/SMI-4.1) id AA01823; Tue, 6 Dec 94 05:24:01 CST Received: by rmy.rmy.emory.edu (5.65/Emory_rmy.3.4.0) via MAILPROG id AA10299 ; Tue, 6 Dec 94 06:16:23 -0500 Return-Path: ilist@rmy.emory.edu From: rvdbergh@eduserv.rug.ac.be (Ria Vandenberghe) Message-Id: <3c1bhu$8sr@infoserv.rug.ac.be> Subject: SQL query returning incorrect result. Bug?! Date: 6 Dec 1994 09:40:14 GMT Reply-To: rvdbergh@eduserv.rug.ac.be (Ria Vandenberghe) Organization: University of Ghent, Belgium Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-To: dave@oncology.uthscsa.edu X-Informix-List-Id: <news.10133> 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