Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!tut.cis.ohio-state.edu!usenet.ins.cwru.edu!ncoast!allbery
From: allbery@NCoast.ORG (Brandon S. Allbery KB8JRR)
Newsgroups: comp.databases
Subject: Re: Problem with SQL join/group-by in Unify database (long)
Message-ID: <1991Jan21.001428.21987@NCoast.ORG>
Date: 21 Jan 91 00:14:28 GMT
References: <584@fciva.FRANKCAP.COM>
Reply-To: allbery@ncoast.ORG (Brandon S. Allbery KB8JRR)
Followup-To: comp.databases
Distribution: na
Organization: North Coast Computer Resources (ncoast)
Lines: 22

As quoted from <584@fciva.FRANKCAP.COM> by dag@fciva.FRANKCAP.COM (Daniel A. Graifer):
+---------------
| sql> select rd_co_id, rd_co_name, count(*), sum(ap_loan_amt) from apmaster,
| sql>	rolodex where ap_funding_date between 12/01/90 and 12/31/90
| sql>	and ap_region = 2 and rd_co_id = ap_broker
| sql>	group by rd_co_name /
+---------------

There is indeed a bug here... but not the one you think.  A selection using
GROUP BY must group by all the non-aggregates in the SELECT clause or the
result is undefined.  Most ANSI SQL's catch this; Unify 4.0 SQL does not, and
instead silently mis-groups things.  Try grouping by both rd_co_id and
rd_co_name here.  I noticed the same problem in the third example of GROUP
BY; the first example appeared correct compared to the actual data shown in
the final select, and (unsurprisingly) the GROUP BY clause was correct.

++Brandon
-- 
Me: Brandon S. Allbery			    VHF/UHF: KB8JRR on 220, 2m, 440
Internet: allbery@NCoast.ORG		    Packet: KB8JRR @ WA8BXN
America OnLine: KB8JRR			    AMPR: KB8JRR.AmPR.ORG [44.70.4.88]
uunet!usenet.ins.cwru.edu!ncoast!allbery    Delphi: ALLBERY