Path: utzoo!attcan!uunet!mcvax!ukc!stc!datlog!ajs
From: ajs@datlog.co.uk ( Andy Simms )
Newsgroups: comp.databases
Subject: Re: INGRES oddity (or is it me?)
Summary: INGRES shouldn't allow this
Message-ID: <1682@dlvax2.datlog.co.uk>
Date: 21 Dec 88 12:56:08 GMT
References: <2183@nunki.usc.edu>
Reply-To: ajs@datlog.co.uk ( Andy Simms )
Organization: Data Logic Ltd, Queens House, Greenhill Way, Harrow, London.
Lines: 62

In article <2183@nunki.usc.edu> seligson@nunki.usc.edu (Richard Seligson) writes:
>I am using the interactive SQL interface to Ingres Version 5.0/01.
>
>I want to create a relation of departments, classes, and the number of 
>students in each class.  Based on that relation, I want to create a second
>relation of departments and total number of students in its classes.
>
>When I create a table, and then a second table based on the first, I get
>the correct results.  However, when I create a view and then create a 
>second view based upon the first view, the second view contains incorrect data
>(the first view is correct).
>
>I am wondering if it is incorrect to build a relation based on a view (Ingres
>doesn't flag any error), or if I've done something wrong.
>
>create view v1 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 view v2 as
>select dname, tot_students=sum(students)
>from v1
>group by dname;
>

Most SQLs disallow GROUP BY if the table referenced in the FROM clause
is itself a "grouped view" (i.e. a view with a GROUP BY). To quote the
ANSI standard (section 5.19 defining <table expression> - the FROM ...
WHERE ... GROUP BY ... HAVING ... stuff):

	"If the table identified in the <from clause> is a grouped
	view, then the <table expression> shall not contain a
	<where clause>, <group by clause>, or <having clause>."

I suspect this restriction is present mainly for implementation reasons.
No SQL that I know of materialises views at the definition stage. Instead
the definition is stored away and incorporated into the overall query
at run time, so the resultant query must be syntactically valid. In your
example you are effectively asking for

    tot_students=sum(count(enrollment.socsec))

which is invalid syntax. WHERE is banned for similar reasons, e.g.

	select dname, students
	from v1
	where students > 1

This ends up as

	where count(enrollment.socsec) > 1

and you can't have COUNT in a WHERE clause. Note that you can't get round
this by using a HAVING clause because that's banned too, which is particularly
annoying.

Finally, you might also note how the lack of outer joins in INGRES means
that you never see the department/class combinations for which there are
no enrolled students.