Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!unmvax!ncar!tank!mimsy!haven!vrdxhq!daitc!jkrueger@daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: INGRES oddity (or is it me?)
Message-ID: <263@daitc.daitc.mil>
Date: 20 Dec 88 01:55:18 GMT
References: <2183@nunki.usc.edu> <8558@alice.UUCP>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: Defense Applied Information Technology Center
Lines: 44
In-reply-to: debra@alice.UUCP (Paul De Bra)

In article <8558@alice.UUCP>, debra@alice (Paul De Bra) writes:
>[masterful summary of SQL syntax ambiguity deleted]

>[possible workaround:]
>create table t2 as
>select dname, tot_students
>from
>select dname, classnum, tot_students=sum(students)
>from t1
>group by dname;
>
>This may help indicate to SQL to evaluate the sum before the projection, but
>a (bogus) optimizer may remove the extra step again.

I believe QUEL disambiguates such queries:
	retrieve into t2 (t1.dname, t1.classnum, tot_students = sum(students))
since each aggregation is independent of the others in the target list.

The cost of this is great difficulty getting table names, domains, and
restrictions to act on all clauses in common.  Best example is the
awkward syntax for a difference operation
	/* find Republicans with acceptable pasts */
	retrieve (person.all) where person.party = "GOP" and
		any(crimes.severity by crimes.person where
			person.party = "GOP" and
			crimes.person = person.name and
			crimes.severity > "misdemeanor") = 0

The repeated GOP qualifier is needed because the person domain inside
the aggregation bears no relation to the person domain elsewhere.  The
any(foo) = 0 is a separate awkwardness.  Much nicer is the SQL

	select * from person where party = "GOP" and not exists
		(select * from crimes where severity > "misdemeanor"
			and crimes.person = person.name);

So I'd say the tradeoffs all circle around the semantics of
aggregation.  If aggregation is isolated from select and project
elsewhere in the query, it saves us from unpredictable order of query
evaluation/execution effects, but it pushes the limits of grouping
syntax and leads to replicate code.

-- Jon
--