Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!rutgers!bellcore!faline!thumper!ulysses!andante!alice!debra
From: debra@alice.UUCP (Paul De Bra)
Newsgroups: comp.databases
Subject: Re: INGRES oddity (or is it me?)
Message-ID: <8558@alice.UUCP>
Date: 18 Dec 88 20:36:15 GMT
References: <2183@nunki.usc.edu>
Reply-To: debra@alice.UUCP ()
Organization: AT&T, Bell Labs
Lines: 67

In article <2183@nunki.usc.edu> seligson@nunki.usc.edu (Richard Seligson) writes:
>Hello,
>
>I am using the interactive SQL interface to Ingres Version 5.0/01.
>
>My database contains the following tables:
>
> ... tables deleted ...
>
>The SQL statements are:
>
>create table t1 as
>select dept.dname, class.classnum, students=count(enrollment.socsec)
>from dept, enrollment, class
>where class.dcode = dept.dcode
>and   enrollment.classnum = class.classnum
>group by dept.dname, class.classnum;
>
>create table t2 as
>select dname, tot_students=sum(students)
>from t1
>group by dname;
>
>The (almost) identical statements to create the views are:
> ... nearly identical statements deleted...

The problem with the creation of t2, or v2, is that there are 2 ways
to interpret this query:
1) Perform the "sum" operation on the original tuples from t1 and then
   remove the classnum attribute
2) First remove the classnum attribute and then perform the "sum" operation
   on the subtuples.

The first operation will yield the "correct" result, whereas the second
may count only the classes with different numbers of students, since after
the projection one may have duplicate tuples, and an SQL implementation has
the freedom of deciding whether to remove duplicates or not. (unless the
"unique" keyword is used forcing the removal of duplicates)

The problem with your SQL implementation is that in the relational algebra
(or domain calculus) the selection and projection operator may be performed
in either order, yielding the same result. But this property is no longer
true if "counting" is added to the domain calculus. SQL is an extention
of the domain calculus for which this property of the domain calculus no
longer holds, and obviously the implementors overlooked this.

An additional problem is that the syntax of SQL does not really indicate
whether it is the "select" (the projection) or the "where" (the selection)
should be executed first.

What you may try to solve this problem is:

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.

Paul.
-- 
------------------------------------------------------
|debra@research.att.com   | uunet!research!debra     |
------------------------------------------------------