[xi]

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc.

Very good point.

In the book pointed out by Matt, Date explicitly states he's not saying ORDER BY is not useful, just that it doesn't return a relation and thus it's not included in the algebra.

My biggest gripe about ORDER BY, LIMIT and relational model is the fact that while Date and others made some attempts to express these operations in terms of relational algebra, they never (AFAIK) tried to do the opposite: alter the relational query model to naturally support them. It's not hard: just replace sets with sequences or arrays. It will gives you natural ORDER and SLICE operators as well as new aggregates FIRST, LAST, NTH. It solves duplicates without having to introduce bags, gives windowing functions for free and probably better represents how modern RDBMS interpret a query. Another hint why sequences may work better than sets is the fact that regular set operations such as INTERSECT and UNION (as opposed to UNION ALL, which becomes concatenation) are so rarely used in real-world queries.

I'm not even arguing that this is a good approach, but I think it deserves some discussion and it appears they never even thought of a possibility of changing the model treating it not as an instrument, but as a sacred scripture.

[einhverfr]

First I don't know why order by, limit, offset, or windowing functions, can't be said to return a relation if we define relations in a way which is sufficiently useful to include these operations. In other words, they are used in ways which returns sets of tuples (or sets of entities if you want to see it that way), based on specific selection criteria.

I would thus agree that to the extent that these are not part of the relational model this says more about the incompleteness of that model than it does about the operations themselves.

[MattRogish]

Relations being defined what they are is not accidental; sets are not bags and there are a lot of very good things that come out of a relation having a very well defined, uh, definition.

It's not a matter of "usefulness" but of "well-defined" that allows us to derive a whole lot of other interesting things.

Folks actually have defined ORDER BY, LIMIT, OFFSET, etc. in terms of the RM; it's just that the typical ORDER BY doesn't return a relation because of ordering (sets are unordered by definition) and so there was a lot of gymnastics they had to do in order to keep the set theory intact.

Sure, arbitrarily reordering is not a hard concept (or even implementation) but to make sure you cover all the bases requires a significant amount of work. A RDBMS is a complicated thing and you don't want to just add something to it without doing proper due diligence.

One could argue that NULLs are more "useful" (I disagree) but the addition of NULLs (a deceptively simple concept) has vastly overcomplicated SQL and lead to a number of inconsistencies in the spec.

[einhverfr]

Sets may be unordered by definition but that doesn't mean you can't define something interesting as an ordered set.

Consider the Pythagorean attempt to prove that all numbers were rational by trying to prove that the square root of two was rational. That they were able to prove that it was not rational meant that we ended up with a new category of numbers. Similarly once you get into the square root of -1 you get into yet another category of numbers designed to address that problem.

Our numeric model isn't complete with just rational numbers, or just rational and irrational numbers. Today we have to add imaginary and complex numbers as well. Why shouldn't we be expanding relational math in the same way?