Thread: Order by
Hi all, Is anyone know that if we allow order-by clause in the view structure. If not, how can we take the ordering of the UNION ALL in 2 different bags ? Thanks!! MICHAEL
>
> Hi all,
>
> Is anyone know that if we allow order-by clause in the view structure.
> If not, how can we take the ordering of the UNION ALL in 2 different bags ?
ORDER BY (along with many other things on views) is on my
personal TODO. I hope to get it working in v6.6.
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) #
At 03:48 +0300 on 02/06/1999, Michael Yeung wrote: > Hi all, > > Is anyone know that if we allow order-by clause in the view structure. > If not, how can we take the ordering of the UNION ALL in 2 different bags ? Correct me if I'm wrong, but ORDER BY shoud not be part of a view, in the same way it is not a part of a table. A table is defined as an unordered collection of tuples. In any given query, you can define the order of the tuples returned. But the table itself is not ordered. A view should look to the user just like a table. The order of the tuples is part of the query, not part of the table, and not part of the view. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Herouth Maoz wrote:
>
> At 03:48 +0300 on 02/06/1999, Michael Yeung wrote:
>
>
> > Hi all,
> >
> > Is anyone know that if we allow order-by clause in the view structure.
> > If not, how can we take the ordering of the UNION ALL in 2 different bags ?
>
> Correct me if I'm wrong, but ORDER BY shoud not be part of a view, in the
> same way it is not a part of a table. A table is defined as an unordered
> collection of tuples. In any given query, you can define the order of the
> tuples returned. But the table itself is not ordered.
>
> A view should look to the user just like a table. The order of the tuples
> is part of the query, not part of the table, and not part of the view.
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?
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) #
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. 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. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
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) #
At 17:49 +0300 on 02/06/1999, Jan Wieck wrote:
> 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?
No, "table expression" includes "group by" and "having", because they
define the data itself. They actually say something about the set of
tuples. Here is the "table expression" definition:
<table expression> ::= <from clause> [ <where clause> ] [ <group by clause> ] [ <having
clause>]
Here is the "query specification":
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::= <asterisk> | <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::= <derived column> | <qualifier> <period> <asterisk>
<derived column> ::= <value expression> [ <as clause> ]
<as clause> ::= [ AS ] <column name>
Finally, if you wondered where the syntax allows for unions and such like
for the definition of a view, here it is:
<query expression> ::= <non-join query expression> | <joined table>
<non-join query expression> ::= <non-join query term> | <query expression> UNION [ ALL ] [ <corresponding
spec>] <query term>
| <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>
<query term> ::= <non-join query term> | <joined table>
<non-join query term> ::= <non-join query primary> | <query term> INTERSECT [ ALL ] [ <corresponding spec>
]<query primary>
<query primary> ::= <non-join query primary> | <joined table>
<non-join query primary> ::= <simple table> | <left paren> <non-join query expression> <right paren>
<simple table> ::= <query specification> | <table value constructor> | <explicit table>
<explicit table> ::= TABLE <table name>
<corresponding spec> ::= CORRESPONDING [ BY <left paren> <corresponding column list> <right
paren> ]
<corresponding column list> ::= <column name list>
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma