Posted by Serge Rielau on 03/08/06 17:44

David Portas wrote:
> Serge Rielau wrote:
>
>> Given that views get expanded into the query
>> there is no technical reason for the presence of the order by columns in
>> the view signature (the columns exposed by the view).
>
> So what would you expect to be the order of rows output by ORDER BY
> _ORDER_OF_v1_ in the following example? Are you sure you can define
> that order for all possible queries? Does it make a difference if I
> leave Z in the view or not?
>
> I suspect that any deterministic logic you define is going to be so
> obscure that it would outweigh any apparent attraction of the syntax
> shortcut you are proposing. It would cause more confusion and
> unjustified assumptions about what the "correct" order should be. In
> other words it would add nothing in terms of clarity or concision.
>
> CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
> (x,z));
>
> INSERT INTO tbl (x,z) VALUES (100,1);
> INSERT INTO tbl (x,z) VALUES (100,2);
> INSERT INTO tbl (x,z) VALUES (200,0);
> INSERT INTO tbl (x,z) VALUES (200,4);
>
> CREATE VIEW v1 AS
> SELECT x,z
> FROM tbl
> ORDER BY z /* note: not legal SQL */
>
> SELECT x
> FROM v1
> GROUP BY x
> ORDER BY _ORDER_OF_v1_ ;
Very good example. Now this of course cannot preserve the order.
One might argue that it should, in fact, return an error.
But does a feature of convenience need to work under all circumstances?
This query also is not updatable, or deletable yet SQL clearly supports
updatable cursors. It simply slaps your wrists when you ask for semantic
nonsense.
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.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]