From: wieck@debis.com
To: herouth@oumail.openu.ac.il
Cc: jwieck@debis.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Order by
Date: Wed, 2 Jun 1999 16:49:34 +0200 (MET DST)
Message-ID: <m10pCKk-0003kGC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <l03130304b37add804973@[147.233.159.109]>
Herouth Maoz wrote:
>
> At 13:45 +0300 on 02/06/1999, Jan Wieck wrote:
>
>
> > That's all right and I'm not going to allow this just for the
> > ORDER BY. It will be enabled as kind of a side effect.
> > Because view's can have a GROUP BY clause, the
> > rewriter/planner must be able to produce plans where
> > different left-/righttrees have their own grouping. Grouping
> > requires a sort and thus sorting of subplans must be
> > supported.
> >
> > Why should we deny ORDER BY on views explicitly?
>
> The SQL92 standard, if I read it right, defines the view by a "query
> expression", which in its turn relies on "query specification", which in
> turn relies on "table expression", which does not include ORDER BY.
>
> In short, ORDER BY is not part of the query syntax used in the definition
> of views, because order is meaningless in views.
Right now, the parser accepts ORDER BY in CREATE VIEW because
the grammar includes the full SelectStmt rule for the view's
query expression. The LIMIT, DISTINCT and ORDER BY clauses
are rejected later in rewriteDefine.c.
>
> The basic idea is that we should get the same results if we use standard
> SQL whether we run on PostgreSQL or on Oracle. So, one may write an
> application which selects the data from the view, and relies on the order
> in the view for something (like grouping). The same program won't work in
> any other database system, because you simply can't make that assumption
> without adding 'ORDER BY' to your actual query.
GROUP BY must be possible in a view's query expression.
Without that views with aggregate columns could never work
properly.
If I now read your above statements right, "query expression"
-> "query specification" -> "table expression" also forbid's
GROUP BY, because it cannot occur in a table expression -
right? That means that a fully SQL92 compliant DB cannot
support aggregates in view definitions?
Oh what a perfect world - I'm stressing my brain to find a
way how they could work and the real solution is to forbid
them :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wieck@debis.com, herouth@oumail.openu.ac.il, jwieck@debis.com
Subject: Re: [SQL] Order by
In-Reply-To: <m10pCKk-0003kGC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox