Path: utzoo!utgpu!attcan!uunet!lll-winken!ames!mailrus!bbn!oberon!nunki.usc.edu!sal44.usc.edu!seligson
From: seligson@sal44.usc.edu (Richard Seligson)
Newsgroups: comp.databases
Subject: Re: INGRES oddity (or is it me?)
Summary: Error when basing queries on views. (SQL)
Keywords: INGRES, SQL, views
Message-ID: <2321@nunki.usc.edu>
Date: 12 Jan 89 00:44:40 GMT
Sender: news@nunki.usc.edu
Reply-To: seligson@oberon.usc.edu (Richard Seligson)
Organization: University of Southern California, Los Angeles, CA
Lines: 32

A while back I had posted a question about views based upon views in Ingres.
I'd like to thank all those who replied.

The two basic points that were brought up were:

1) There is an ambiguity in SQL regarding whether projection is done before
   or after selection. Pointed out by Paul De Bra (debra@alice.UUCP).

2) And as pointed out by Andy Simms (ajs@datlog.co.uk):

> 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>."

Unfortunately, INGRES is violating both.  It allows me to use a
"grouped query" based on a "grouped view", and it interprets the 
ambiguity differently depending upon whether a "grouped view" was 
involved or not.

If my "grouped query" is based on a "grouped view" then INGRES projects
then selects, otherwise INGRES selects then projects.

Has anyone else found this?  I can supply a small INGRES script that
demonstrates this.

Rich Seligon                seligson@oberon.usc.edu  - ARPA
                            ...uunet!oberon!seligson - UUCP