|
|
Posted by Serge Rielau on 03/08/06 21:21
David Portas wrote:
> Serge Rielau wrote:
>> Here the user chose to further process the rows of the view in way that
>> cannot preserve order. That does not invalidate the usefulness of the
>> proposal IMHO.
>>
>
> So here's the problem. There are many, many examples using joins,
> unions, aggregations and projections where ordering cannot sensibly be
> permitted based on the underlying view(s). So do you want to detect
> these at design time and raise an error or do you just give a warning
> and allow creation of a query with the ORDER BY clause that won't in
> fact be ordered?
Actually not UNION.
SELECT * FROM (SELECT c1 FROM T1 UNION ALL SELECT c1 FROM T2) AS X
Does not allow you to reference T1 or T2.
If your view contains the UNION then of course it can order the result
of the union.
There are to orthogonal pieced here:
Defining a "suggested" ordering and consuming that ordering.
Let's assume I have created a view with an order by.
Let's assume I have written some queries that consume this order by
correctly (no aggregation, funny join).
Now I alter the view and I change the order by clause.
Nothing will change. all queries will continue to work using the new
order. this is no different than if you alter the DEFAULT of a column.
It doesn't break any INSERT or UPDATE statements.
Let's go back to the original problem:
The OP wanted to _encapsulate_ the order inside of an object (preferably
a view). This is absolutely achieved.
If the query succeeded against the object once it will always succeed,
because the only way to break the order by is through bad specification
of the query itself.
If you screw up the ORDER BY for a result set in a procedure exactly the
same implications arise. Of course the app will see the difference and
presumably this is the whole idea.
W.r.t. what happens in the bad case: Yes, of course the DBMS should
raise and error if it can't do what the user requests.
I see no point, btw., in raising an error when the underlying object has
NO ORDER BY to begin with.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Navigation:
[Reply to this message]
|